DB Migration (Server to server)

(Original post here:
http://yrushka.com/index.php/sql-server/database-recovery/sql-server-migration-from-one-server-to-another-detailed-checklist/
)

I. Steps to do on the current Production server – [Server A]

1. Check SQL Server properties – These must stay the same on the future server. 

use master

GO

select SERVERPROPERTY ('Collation')

select SERVERPROPERTY ('Edition')

select SERVERPROPERTY ('InstanceName')

select SERVERPROPERTY ('ProductVersion')

select SERVERPROPERTY ('ProductLevel')

select SERVERPROPERTY ('ServerName')

2. Check global configuration settings – These must stay the same on the future server. 

use master

go

sp_configure 'show advanced options' , 1

reconfigure

go

sp_configure

go

3. Check Databases Integrity

It is necessary in order to be sure that restored backups on migrated server will be valid.

                        DBCC CHECKDB ('DATABASE_NAME')

4. Note down model database growing options. This setting is important for every new created database. My strong advice is that you never leave the default setting because it can be a big cause for poor performance. The default setting is 1 MB per data file and 10% per log file – imagine what it means for a database to allocate MB after MB if it needs to allocate say 50 MB or grow a log file that is 10 GB in size.
5. Note down physical path for master, model, msdb, tempdb databases data and log files.  

use master

go

select name, filename from sys.master_files where database_id in (1,2,3,4) order by database_id

6. Note down physical path for mssqlsystemresource data and log files.

SQL 2008: It should be in :\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Binn\

For SQL 2005: same as master database location.

use master

go

select name, filename,dbid from sys.master_files where database_id in (32767)

7. Perform Full backup on master and msdb

II. Steps to do on the future Production server – [Server B] 

1. Install same edition/version of SQL Server on the new machine.

Keep in mind the following when installing: 

o    Make sure the new server has mounted the same drive letters where system databases reside on old SQL Server. Otherwise, after the restore of master SQL will not be able to start because SQL will want to start the rest of system databases on physical paths from [Server A].

o    If you want to restore system databases then you need to keep the same edition (Standard, Entreprise, Business Intelligence) and version (ex: 2008 RTM, 2008 SP1, 2008 R2 RTM etc.). You cannot restore a backup of a system database (master, model, msdb) on a server build that is different from the build on which the backup was originally performed. An workaround would be, to replace manually the system databases files copied from [Server A] to [Server B]. This can be done only if the migrated server can be stopped. If not, then you must follow the procedure with “restore database….”

o    If you want to restore only user databases, then you can install the same or upper edition/version. If you install a lower edition/version than the current Production one, you might not be able to restore some of the databases having higher builds features which won’t be supported on lower builds.

o    Keep the same collation when installing SQL Server.

o    Create a Domain/Windows user account that will be used to start SQL Services.

2. Backup system databases – master, model, msdb – and put in a safe location… in case you want to recover them.
3. Stop SQL Server. Copy all system databases files and add them to the safe location.
4. Create the physical locations for master, model, msdb, tempdb, mssqlsystemresource databases noted down at steps I.5 & I.6
5. Give to the user running SQL Full security rights on the new folders created.
6. Copy system databases backups made on [Server A] to [Server B] (step I.7).
7. Stop SQL Server.
8. Copy existing model, msdb and mssqlsystemresource database files from the installed location on [SERVER B] to the new created ones (step II.4). Afterwards you will be able to change the location for these databases. For now SQL will need old locations in order to load msdb, model, tempdb and mssqlsystemresource.
9. Start SQL Server in single user mode. From an elevated cmd (started with administrator rights), access the right folder where sqlservr.exe executable is located and execute below commands. Normally you will find it at “C:\Program Files\Microsoft SQL Server\MSSQL[InstanceName]\MSSQL\Binn”

There where cases when other services like SQL Agent or SQL Reporting took the single one connection and prohibited the administrator to restore master. This error is given:

Reason: Server is in single user mode. Only one administrator can connect at this time.
Before starting the SQL in single user mode, make sure you have stopped every SQL service and there is no one that will make a default connection to it (application, reporting). 

cd [drive]:\folder_name

sqlservr.exe -c –m

Restore master database from the backup copied from [SERVER A] using a NEW cmd line started with administrator permissions or using a DAC (Dedicated Administrator Connection) connection.

-- connect to SQL

sqlcmd -SMACHINENAME\SERVERINSTANCE -E

-- make sure you are connected to right server:

SELECT @@servername

GO

RESTORE DATABASE master FROM DISK = '[Drive]:\Backup_path\MASTER_.bak' WITH REPLACE;

GO

10. Exit the cmd prompt and Start SQL from Services (in multi-user)
11. Now the system databases (except master) will be loaded from the new created paths. Check new files location for system databases. Master will point to the right location. msdb, tempdb and model must be changed. If you will restore msdb you can specify at that time, the new location for the files. So here I will show how to move model and tempdb. For msdb is the same.

select * from sys.master_files

use master

go

Alter database tempdb modify file

(name=tempdev, filename='[drive]:\new_location\tempdb.mdf')

Alter database tempdb modify file

(name=tempdev2, filename='[drive]:\new_location\tempdev2.ndf')

Alter database tempdb modify file

(name=templog, filename='[drive]:\new_location\templog.ldf')

Alter database model modify file

(name=modeldev, filename='[drive]:\new_location\model.mdf')

Alter database model modify file

(name=modellog, filename='[drive]:\new_location\modellog.ldf')

12.    Stop SQL Server and move the model files from old location to new location. tempdb will be re-created on the new specified location at every SQL restart so no need to move the files.

13.    Start SQL and make sure that the system database point to the right locations.

select * from sys.master_files

14.     [OPTIONAL] Restore msdb database. I personally encountered problems running Database mail after msdb restore on SQL 2008 R2. I ended up leaving the msdb orginal files and migrated all jobs by scripting, created msdb.dbo.cdc_jobs table (because cdc was in place as well) and re-configured database mail.

15.    Remove folders created at step II.4.

16.    Test some SQL users for connection.

17.    Linked Servers will not work because their credentials where encrypted with Service master key from originated server. In order to fix this you need to backup service master key from [Server A], give permission on the file to your user and copy to [Server B] to restore it.

18.    Change the server name in system tables. @@servername variable will point to old server. It must be changed.

-- check servername with ID = 0. The queries will return old server name [SERVER A]. [SERVER B] will not be found. 

SELECT @@servername

EXEC sp_helpserver 'SERVER B'

EXEC sp_helpserver 'SERVER A'

SELECT srvname FROM sysservers where srvid = 0

-- DELETE old references to old servername.

EXEC sp_droplinkedsrvlogin 'SERVER A', null

EXEC sp_dropserver  'SERVER A'

-- ADD new server name: [SERVER B]

EXEC sp_addserver [SERVER B] , 'local' ,'duplicate_OK'

EXEC sp_addlinkedsrvlogin 'SERVER B', 'true'

-- RESTART SQL. [SERVER B] will replace the old server name.

SELECT @@servername

EXEC sp_helpserver 'SERVER B'

SELECT srvname FROM sysservers where srvid = 0

On versions below SQL 2008, you have to update also the originating server name from sysjobs as well. On higher versions, the reference in sysjobs is made by server_id which is always 0.

SELECT * FROM msdb.dbo.sysjobs

UPDATE msdb.dbo.sysjobs SET originating_server = @@SERVERNAME WHERE originating_server <> @@SERVERNAME

III. Remaining Steps to do on the current Production server – [Server A]

19. Perform Full backups for all user databases. It is a good idea to use backup compression and backup verification (RESTORE VERIFYONLY)
20. Perform Differential backups.
21. Copy all backups on [SERVER B].
22. Restore Full backups and differential backups on [SERVER B] with ‘NO RECOVERY’ option.
23. Perform Transactional backups.
24. Copy all tran backups on [SERVER B] and restore them using with ‘Recovery’ option.
25. And a last step, after you restore all user databases, keep in mind to change databases owners to ‘sa’ user, otherwise you will receive errors of executing sys schema objects like these ones:

The EXECUTE permission was denied on the object ‘’, database ‘mssqlsystemresource’, schema ‘sys’. (.Net SqlClient Data Provider)

USE Database_Name

EXEC sp_changedbowner 'sa'

GO