Restoring a SQL Server Database – A Practical Example

I then moved one copy of the zipped file onto the folder named backups which was on the C drive of the dev SSIS server and the other zipped file onto the folder named Backup_03102011_DB present on the D drive. I gave Full permissions on both these folders.

I then unzipped the two backup files on the dev SSIS server. Once done, I then logged on to the Dev OLTP machine and against the database named ABC I executed the following T-SQL statement to restore the database.


 FROM DISK='\\10.A.A.A\backups\ABC_1.bak', 


WITH MOVE 'ABC_Data' TO 'D:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ABC_Data_1.mdf', 

MOVE 'ABC_Log' TO 'D:\Program Files\Microsoft SQLServer\MSSQL.1\MSSQL\Data\ABC_Log.ldf'


Where 10.A.A.A is the IP Address of the dev machine.

Once the above block of T-SQL code was executed successfully, I then changed the owner of the database to sa by executing the following query against the database named ABC.

Exec sp_changedbowner ‘sa’

The next step involved mapping the Orphaned users. In order to find the Orphaned users, you need to execute the below T-SQL query against the ABC database on dev OLTP machine

sp_change_users_login @Action=’Report’

Once the above query is executed, it will list all the Orphaned users present in the database named ABC. In order to fix this you will need to execute the below T-SQL query:

exec sp_change_users_login @Action=’update_one’, @UserNamePattern=’User Name’, @LoginName=’Login Name’;

I hope you all have enjoyed reading this article. Any suggestions or comments on this process would be most welcome.

Pages: 1 2


No comments yet... Be the first to leave a reply!