Migrate the SQL Server 2008

Recently I migrated one SQL Server 2008 from one server to another with different directories, and did encounter some problems. Finally I resolved all of them, and though maybe I should make a note.

Before the migration, in the original server, you'd better create a SQL Server ID with sysadmin privilege, and run below statement to backup the SMK in SSMS:

BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file' 
ENCRYPTION BY PASSWORD = 'password'

Next need to backup all the databases including the system databases, and install the new SQL Server with the same version in the new target server, then transfer the backup files to the new server.

The first step is always to place the instance to single user mode and restore the master database.  If you're not familiar with this step, please visit the official webpage: Configure Server Startup Options.

Please note: DO NOT LEAVE ANY SPACE BEFOER OR AFTER THE -m;

Use sqlcmd -E -S to connect to the specific instance, and run:

RESTORE DATABASE master FROM DISK="D:\IBM_DBA\sqlserver_backup\master_backup_2015_08_25_230003_8593750.bak" WITH replace,
move 'master' to "D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf",
move 'mastlog' to "D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf"
GO

After the restore, the instance will be terminated automatically, now start it with below option:

net start MSSQLSERVER /m /c /T3608

Use the created SQL Server ID to connect to the instance, and run below commands:

ALTER DATABASE [model] MODIFY FILE(NAME=modeldev,FILENAME='D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf')
GO
ALTER DATABASE model MODIFY FILE(NAME=modellog,FILENAME='D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf')
GO

ALTER DATABASE msdb MODIFY FILE(NAME=MSDBData,FILENAME='D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf')
GO
ALTER DATABASE msdb MODIFY FILE(NAME=MSDBLog,FILENAME='D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf')
GO

ALTER DATABASE tempdb MODIFY FILE(NAME=tempdev,FILENAME='D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE(NAME=templog,FILENAME='D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.ldf');
GO

Then restart the SQL Server instance to make the change take effect.

Now restore the model and msdb databases:

RESTORE DATABASE model FROM DISK="D:\IBM_DBA\sqlserver_backup\model_backup_2015_08_25_230003_8750000.bak" WITH replace,
move 'modeldev' to "D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf",
move 'modellog' to "D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf"
GO

RESTORE DATABASE msdb FROM DISK="D:\IBM_DBA\sqlserver_backup\msdb_backup_2015_08_25_230003_8750000.bak" WITH replace,
move 'MSDBData' to "D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf",
move 'MSDBLog' to "D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf"
GO

Restart the instance again and restore all other databases.

You will got some errors in the system event log, so run below commands:

RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password' FORCE;
GO
ALTER DATABASE [msdb] SET ENABLE_BROKER;
GO

Then restart the instance again.