Migrate the SQL Server 2008

  • A+
Categories:SQL Server

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:

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:

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

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

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

Now restore the model and msdb databases:

Restart the instance again and restore all other databases.

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

Then restart the instance again.

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: