SQL Server database migration.

Backup restore approach:: Taking full backup from the source then restoring to destination server.

Backup restore approach: this is generally most common approach taken when full database migration is planned as in this case all database objects from one instance gets migrated to another instance.

· 

1^st : We have to take a full backup of the database.  Before taking a full backup for restoration activity it is a good practice to keep it in Restricted User mode. Please note do not put the database into single user mode. If absolutely necessary before putting the database into single user mode please stop SQL Server Agent services before doing same.

When taking a database full backup we should prefer taking copy only backup. As it should not hamper the current backup plan which is schedule for that database.

Taking full backup through SSMS GUI.

In destination please add a .bak file to take a backup.

2^nd step: Once sql database full backup is taken transfer the backup file to destination server.

Open SSMS right click on Database click to restore database choose the backup file once the correct file is selected then in the  database drop down window you will be able to see the target database name. Go to option and check the mdf and ldf file location before hitting ok.

3^rd : After the full database backup is restore successfully we need to migrate the logins from the sources to target server.

To migrate the sql logins 1^st step you need to run a script that will create a store procedure which will help to migrate the logins below details:

Below script applies from SQL 2005 version and above.

The above script

Note: This script creates two stored procedures in the master database. The procedures are named sp_hexadecimal and sp_help_revlogin.

Once the store procedure is create need to exec it source server.

EXEC sp_help_revlogin

The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

On destination server, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.

Open a new Query Editor window, and then run the output script that is generated in the source server.

Then yours logins will get migrated.

4^th step: After the login is transferred: You need to check if there is any orphan users in the database to check it please ran the below query;

exec sp_change_users_login 'report'.

If in the output you find there are orphan users you need to fix those,

Querry for fixing orphan users;

EXEC sp_change_users_login 'update_one',  'db_login1',  'db_login1';