Backup to Multiple Directories?

Question

Backup to Multiple Directories?

Michael Valentine Jones

SSC Guru

Points: 64818
More actions
September 13, 2007 at 3:58 pm

#118594
Has anyone ever had a need to split backups to files in multiple directories?

I am building a general purpose stored procedure to do various types of backups, and I was wondering if the need to do this is common enough to make it worth the effort.

I'm not asking if it is possible to do this; I know it is. I just want to know if the need to backup to multiple directories is common enough to be worth the effort to program it into my procedure.

Here is an example of a command to backup to multiple directories. This will produce five backup files that will be equal in size, and about 1/5 the size of a full backup to a single file.
```
backup database [MyBigDatabase]
to
 disk = 'D:\BACKUP\MyBigDatabase_db_200709131454_001.BAK' ,
 disk = 'E:\BACKUP\MyBigDatabase_db_200709131454_002.BAK' ,
 disk = 'F:\BACKUP\MyBigDatabase_db_200709131454_003.BAK' ,
 disk = 'G:\BACKUP\MyBigDatabase_db_200709131454_004.BAK' ,
 disk = 'H:\BACKUP\MyBigDatabase_db_200709131454_005.BAK'
with
 init,
 stats = 5
```
```
 
```

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

I would have thought the only need to do this if is you do not have the space on a single drive to complete the backup, or you wanted to spread the i/o load of the backup across drives to decrease its performance hit. Doing this would I expect increase the speed of the backup if your backup window was tight.

Otherwise , why complicate the backup\recovery process if you do not need to?

HTH

george

---------------------------------------------------------------------

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

I know it is not needed for the vast majority of cases. I am trying to decide if it would be useful enough to build into a general purpose procedure that I hope will cover all backup situations.

I want to hear from anyone who has had a need to do this. How often have they done it? How many directories did they backup to at the same time? Why did they backup to multiple directories (performance, space, etc.)? How big were the databases?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

I use up to 4 directories for backups, based on database size. Each directory is on a separate drive that doesn't have any of the SQL data or log files on it. I use one drive for databases up to 1 GB, two drives up to 10 GB, and four drives for larger databases. After the backup to local drives is finished I move the files to a network share, and then update the physical_device_name field in msdb.dbo.backupmediafamily to the network location to simplify restores.

I had to get creative because I had a 200+GB database that took over 4 hours to backup over the network, and sometimes over 9 hours if other big jobs were running overnight. It takes only seven minutes to backup the same database to four local drives, and while the file copy afterward may take two hours it is a background file transfer that doesn't interfere with other database activity.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Did you backup to 4 local drives because you didn't have enough space for a full backup on a single drive?

Or were you doing it mainly for backup speed?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

I was backing up to a network fileshare that was about 1.5TB, so size was not an issue. It was an IDE-based piece of junk, so throughput was an issue. Having the BACKUP command running for hours, holding locks, and interfering with other maintenance jobs was an issue.

The backup to multiple local drives on the server was a huge speedup (4 hours best case vs 7 minutes), and the file copy operation is a simple OS file copy that doesn't hold locks or otherwise interfere with SQL. There is obviously a slight drain on network bandwidth and disk activity, but it's not really noticable.

JeremyE SSCoach Points: 16201 More actions · Answer 6

If the db's are a backend for a purchased application, it's nice to split the files to make FTPing easier for troubleshooting but that is usually an ad hoc backup.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

That is always a sore point with me, especially when someone wants me to FTP them a 100 GB database.

If they want test data, I tell them to buy a large USB drive, send it to me with a prepaid return shipping envelope, and provide their PGP public key to encrypt the file with.