Restoring Backup -Size Issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Backups » Restoring Backup -Size Issue

Restoring Backup -Size Issue

Rate Topic

Display Mode

Topic Options

Author

Message

facticatech

Posted Tuesday, December 28, 2010 3:48 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, October 09, 2012 11:50 PM
Points: 23, Visits: 108

I have a backup file,which is 30 gb in size . When I restore, it creates 5 db's each of 20gb. So total of 100 gb space is required on my harddrive. But available space on my harddrive is only 80 gb.

4 db's are restored and 5th one fails.
Wondering if I can shrink the db size or anyway handle this situation.

I guess this is because of the default db size or autogrowth option.

Can anyone help me on this with steps.

Post #1039685

Ray Mond

Posted Tuesday, December 28, 2010 4:21 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 2:28 AM
Points: 343, Visits: 1,510

When you restore a database from a backup set, the size of the data files and transaction log files will be identical to that of the database at the time the backup was taken. Depending on your needs, you could try using SQL Virtual Restore, which allows you to restore a database that's smaller than what a normal restore would require.

Ray Mond

TLogInfo - the only FREE tool to analyse your transaction logs. Download here.
SQL BAK Reader - the only FREE tool to inspect your SQL Server backup files without using SQL Server. Download here.

Post #1039700

ALZDBA

Posted Tuesday, December 28, 2010 4:32 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:17 AM
Points: 6,862, Visits: 8,049

As Ray Mond stated, to restore a db, you'll need free space at least equal to the size of the database files at backup time.
If your bak file contains backups of a number of databases, you can restore them individually.
Since in this case you are short on space, alternatives are:
- restore the db to another drive letter
e.g.

RESTORE DATABASE yourdb
	FROM DISK = 'x:\MSSQL\BACKUP\yourdbFull.BAK'
	WITH MOVE 'yourdb_Data' TO 'E:\Mssql\Data\yourdb_DATA.mdf',
	     MOVE 'yourdb_Data_2' TO 'F:\Mssql\Data\yourdb_DATA_2_Data.ndf' ,	
	     MOVE 'yourdb_Log' TO 'G:\Mssql\Data\yourdb_log.LDF' ,
	(no)RECOVERY

- shrink a restored db to free up space for the next restore.

It all depends on the goal of your restores !

If you shrink a db, or some db-files, the data will not be well organized and will need index rebuilds ! which will cause your db file(s) to grow again !

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #1039705

« Prev Topic | Next Topic »

Permissions