Calculating the Size of a Multi File Database Backup

• 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]