Calculating the Size of a Multi File Database Backup

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  August 2, 2010 at 4:42 pm

  #157123

  Hello Everyone

  Is there a way to calculate the size of the backup file, when I would like to split the backup file across a few different spindles? Is it just the size of the database divided by the number of files in the backup clause? I tested this and came up with two slightly different file sizes.

  Another question,

  How would one restore the database that has been split up to multiple files? I am assuming the restore will have to be in order.

  I would like to create a SSIS package that will calculate the size, and then check the amount of availabe space on the SAN before attempting the backup.

  Thanks in advance

  Andrew SQLDBA

• Jonathan Kehayias

  One Orange Chip

  Points: 26778

  More actions

  August 2, 2010 at 5:27 pm

  #1201312

  Striped backups require that all files be available to restore the database since the data is striped across the backup devices when the backup stream is being written. The resulting stripes will roughly be equal sized when the backup operation completes.

  Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
  My Blog | Twitter | MVP Profile
  Training | Consulting | Become a SQLskills Insider
  Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

• magasvs

  SSCertifiable

  Points: 7660

  More actions

  August 9, 2010 at 5:45 pm

  #1204459

  Hi,

  To restore the database that have been backed up to the multiple files you just need to list all devices:

  RESTORE DATABASE database_name FROM backup_device1, backup_device2...

• Richard M.

  SSCertifiable

  Points: 7401

  More actions

  August 10, 2010 at 7:17 am

  #1204751

  If you used the backup with multiple files, you would use a command like this to restore from multiple files:

  RESTORE DATABASE

  [DatabaseName]

  FROM

  DISK = N'Path_to_file_1.bak',

  DISK = N'Path_to_file_2.bak',

  ...

  DISK = N'Path_to_file_n.bak',

  WITH

  FILE = 1,

  RECOVERY,

  REPLACE,

  NOUNLOAD,

  STATS = 10

  GO

  You only would do a restore "in order" if you are restoring lets say a full backup and then some transaction log backup files. These last ones would need to be in order to successfully restore.

  To calculate the size of the backup you would need to know the amount of data that is backed up (free space is not backed up) and divide by the number of files used to backup, that will give you a rough estimate.

  _______________________________________________________________________
  For better assistance in answering your questions, click here[/url]