«

»

Apr 13 2013

Move a SQL database from one server to another server

Sometimes you need to move/migrate your SQL database(s) from one server to another, or just copy that database from a production environment to a test one, or vice-versa. Maybe you want to upgrade you SQL environment to the latest version, and a migration is preferred instead of in-place upgrade because of a few things, like:

New Operating System – you can use the latest OS for your new SQL server(s), if you are not on the last one.

Installation rollback – if something goes wrong with the installation or with the migration on the new server, you are safe, because you have the old one up and running. All you have to do is start from scratch with the new server and learn from your mistakes.

One by one – with a migration you don’t have to upgrade them all at once. Since your environment is still working you can migrate a few of your SQL servers to the latest edition, put them in production and see if everything goes well, and if it does, migrate a few more.

Hardware refresh – you can easily increase you processing power, and not use the old hardware in the company.

Test environment – you can test the new SQL servers as you go without affecting the users.

Before you actually start moving databases, make sure your application(s) are compatible with the new SQL version, the OS is healthy and you have backups. Here I’m going to show you how to move a database from a SQL Server 2008 R2 Enterprise server to SQL server 2012. If you have older versions of SQL server is OK, the procedure is the same; maybe a few buttons may be moved here and there. I will present three methods here to migrate a database: back up and restore, detach and attach the database, use the copy database option in SQL Server Management Studio.

Let’s start with the first one, backup and restore. Use this method if you can’t put the database offline. Now connect to the SQL 2008 R2 database by opening the SQL Server Management Studio. Type your SQL host name and instance then select Windows Authentication. Make sure the account you are log in with right now has privileges to connect to the database.

Right-click the database you want to move, go to Tasks > Back Up.

Here, I usually leave all the options to their default settings except the backup location. I just don’t like the path. So if your database is like mine, on the C drive, you can change that by clicking the Add button, then provide the new backup location. On the File name box type the a name for the backup file then hit OK twice.

    

Back on the Back up Database window select the old path and hit the Remove button. Now you should have only one backup location. Click OK to start the backup.

A notification window will be displayed informing you that the backup has been completed successfully.

If you open the backup path in Explorer, the database should be present there.

Copy or move this database backup to the new SQL server. Also here (on the SQL 2012 server), open the SQL Server Management Studio and connect to your existing SQL instance.

Right-click the Database folder and choose Restore Database.

Ignore the message on top of the window “No backupset selected to be restored.“. This is normal because since no backup has been done on this system using SQL Server Management Studio. Select the Device radio button then choose a database to restore. This is done from the button on the right.

Click Add on the Select backup devices window and browse to the backup location. Here I’m using a UNC path to access the backed up database, I was just to lazy to copy it locally. Click OK.

    

After a quick verification the database is ready to be restored. Press the OK button to start the process.

Depending on the size of your database this could take some time, but at the end you should get a successful message and the database restored/imported.

    

There is one more step to do here that applies to all three methods, but I’m going to show it to you at the end of this guide, because I don’t want to keep repeating it on every section. Now that we are done with the first method, let’s move over to the second one, detach and attach the database. Using this method the database will not be available to clients. And by clients I mean software that reads and writes to this database. You will need a downtime for this, and the downtime depends on the size of the database. On the SQL 2008 R2 server right-click the database, go to Tasks > Detach.

Check the boxes Drop Connections and Update Statistics then hit OK.

After the database is detached it will be gone from the SQL Server Management Studio, so don’t panic if you see it disappear.

Using Explorer, open the database location and copy the files yourdatabsename.ldf and yourdatabasename.mdf to the new server. You should put the files on the final path, because after you attach the database on the destination server this files will grow, well…the .mdf file will.

On the SQL server 2012 right-click the Database folder and choose Attach.

In the Attach Databases window click the Add button then browse to the files location and select the .mdf file. Click OK twice.

    



The database is now fully functional. As you saw, this method is faster and involves fewer steps, but the database is offline trough the all process.

The last method is to copy the database using the SQL Server Management Studio. This is the easiest one and right now I’m thinking why I left it at the end. Oh well…never mind, let’s move on. Even if is the easiest one, there is a catch. You might think…let me just open the Management Studio on the old server and use the wizard to copy the database. You can, but you will get an ugly error: “Index was outside the bounds of the array.“. This is because the SQL 2008 R2 Management Studio is not compatible with SQL server 2012.

To get past this error we need to go to the destination server, the SQL 2012 server, open the Management Studio console and connect to the old SQL server instance. From the File menu choose Connect Object Explorer, or click the icon from the Object Explorer window.

     

Type the server name, the old one, then click Connect.

Now that we are connected to the old SQL server, right-click the database and choose Tasks > Copy Database.

The wizard automatically knows the source server, but verify it anyway. If is not the one you want, type it in the Source server box.

On the destination server, the server name most likely will be wrong, so we need to type the correct one. Click Next when you’re done.

Looks like we have an error “SQL Server Agent does not appear to be running on the destination server.  If SQL Server Agent is not running on the destination server, Copy Database Wizard will not function properly. Do you want to continue?”. This popped-up because the SQL server Agent is not running on my SQL 2012 server. I deliberately shut it down so you can see the error. Click No here, and go start the SQL Agent.

Now when you click Next at the Select a Destination Server screen, you should be good-to-go. On the next screen there are two options; the first one is faster but requires the database to go offline, and second one is slower but the database stays online. Choose whatever you think is right for you then click Next.

Select if you want to copy or move the database. I will go with the default option here and copy it.

On this wizard page you have the option to change the destination database name and to overwrite it if it already exists. The destination path can’t be changed from the wizard, this is done from the SQL server 2012 Properties page/Database Settings. Since on the destination server I know I don’t have a database with this name I will go with the first option Stop the transfer if a database or file with the same name exists at the destination.

If there are any related objects to this database, select them, then press the arrow to move them to the right, to the Selected related objects section.

Choose how the logs are saved: in a text file or in Windows Event Viewer. Click Next to continue the wizard.

If you don’t want to copy/move the database immediately, you can schedule the job. For the sake of this example I will go with the first option and run the task right now.

On this screen click the Finish button to start the copy/move database process.

Depending on the size of the database this could take from a few minutes to a few hours. When is done you should see only green check arrows.

     

Refresh the console and you should see the database up and running on the new server.

Like I told you, there is one more step that applies to all three migration methods. The database needs to be put in a 2012 mode, or the latest version of your SQL server in case you are not using SQL 2012. This is to take advantage of all the features that the latest SQL edition provides. After the database has been moved, right-click it and choose Properties.

Click the Options page and on the Compatibility level box choose the latest edition of SQL server. In mine case is 2012. You have to be careful with this, because if you ever wanted to migrate the database to an older SQL version is not going to work. There are going to be incompatibility problems, so again…caution.

Want content like this delivered right to your

email inbox?


4 comments

Skip to comment form

  1. hasan

    thank you very much.

    1. Adrian Costea

      No problem. Glad I could help.

  2. Rhian

    Do we also need to change the ODBC Connection as well?

    Great post by the way 🙂 Really useful

    1. Adrian Costea

      Yes, since the server will have a different name, the ODBC connection needs to be changed to reflect the new SQL server name.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

css.php