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




Array

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 |