SQL Server Restoring Database – One Such Approach in an Emergency

Performing a SQL Server Restoring Database is a part of a DBA’s daily life. He might need to perform a Restore due to various reasons such as Recovery, Refreshing a database for testing purpose and so on. However many times it becomes too difficult to perform a Restore due to reasons such as corrupted media, low disk space on the server and so on. In this exclusive article, a long time Author and an SQL DBA from Larsen&Toubro InfoTech India discusses one such approach which he used to Restore the backup of the Production Database on his test environment when he ran short of space.

Scenario:

A couple of days back, members of my support team approached me saying that they are unable to refresh a database named ABC present on the OLTP development environment with the copy of the backup of the same database from the Production server. The backup copy taken from the Production server was around 75 GB in size. On our dev OLTP machine we were having just 1 dedicated drive(D) for SQL Server which was having just 70 GB of free space left on it.

Solution:

After having a detailed look, I came to a conclusion that I could not free up any space on the D drive of the dev machine. One important point to mention is that our entire dev machines were in different domain as compared to the staging as well as the production box. I logged on to the Production Server and decided to split the backup of the database named ABC into two equal parts using the T-SQL as shown below:

BACKUP DATABASE ABC

TO DISK=’B:\DB Backups\ABC_1.bak’,

DISK=’B:\DB Backups\ABC_2.bak’

GO

Once the above block of T-SQL statement is executed, it splits the Full Backup of the database named ABC present on the Production server into two equal parts. For e.g. if the size of my database is 76 GB then it will be divided into two equal parts each of size 36 GB.

Once the full backup gets splitted into two equal parts, I then perform the RAR of them. Obviously first performing the RAR and then moving them to the different server would be pretty much faster than moving the original copy of a much bigger size. Once the splitted files got zipped successfully I then moved them onto my staging server. This is because the staging box was pretty good in terms of resources on it and also since both the Production and Staging servers were in different Data Centers due to good hardware the copying process worked much faster. Now as discussed earlier the free space available on my dev OLTP box was just 70 GB whereas the backup copy was 75 GB therefore it was not possible for me to transfer the zipped copies of the full backup on the dev OLTP box. I was having another box which was used as a SSIS Dev server which was having two disks which were having a good amount of free space. They are as follows:

Drive D was having 49.9 GB free space

Drive C was having around 55 GB free space

The dev machines were standalone machines and not a cluster therefore there were no issues for me to copy 1 zipped file of the backup onto the C drive.

I then moved one copy of the zipped file onto the folder named backups present 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 query 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 SQL Server\MSSQL.1\MSSQL\Data\ABC_Data_1.mdf’,

MOVE ‘ABC_Log’ TO ‘D:\Program Files\Microsoft SQL Server\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 got 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’

Next step involves 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 you the list of all the Orphaned users present in the database named ABC. In order to fix it 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 on the same are most welcome.

Regards

Satnam Singh

Like us on FaceBook  |  Follow us on Twitter

Follow me on FaceBook| Join the fastest growing SQL Server group on FaceBook