TSQL Backup and Restore to and from Multiple Files

Backing up and restoring to multiple files can make your admin activities faster and can also test the throughput of your storage if you want to go silly.
A sample backup command sample for backing up and restoring 2 files is outlined below :

BACKUP DATABASE <DatabaseName>
TO DISK = 'C:\folder\<DatabaseName>_yyyymmdd_file1.bak'
,DISK = 'C:\folder\<DatabaseName>_yyyymmdd_file2.bak'
with init, nounload, stats=10, compression

Now run the following query to kill all connections in the destination database and then perform the multi file restore with placeholders for your own object names  :

ALTER DATABASE <DatabaseName>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
ALTER DATABASE  <DatabaseName>
SET MULTI_USER;

RESTORE DATABASE  <DatabaseName>
FROM
DISK = 'c:\folder\<DatabaseName>_yyyymmdd_file1.bak',
DISK = ' c:\folder\<DatabaseName>_yyyymmdd_file2.bak'
WITH STATS=10, REPLACE,
MOVE 'LogicalDataFileName' TO 'X:\MSSQL\DATA\PhysicalDataFileName.mdf',
MOVE 'LogicalLogFileName' TO 'X:\MSSQL\DATA\PhysicalLogFileName.ldf';

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

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