Restoring a SQL Server Database – A Practical Example

Performing a
Database Restore is a part of a DBA’s daily life. A DBA may need to perform a
Restore due to various reasons such as recovery, refreshing a database for
testing purpose etc. Many times it can be difficult to
perform a Restore due to corrupted media, low disk space on the
server and so on. In this article, I will outline one approach which I used
to Restore the backup of the Production Database a test environment.


A couple of
days back, members of my support team approached me saying that they are unable
to refresh a database named ABC 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 machine we were had just 1 dedicated drive(D) for SQL Server which
was having just 70 GB of free space left on it.

Restore 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 a different domain than the staging and production boxes.

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:

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

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 example, if the size of a database is 76 GB then it will be divided into two equal
parts each of size 36 GB.

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

Drive D had 49.9 GB free space

Drive C had 55 GB free space

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

Pages: 1 2


No comments yet... Be the first to leave a reply!