SQL server, SQLServerPedia Syndication

SQL backup mirroring (and splitting).

Did you know that you can mirror your SQL backups? This is good for those times when you need multiple backup copies. Like one local copy and another one on a network share.

You can even split backups over multiple files. If you have an IO bottleneck during backup, or if you are low on disk space, this backup splitting feature will come in handy. Below you can see my tests.

The backup file is 5.2 GB in size.

1)     Doing a normal backup:

backup database backupdb_dev to disk=’c:\dba\db_normal_bakup.bak’

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 2 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656250 pages in 67.109 seconds (76.397 MB/sec).

 

2)     Backuping up to two files on the same spindle:

backup database backupdb_dev

to disk=’c:\dba\db_bakup_spindle1_file1.bak’,

disk=’c:\dba\db_bakup_spindle1_file2.bak’

Processed 656248 pages for database ‘backupdb_dev’, file ‘Backupdb’ on file 1.

Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 63.322 seconds (80.966 MB/sec).
Each backup file was 2.6 GB in size.

 

3)     Backing up to two files on two different spindles:

backup database backupdb_dev

to disk=’c:\dba\db_bakup_spindle1_file1.bak’,

disk=’f:\dba\db_bakup_spindle2_file1.bak’

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 62.775 seconds (81.671 MB/sec).

 

Theoratically, the backups

4)     Doing a backup and mirroring to the same spindle.

backup database backupdb_dev

to disk=’c:\dba\db_backup_mirror1.bak’

mirror to disk=’c:\dba\db_backup_mirror2.bak’

with format

Processed 656248 pages for database 'backupdb_dev', file 'Backupdb' on file 1.
Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 95.500 seconds (53.685 MB/sec).

Both files were 5.2 GB in size. No surprise here. The backup took only 50% longer.

5)      Doing a backup and mirroring to different spindles.

backup database backupdb_dev

to disk=’c:\dba\db_backup_mirror1.bak’

mirror to disk=’f:\dba\db_backup_mirror2.bak’

with format

Processed 656248 pages for database ‘backupdb_dev’, file ‘Backupdb’ on file 1.

Processed 1 pages for database 'backupdb_dev', file 'Backupdb_log' on file 1.
BACKUP DATABASE successfully processed 656249 pages in 97.538 seconds (52.563 MB/sec).
About these ads
Standard

2 thoughts on “SQL backup mirroring (and splitting).

Thinking about someting? Leave a Reply...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s