Considering that many Microsoft enterprise products rely on SQL Server, many admins face the task of copying the SQL Server database if new server hardware is deployed or when an upgrade of the SQL version is necessary. As an example, I will copy a SQL Server database hosted on SQL Server 2016 to a SQL Server 2022 system.
Latest posts by Jasmin Kahriman (see all)
Copying an application relying on SQL Server 2016 to the system

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.

  1. Connect to your SQL instance using SSMS.
    Connect to your SQL instance

    Connect to your SQL instance

  2. 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

    Select the database you want to migrate

  3. Right-click the database and then select Tasks > Back Up…
    Back up the database

    Back up the database

  4. 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

    Choose the backup location

  5. When the backup is finished, you will see the confirmation window. Click OK.
    Confirmation after the backup of database completed sucessfully

    Confirmation after the backup of database completed sucessfully

  6. Navigate to the location where you stored the backup file. The file has an extension of .bak.
  7. Copy the file to a new server with a newer SLQ Server version installed.
    Copy the backup file

    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.

  1. Navigate to the new server and connect to the SQL Server using SSMS.
  2. Expand Databases.
  3. Right-click Databases and then click Restore Database...
    Restore the database on the new SQL server

    Restore the database on the new SQL server

  4. 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

    Select the backup file to restore

  5. Click Add to add new backup media.
    Add backup media

    Add backup media

  6. Navigate to the location where you saved the SQL backup file, select it, and then click OK.
    Select .bak file

    Select .bak file

  7. Click OK to confirm.
  8. Check that everything is all right, and then click OK.
    Restore 4sysops database

    Restore 4sysops database

  9. 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

    Database successfully migrated to the new SQL Server

  10. 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.

  1. Right-click your database and then click Tasks > Take Offline.
    Take the database offline on the old server

    Take the database offline on the old server

  2. Select Drop All Active Connections, and then click OK.
    Drop all connections to a database on the old server

    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

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.

avatar
4 Comments
  1. Mark Burns 2 months ago

    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.

    • Gerold Manders 2 months ago

      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.

      • Author

        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.

    • Author

      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.

Leave a reply

Please enclose code in pre tags

Your email address will not be published.

*

© 4sysops 2006 - 2023

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account