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'

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

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

sp_change_users_login @Action=’Report’

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

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


Leave a comment

Your email address will not be published.