- Install and configure Windows Server Backup in Windows Server 2022 - Fri, Jun 2 2023
- What is Windows 10 S mode? How does Windows 11 S mode differ? - Fri, May 19 2023
- Move a VM from XenServer to Hyper-V with StarWind V2V Converter - Tue, May 9 2023
Copying an application relying on SQL Server 2016 to the system
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.
Connect to your SQL instance
- 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).
Select the database you want to migrate
- Right-click the database and then select Tasks > Back Up…
Back up the database
- 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).
Choose the backup location
- When the backup is finished, you will see the confirmation window. Click OK.
Confirmation after the backup of database completed sucessfully
- 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.
Copy the backup file
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...
Restore the database on the new SQL server
- 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:\).
Select the backup file to restore
- Click Add to add new backup media.
Add backup media
- Navigate to the location where you saved the SQL backup file, select it, and then click OK.
Select .bak file
- Click OK to confirm.
- Check that everything is all right, and then click OK.
Restore 4sysops database
- 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).
Database successfully migrated to the new SQL Server
- 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.
Take the database offline on the old server
- Select Drop All Active Connections, and then click OK.
Drop all connections to a database on the old server
As you can see, the old database is offline.
Subscribe to 4sysops newsletter!
Database is offline
Wrapping up
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.
Wow – did you gloss over or outright omit a TON of really important steps!
if you were my DBA and that is all you proposed to do to migrate from one server to another server version entirely, I’d fire your ass in a flash!
You don’t do the proposed steps with a production environment (no matter how small), without doing a full set of compatibility, performance and health tests first! In the end, the copy and move of a production database to a new server environment may be VASTLY more complex than doing a simple file-based backup and restore! The fact that you mention NONE of this in your article is a MAJOR disservice to your readership.
A copy is a copy. Even in a production environment. Until the copied DB is confirmed to work according to the existing/new specifications you imposed, the original DB shouldn’t be taken offline. That is after all what an ‘Acceptance’ environment is for.
Don’t have one of those? Then what you consider to be a ‘Production’ environment barely outgrew the hobbyist stage. Any serious company even has a ‘Testing’ environment in which things get mangled and fixed until those things don’t break, Only then the DB/Code is moved to the ‘Acceptance’ environment. And only when the go-ahead is given, the ‘Acceptance’ environment is transferred to ‘Production’
Until all that has come to pass, a DB copy is just that, a DB copy.
And if you are really serious, you use as little as possible from the features in DB server software and separate business logic from DB Server logic as you can. Sure, that hurts in the beginning of development, but later you reap rewards in sheer speed, scalability that doesn’t cost an arm and a leg in subscriptions and your software can easily switch between different DB Server providers.
Hey Gerold – thanks for commenting. Only once confirmed it works (“If everything works as expected, you should take the 4sysops database offline on the old server.”) take it offline.
Hey Mark – thank you for your comment. This article shows how to copy the database from one to another server, not the prechecks. However, your point is valid, and we’ll add a small paragraph about it.