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.
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
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.
]]>