Before you follow this guide, make sure that you have a backup database. If your system runs in a virtual environment, I recommend creating snapshots as well. This is best done outside office hours or when the database is not being used. If this is not possible, you should inform your users.
Copy the SQL Server database
Before we start with the copy procedure, you need to download SQL Server Management Studio (SSMS). SSMS is the console you will use to manage your SQL infrastructure.
- Connect to your SQL instance using SSMS.
- Expand Databases and then select your database. In my case, the database is called 4sysops, and it is hosted on server SQL01 (SQL Server build 13.0.5026.0, which is Microsoft SQL 2016).
- Right-click the database and then select Tasks > Back Up…
- Select or change the backup location and then click OK. The default backup location is C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup. However, you can change it (network locations are also supported).
- When the backup is finished, you will see the confirmation window. Click OK.
- Navigate to the location where you stored the backup file. The file has an extension of .bak.
- Copy the file to a new server with a newer SLQ Server version installed.
Restore the database to a new SQL server
In the second part, you will restore the database from the old SQL Server to the new one.
- Navigate to the new server and connect to the SQL Server using SSMS.
- Expand Databases.
- Right-click Databases and then click Restore Database...
- Select Device and then click the More button (…) to browse to the device and location where you stored your backup file on the new server. In my case, I stored it at the root of the C partition (C:\).
- Click Add to add new backup media.
- Navigate to the location where you saved the SQL backup file, select it, and then click OK.
- Click OK to confirm.
- Check that everything is all right, and then click OK.
- If everything is OK, you will see a popup window confirming that the restore was successful. Click OK. The 4sysops database has now been successfully copied from the old server, SQL01, to the new server, SQL02 (SQL Server build 13.0.5026.0, which is Microsoft SQL 2022).
- Reconfigure your application, so it now points to the new server where the database has been copied.
Take the old SQL Server database offline
If everything works as expected, you should take the 4sysops database offline on the old server. Navigate to your old server, connect to your SQL Server using SSMS, and follow the procedure.
- Right-click your database and then click Tasks > Take Offline.
- Select Drop All Active Connections, and then click OK.
As you can see, the old database is offline.
Subscribe to 4sysops newsletter!
If you have to copy a SQL Server database from one SQL Server to another, you need to download SQL Server Management Studio (SSMS). The copy procedure has two steps. In the first step, you need to back up the database from the old server to a local or network storage location, and in the second step, you need to restore it to a new server. At the end, your application should point to the newly copied SQL Server. I recommend keeping the old SQL Server database until you are sure that your application with the new SQL Server database works as expected.