Moving a database between two SQL Server instances

Overview
There are many reasons for moving a database from one SQL Server instance to another instance, for example:

  • Upgrade by restoring into a instance which is a more recent version of SQL Server
  • Move to a new instance which has a system collation that is right for the database
  • Server consolidation, reducing number of instances
  • Using some database-level HA/DR technology (like log shipping or database mirroring) where a failover moves the database to a different server
However, the database is not all that you want to worry about. There are things outside the database, which you also should consider. The purpose of this article is to be an aid for those situations.

Versions
This article is not written for some specific version of SQL Server in mind, but is generally targeted towards more recent versions.

Plan ahead!
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":
  • Logins which aren't used in other databases.
  • SQL Server Agent jobs which references the database.
  • Backup history information. This is not that critical since you hopefully have other means to trim this (so over time this will be aged out automatically).
  • Backup files still residing on the hard drive. Of course, there might be a good idea to keep some of these as an extra fallback measure.
  • Backup devices (as seen in Server Objects, Backup Devices and sys.backup_devices).
  • ... and checkout the rest of this article. Things you want to bring over to the new server will also be things you might want to remove from the old server.

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:
RESTORE HEADERONLY FROM DISK = 'R:\mydb.bak' 
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:
RESTORE FILELISTONLY FROM DISK = 'R:\mydb.bak'
WITH FILE = 1
 
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:
RESTORE DATABASE mydb FROM DISK = 'R:\mydb.bak'
WITH
 MOVE 'mydb' TO 'C:\mydb.mdf'
,MOVE 'mydb_log' TO 'C:\mydb_log.ldf'
,REPLACE
 
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.

Logins
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):
SELECT *
FROM sys.database_principals AS d
WHERE NOT EXISTS
  (
   SELECT *
   FROM sys.server_principals AS s
   WHERE s.sid = d.sid
   )
AND type_desc = 'SQL_USER'
AND name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')
 

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:
ALTER USER Joe WITH LOGIN = Joe 

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. You can use sp_help_revlogin from Microsoft, Greg Low's has some great scripts, or Robert L Davis' script which also bring over server role membership and login permission settings. Also, there's several cmdlets for Powershell written specifically for these purposes at DbaTools, for instance Copy-DbaLogin.

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.

Jobs
Stored in the msdb database, can be scripted using SSMS.
SELECT name
FROM msdb.dbo.sysjobs
WHERE name NOT IN ('syspolicy_purge_history')
 
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.

The application
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.
  • Make sure you go through all the places where your application has the name of your SQL Servers. This will typically be in connection strings, but can also be in ODBC configurations or other configuration files.
  • Make sure that the application can reach the new SQL Server, things like firewalls and DNS entries.

Server configuration (sp_configure)
Stored in the master database, can be scripted using SSMS, listed in SSMS under Server Objects.
SELECT * FROM sys.configurations
Make sure that the destination server has apropriate configuration settings. This include things such as "max server memory", "optimize for ad-hoc workloads", etc.

Collation
SELECT
 SERVERPROPERTY('Collation') AS server_collation
,DATABASEPROPERTYEX(DB_NAME(),'Collation') AS database_collation
The database has a collation, which among other things serves as default collation when you create tables. The instance also has a collation, which acts as a default collation for when a new database is created. Both can be overridden using the COLLATE clause. You want to make sure that moving the database from one instance to another won't cause any collation related problems (like "collation conflict" errors in your application). You can check the server collation using SERVERPROPERTY('Collation'). You see the database collation in sys.databases and also in the output from RESTORE HEADERONLY.

Database ownership
SELECT
 (SELECT SUSER_SNAME(owner_sid) FROM sys.databases WHERE database_id = DB_ID()) AS owner_according_to_master
,(SELECT SUSER_SNAME(sid) FROM sys.database_principals WHERE name = 'dbo') AS owner_according_to_the_app_database
The login who performs the restore, attaches or creates the destination database will be the owner of the database, as seen from the instance (master database) perspective. You can see this in the sid column in sys.databases. However, if you use a binary method to move the database (restore or attach), then the owner (according to the database) will be the same as before (possibly an orphaned user, see above). A problem is if the login who used to own the database (which on the old instance can access the database as the dbo user, being the owner) is no longer the owner on the new instance. I.e., make sure that the login that used to own the database will be able to access it in the destination instance, with proper privileges. I typically have sa as owner for most of my databases. Here's an example for how to change owner for a database:
ALTER AUTHORIZATION ON DATABASE::AdventureWorksDW2008R2 TO sa 

Linked Servers
Stored in the master database, can be scripted using SSMS, listed in SSMS under Server Objects.
SELECT * FROM sys.servers WHERE is_linked = 1 

Database maintenance
Stored in the msdb database, if you use Maintenance Plans
You of course want to performan regular database maintetance after moving the database to the new server. So, make usre to include the database in whatever solution you have for database maintetance.

User-defined error messages
Stored in the master database.
SELECT *
FROM sys.messages
WHERE message_id > 50000
 
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.

Service Broker
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.

Operators
Stored in the msdb database, can be scripted using SSMS.
SELECT name
FROM msdb.dbo.sysoperators
It is very unlikely that your code extracts an email address based on an operator name using above table in msdb and then uses that email with for instance sp_send_dbmail. Unlikely, but possible.

SQL Server Agent Event Alerts
Stored in the msdb database, can be scripted using SSMS
SELECT name
FROM msdb.dbo.sysalerts
Alerts aren't tied to a specific databases (well, it can be limited to a certain database...), so your app won't stop working if you don't carry over your alerts. But it is likely that you also want Alerts on the destination server if you have such on the source server.

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

Filestream
SELECT * FROM sys.filegroups WHERE type_desc= 'FILESTREAM_DATA_FILEGROUP'
SELECT * FROM sys.database_files WHERE type_desc= 'FILESTREAM'
 
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:
  • Server Audit
  • Policies
  • Data Collection
  • Resource Governor
  • Backup Devices
  • Cross-database queries
  • Endpoints
  • DDL triggers at the server level
  • Credentials
  • Extended stored procedures
  • Change Data Capture
  • Certificates in the master database that are used to sign procedures or assemblies
  • Consider doing things such as updating statistics, check database integrity etc. after the move.
  • If you go from a production to a QA/test/dev environment, then there might be requirements to obfuscate some data / keep some data in the destination database.

Tricky things
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.
  • You need to change the collation in the database after move, on the destination instance. Changing the collation of a database is not a simple task. Here's an aticle and a powershell script that might be useful.
  • The destination SQL Server is of a lower version than the source SQL Server. You cannot restore or attach a database which is of a higher version. Here are some options that can be helpful:
    • Upgrade the destination instance to the same version as the source instance. Definitely the easiest option, and the one I suggest, if at all possible.
    • Use a third party product to replicate schema and then the data (like for instance Red Gate SQL Compare / SQL Data Compare).
    • Use the import-export wizard in SQL Server Management Studio.

More information
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.


Acknowledgements
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!