|Moving a database between two SQL Server instances|
About me and Nucleus
Articles, tips etc.
Courses and training
There are many reasons for moving a database from one SQL Server instance to another instance, for example:
This article is not written for some specific version of SQL Server in mind, but is generally targeted towards more recent versions.
Check out the "Contained Database" concept, introduced in SQL Server 2012. The whole idea is to make the database less dependent of what is outside the database. Here you find Web-based Books Online documentation for Contained Databases.
Scope and purpose
The main purpose of this article is to list things that your database might use, which lives outside that database. Focus is on listing these things, so you don't forget about them. Secondary is to elaborate or point to resources that has more information about that particular topic. This article do not focus on going from a lower to a higher version of SQL Server, might be additional considerations when upgrading to a higher version.
The queries below are in general meant to be executed from your database context.
Move or copy?
These operations have so much in common so I will not treat them differently in the rest of the text. Basically, move is the same as copy, but you also then delete the database from the source server. When moving a database, there will be things left behind on the old server, which you probably want to clean-up. Here are things that can be "left behind":
How to do the actual move
Unless you have some HA/DR technology where moving the database is part of that technology, then the first obvious step is the database itself.
I prefer BACKUP DATABASE and RESTORE DATABASE. For backup, consider using the COPY_ONLY option of the BACKUP command if you are doing a copy, or the NORECOVERY option if you are doing a move. The restore process might require you to use the MOVE option to specify some alternate filename for any of the database files to be created. Say you have a backup file named mydb.bak, and want to restore a database from this backup file. You might not know anything about what databases are included in this backup file, what path their database files were using etc. Start by investigating how many backups are in this file, types of backup and the database name for each backup in there:
You will see one row per backup contained in this backup file. Based on information above, you now want to see each database file used by that database, for the desired database in the backup file. Use the value from the "position" column from above, in the FILE option:
You will see one row per database file. The two columns you want to focus on are LogicalName and PhysicalName. Use the values from these columns in the MOVE option for the actual RESTORE command:
You might want to read about the REPLACE option for the RESTORE command in Books Online. Be careful if you use it and the destination database exists - so you don't overwrite the wrong database by mistake!
Another option is to detach the database using sp_detach_db and then attach it using CREATE DATABASE ... FOR ATTACH; or detach and attach using the GUI.
This will actually detach the database from the source server! Many things can go wrong here, which is why we prefer backup and restore. The backup process is online and since the source database was never made un-available in the first place, you don't risk destroying anything in case of mistakes, data corruption, or whatever other things that can happen (you'd be surprised). You might argue that it takes time to produce a backup file, but you should have such a backup anyhow! If that isn't recent enough, then just do an extra differential backup or a log backup. In addition, the size of the backup file(s) to transfer to the new instance is likely smaller than the combined size of the database files (backup do not include unused extents). And you can also use the COMPRESSION option of the backup command to make the backup even smaller. Furthermore, you can even split the backup into several files by using the striping option for the backup command (just specify more than one backup file).
Yet another option is to use some tool that scripts all objects in the database, export the database, then uses the scripts to create the objects and import the data. This has the drawback of not moving the database at the binary level; making is a more fragile process than backup/restore or detach/attach (in case something goes wrong with the scripting process). SSIS has a "Transfer SQL Server Objects" task, which can also be used from the "Copy Database Wizard". SSIS also has other task types that base be useful here, some will be mentioned below.
If this is a move, then you don't want to have users doing modifications in the source database after you took a copy if it (regardless of which of above methods you are using). This includes possible open transactions, that later on can become committed - you don't want to manually re-integrate such modification in the destination database! You can set the database to read-only or single user before you for instance do the database backup.
Stored in the master database
Main catalog views: sys.server_principals, dbname.sys.database_principals
Make sure you have the necessary logins with the same name, and for a SQL Server login also password and SID on the destination server.
This is probably the most obvious and known issue. A user in a database is "mapped" to a login. Inside the database, you can list the users through the sys.database_principals catalog view. Pay special attention to the sid column. The user is connected to a a login, sys.server_principals which also has a sid column. The sid columns is the mapping from the user to the login.
For Windows logins, the SID is produced by the AD or SAM database, and unless you move the database to a SQL Server in a different domain, then all you have to do is to create the same Windows login on the new server.
For a SQL Server logins, SQL Server will invent a sid when you create the login. So, if you just create the login on the new server, the sids won't match. If you use the GUI and look at the login on the new server, you won't see it mapped to the database. If you use the GUI to list the user inside the database, you won't see it mapped to a login. This is what we call an "orphaned user". So, SQL Server logins and users for those logins require a bit more attention than Windows logins. This query will list orphaned users in the database (it doesn't differentiate a deliberate user without login in source db, easiest is probably to check them manually):
If the login already exist on the new server, then you can adjust the sid for the user inside the database so it matches the right login. (Using the ALTER USER command or the old-fashioned sp_change_users_login.) Here's an example of using the ALTER USER command to map to an existing login:
If the logins do not exist in the destination instance, then I suggest you use a utility to script them on the source server and from that script create them on destination server; making sure they have the same sid and also password. There is a "Transfer Logins" SSIS task, but that doesn't carry over the password, so instead I suggest you use the sp_help_revlogin stored procedure. Use this if the source server is 7.0 or 2000 or this if the source server is 2005 or more recent. These procedures will also take care of server roles assignment and also carry over Windows logins.
Make sure that you have covered necessary server roles and permissions assigned to logins.
Also, your application might use some login without that login existing as a user in the database. A login which has the sysadmin server role can access all databases (using the dbo user), and your application might be using such a login. Your application won't be able to login using such login if that login doesn't exist on the destination server or if it exist but with a different password.
Stored in the msdb database, can be scripted using SSMS.
A job isn't tied to a specific database, so you want to go over all jobs to determine which ones you need to carry over to the new server.
Obviously you want to make sure that the application attempts to connect to the correct server and also that it can connect to that server.
Stored in the master database, can be scripted using SSMS, listed in SSMS under Server Objects.
Only relevant if you use Service Broker. You will have to enable Service Broker inside the database after attach or restore. You cannot have any connections to the database when you enable (or disable) broker.
Database Mail configuration
Stored in the msdb database.
It is possible (but pretty rare) that code executed in your databases also uses sp_send_dbmail to send email. If that is the case, you would have to enable and setup the Database Mail functionality on the destination instance.
Stored in the msdb database, can be scripted using SSMS.
SQL Server Agent Event Alerts
Stored in the msdb database, can be scripted using SSMS
Stored in the msdb database.
User-defined error messages
Stored in the master database.
I don't see user-defined error message much used, but it is easy to check and see if you have any messages with message_id > 50000.
Stored all over the place
What to do depends on whether you move a subscription database or a publisher database. But you do want to stop and think for a while, and probably remove that part of the replication setup and re-do it.
The actual filestream data is brought over automatically if you use BACKUP and RESTORE. You are in for more and messier work if you use some other method, so don't even go there - use BACKUP and RESTORE!. You also need to make sure that the destination instance is configured to support filestream (both the "SQL Server Configuration Manager" tool and sp_configure).
Other things which might be relevant, in no particular order:
Some things are particularly tricky, and I want to take the opportunity to point them out to you. I.e., if you have any of these situations, expect more work that just a standard "backup/restore-and-bring-over-the-other-stuff" (which is basically the scenario for this article). I will not go into details on how to handle these situations, I just point them out for you, so you can warm up your favourite search engine.
Here's a topic in the good old SQL Server Books Online that discusses managing metadata when you move a database: Managing Metadata When Making a Database Available on Another Server Instance. Obviously some of the stuff listed has already been discussed here, but there are some additional info.
Thanks for providing feedback and suggestions for this article: Dave Dustin, Linchi Shea, Paul S. Randal, Allan Hirt, Edwin Sarmiento, Dan Guzman, Adam Machanic, Kalen Delaney, Erland Sommarskog, Margi Showman, Jean-Sebastien Brunner, Aaron Bertrand, Ben Miller, Martin Bell. Much appreciated!