SQL Server’s database backup across more than one file

Posted: December 31, 2008 in Sqlserver
You can stripe a SQL Server’s database backup across more than one file. Striping a SQL Server backup across more than one backup file can provide performance advantages as well as make it possible to backup a VLDB where there is no single drive available with enough free space. Striped backups are supported in SQL Server 2000 & SQL Server 2005.

— take a striped backup of the DB. Can be many more files than just two.
backup database northwind
to disk=’\\Server1\t$\northwind1.bak’,
disk=’\\Server2\h$\northwind2.bak’

— take a look at the logical files in the DB so we can move them on restore
restore filelistonly
from disk=’\\Server1\t$\northwind1.bak’,
disk=’\\Server2\h$\northwind2.bak’

— restore a DB from a striped backup
restore database northwind
from disk=’\\Server1\t$\northwind1.bak’,
disk=’\\Server2\h$\northwind2.bak’
with move ‘northwind_Data’ to ‘t:\northwind2.mdf’,
move ‘northwind_Log’ to ‘h:\northwind2.ldf’,

Advertisements

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