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.
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.
RESTORE DATABASE ABC FROM DISK='\\10.A.A.A\backups\ABC_1.bak', DISK='\\10.A.A.A\Backup_03102011_DB\ABC_2.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' GO
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’
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
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
Pages: 1 2