sqlrx logo
SQLRx Tip of the Month
 

April 2013
______________________________________________________________________________
SQL 2008    SQL Server Administration
 

   Backup database to one file or multiple files. Use these commands to back your databases up without using a maintenance plan. Backing up to multiple files is good to use for large databases and will break up a single large backup file to several smaller files. Good to use when large backups are hard to move across the network.

  
  

-- Backup a database to a single backup file

--

DECLARE @BackupFile NVARCHAR(200)

DECLARE @Timestamp VARCHAR(50)

-- create a timestamp for a unique name

SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),'-',''),':',''),' ','')

-- create backup file string

SET @BackupFile = 'D:\SQLBackups\AdventureWorks2012_Full1_'+@Timestamp+'.bak'

-- backup the database

BACKUP DATABASE AdventureWorks2012

TO DISK = @BackupFile

   WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup',

              SKIP, NOREWIND, NOUNLOAD, STATS = 10;

GO

 

 

--

-- Backup a database to multiple backup files

--

DECLARE @BackupFile1 NVARCHAR(200)

DECLARE @BackupFile2 NVARCHAR(200)

DECLARE @BackupFile3 NVARCHAR(200)

DECLARE @BackupFile4 NVARCHAR(200)

DECLARE @Timestamp VARCHAR(50)

-- create a timestamp for a unique name

SET @Timestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(), 20),'-',''),':',''),' ','')

-- create backup file strings

SET @BackupFile1 = 'D:\SQLBackups\AdventureWorks2012_Full1_'+@Timestamp+'.bak'

SET @BackupFile2 = 'D:\SQLBackups\AdventureWorks2012_Full2_'+@Timestamp+'.bak'

SET @BackupFile3 = 'D:\SQLBackups\AdventureWorks2012_Full3_'+@Timestamp+'.bak'

SET @BackupFile4 = 'D:\SQLBackups\AdventureWorks2012_Full4_'+@Timestamp+'.bak'

-- backup the database

BACKUP DATABASE [AdventureWorks2012]

TO DISK = @BackupFile1,

              DISK = @BackupFile2,

              DISK = @BackupFile3,

              DISK = @BackupFile4

WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup',

              SKIP, NOREWIND, NOUNLOAD, STATS = 10;

GO

 

 ______________________________________________________________________________
As always, don't forget to regularly monitor the 11 VitalSigns  used by SQLRx to debug performance bottlenecks.

If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month, email it to us!  If you missed any of our previous tips, you can view them here.

For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at [email protected].


Sincerely,

 

Lori Brown | SQLRx Senior Consultant | Integrated Services, Inc.
4144 N. Central Expwy, Suite 430  |  Dallas, TX  75204
Phone: 214.526.7680 x 113 | [email protected] 

 

 

Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit

 

25 Years of Credibility & Trust

 

Join our email list          Check out our events          SQLRx Downloads