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.

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.

Pages: 1 2




Related Articles :

4 Responses to “Restoring a SQL Server Database – A Practical Example”

  1. hi, I wanna know why you change the database owner to ‘sa’? Does the owner differs from Prod to Dev?

  2. When you restore it will create with the user logged in. If that user login is disabled or the user leaves the DB will have problems. Changing to sa owner keeps this from having any issues like this

  3. It depends on what version of Sql Server you are using but you also could have compressed the backup file.

  4. My backup is broken piece of glass!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |