Use the following instructions when you need to create a copy of an existing database on the same server.
- Create a backup of the existing database.
- Create the new database and select the task ‘Restore’
- Select the db backup file to restore, however as the backup is for another database on the existing server the option ‘Overwrite the existing database (WITH REPLACE)’ must be selected
- Before restoring the database, just check the filepaths where the database is being restored too!
During the maintenance of a Windows server it was decided that it was time to ditch SQL Server Express and migrate to SQL Server Workgroup Edition - mainly because the restrictions on database size and users were becoming an issue.
However, rather than proceeding to backing-up the database, uninstalling the Express version and then configuring the new installation of SQL Server from scratch, a little research proved suspicions that it would be possible to migrate through an upgrade procedure that would maintain the existing database and it configuration.
The procedure was simple enough - run setup.exe from the command line and include the switch SKUUPGRADE=1. Of course, before we began we still created a backup of the database!
At first setup appears to run as normal, but later during the process you are able to select the database that is to be upgraded. By following the installation wizard and waiting for installation to complete we were able to upgrade the SQL Server Express database to the Workgroup Edition. We confirmed installation by checking the version of the database using SQL Server Management Studio. After some security configuration in SQL Server Configuration Manager we found that users could immediately connect to the upgraded database without issue.