Migrating SQL Server Database
Migration of the SQL Server databases is a thing which every SQL DBA comes around often.
We have 2 ways of migrating the databases; the first one is Side by Side and the other one is In-place.
Most of the DBAs prefer Side by Side method of Migration since you have your DBs running on the Source Server until and unless you don't stop them manually.
Following are the steps we should follow while migrating the Databases( Side by Side method).
Pre-Migration Steps:
On the Source Server: This is the Server where your DB already exists.
1) Document the required details like the Source and Destination Server details which include Server Name, Database Name, Port number, DB size etc.
2) Once you have everything ready...You are all set to start. Login to your Source Server and have your DB(which has to be migrated). For eg.
BACKUP DATABASE DATABASE_NAME
Pre-Migration Steps:
On the Source Server: This is the Server where your DB already exists.
1) Document the required details like the Source and Destination Server details which include Server Name, Database Name, Port number, DB size etc.
2) Once you have everything ready...You are all set to start. Login to your Source Server and have your DB(which has to be migrated). For eg.
BACKUP DATABASE DATABASE_NAME
TO DISK='D:\MIGRATION\DB_NAME.BAK'
WITH STATS=10'
3) Once you have your DB backed up, you need to check for backing up Logins. We have various steps for scripting out the logins such as scripting the logins by packages, create script wizard etc.
3) Once you have your DB backed up, you need to check for backing up Logins. We have various steps for scripting out the logins such as scripting the logins by packages, create script wizard etc.
We also have sp_help_revlogin stored procedure which reads the system tables to generate the script for each logins and users with the passwords in encrypted form. sp_help_revlogin script is available on internet.
This can be installed on the Server and can be used. This SP makes your life easier. The syntax will be:
SP_HELP_REVLOGIN 'DATABASE_NAME'
N.B.: For those who don't have Sp_help_revlogin installed on your server, click here to get the code:
4) Once you are done with scripting out the users and login its now time to script out Jobs. You can simply right click to the job and script it out to a text pad or file.
Once you are done with all the above steps you are now ready to actually Migrate your DB/DBs.
Migration Steps:
On the Destination Server: This is the Server where your DB will be migrated.
1) Restore the Backup of the DB which you have taken from the Source Server. Syntax will be.
2) Once Restoration is done you now need to check for orphaned users in your newly migrated DB. You can run the below Stored Procedure.
SP_CHANGE_USERS_LOGIN 'REPORT'
This will give you a list of all the orphaned user in the database which can be fixed by:
-- If you already have a login id and password for this user,
-- fix it by doing:
EXEC SP_CHANGE_USERS_LOGIN 'AUTO_FIX', 'USER'
-- If you want to create a new login id and password for
-- this user, fix it by doing:
EXEC SP_CHANGE_USERS_LOGIN 'AUTO_FIX', 'USER', 'LOGIN', 'PASSWORD'
3) Once you have fixed the Orphaned users its now time to restore jobs and packages which you have scripted out from Source Server.
4) Post restoration you should re-index and update the stats. The below SPs will help you re-indexing and updating stats for all the tables in a Database.
USE DATABASE_NAME
GOEXEC SP_MSFOREACHTABLE@command1="PRINT '?' DBCC DBREINDEX ('?', ' ', 80)"
GOEXEC SP_UPDATESTATS
GO
This completes our DB Migration
SP_HELP_REVLOGIN 'DATABASE_NAME'
N.B.: For those who don't have Sp_help_revlogin installed on your server, click here to get the code:
4) Once you are done with scripting out the users and login its now time to script out Jobs. You can simply right click to the job and script it out to a text pad or file.
Once you are done with all the above steps you are now ready to actually Migrate your DB/DBs.
Migration Steps:
On the Destination Server: This is the Server where your DB will be migrated.
1) Restore the Backup of the DB which you have taken from the Source Server. Syntax will be.
2) Once Restoration is done you now need to check for orphaned users in your newly migrated DB. You can run the below Stored Procedure.
SP_CHANGE_USERS_LOGIN 'REPORT'
This will give you a list of all the orphaned user in the database which can be fixed by:
-- If you already have a login id and password for this user,
-- fix it by doing:
EXEC SP_CHANGE_USERS_LOGIN 'AUTO_FIX', 'USER'
-- If you want to create a new login id and password for
-- this user, fix it by doing:
EXEC SP_CHANGE_USERS_LOGIN 'AUTO_FIX', 'USER', 'LOGIN', 'PASSWORD'
3) Once you have fixed the Orphaned users its now time to restore jobs and packages which you have scripted out from Source Server.
4) Post restoration you should re-index and update the stats. The below SPs will help you re-indexing and updating stats for all the tables in a Database.
USE DATABASE_NAME
GOEXEC SP_MSFOREACHTABLE@command1="PRINT '?' DBCC DBREINDEX ('?', ' ', 80)"
GOEXEC SP_UPDATESTATS
GO
This completes our DB Migration
http://sqlgeneral.blogspot.in/2011/11/migrating-your-sql-server-database.html
No comments:
Post a Comment