Solved

Disk space required for SQL Server 2005 Restore

Posted on 2011-03-11
6
386 Views
Last Modified: 2012-08-13
Hello All,

This is a very straightforward question which I cannot seem to find the answer to in this forum or SQL Server Books Online.

We are using SQL Server 2005 SP3.
I need to restore a full backup of our Production database (PD) into our Prototype database (PY).  I have done this in the past and have all the scripts and processes I need.  However, we are starting to get tight on disk space so I want to be certain I have enough space.

Our system:

PD datafile 355 GB and PY datafile 211 GB on the same phyiscal disk which has 261 GB free
Current PD full backup file is 278 GB.

We have SQL Server backups run nightly to disk.  There is massive space available on the disk that holds the log files, so no restore issues there.

So, I see the following scenarios:
A.  New PY file will allocate 355 GB
B.  New PY file will allocate 278 GB
C.  Restore process will create a temp file and delete the existing PY file at the end of the process
D.  Restore process will immediately overwrite the existing PY file during the restore

My understanding:
If D is true we have plenty of disk space for either A or B.
If C is true I think it depends
   If B is true, I think I could purge 30+ GB out of the existing PY to bring the free space to 290+ making room.
   If A is true, I could be in trouble.  If these were independent databases, I would have no problem dropping PY.  However, they are part of our JDEdwards ERP system and I cringe about dropping a key database which may break a link somewhere in the JDE system databases.  I may have to consult with JDE gurus on that.

In the past there has been no issue on disk space, so I haven't bothered to watch and see what happens to the files.

Thank you for your comments/corrections/laughter =) at my naive understanding and situation.

Thanks,

Jeremy
0
Comment
Question by:WvrLthr
  • 3
  • 2
6 Comments
 
LVL 29

Expert Comment

by:Randy Downs
ID: 35112892
Maybe these will help.
http://msdn.microsoft.com/en-us/library/ms188223.aspx
http://msdn.microsoft.com/en-us/library/ms191315.aspx

 Intuitively the Replace option would not need a temporary file so should fit on your drive. If it were me, I'd call JDE for their opinion. If you can wait, you might try replacing the drive with something larger before the restore.
0
 
LVL 29

Accepted Solution

by:
Randy Downs earned 250 total points
ID: 35112938
You could test your theory by backing up a small test database $ restoring to see how much space it takes. You could do a real life restore test restoring to the big drive.
0
 
LVL 2

Assisted Solution

by:Umesh_Madap
Umesh_Madap earned 50 total points
ID: 35115932
as other guys suggested if possible replace the drive with higher space or SAN.

you can do once thing.

if you want to restore the database on non-production server, you can test once by restoring the with the prod backup, i don't think it required free space for team db.

As per my knowledge it should work i am sure.

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:WvrLthr
ID: 35129036
Number-1,

Thank you for the attempt to answer.  I was really looking for someone to definitively answer the question.  I had read those links and, like you, believed it would work by immediately replacing the file.  Your second post about doing a test was what I was hoping to avoid, but I decided that was going to be the way to know for sure, so I took the time this morning to create a new database and tested.

I am going to give points for your time, even though the answer was indirect.
0
 
LVL 1

Author Comment

by:WvrLthr
ID: 35129059
Umesh Madap,

Thank you for the attempt.  We are not going to add disk to the SAN anytime soon and restoring on a non-production server is not feasible.

I decided to take the time and create a test scenario with smaller databases.

Thanks for your time.
0
 
LVL 1

Author Closing Comment

by:WvrLthr
ID: 35129108
The specific questions were not answered definitively from experience.  Some pointers and options were provided.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
format nvarchar field as mm/dd/yyyy 4 69
Help with SQL joins 9 48
Isolation level in SQL server 3 49
How to place a condition in a filter criteria in t-sql (#2)? 10 47
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question