Monday 11 February 2013

Backup DB using SQL statement


-- Name of the DB to backup, and destination directory
DECLARE @DBName VARCHAR(50)           
DECLARE @Destination VARCHAR(2000)

SELECT @DBName = 'DB',
  @Destination = '\\TestMachine\'
+ REPLACE(@@SERVERNAME, '\','_')
+ '_' + REPLACE(@DBName, ' ','_')
+ '_' + LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 121), '-',''),':',''),' ','_'), 13) + '.bak'

--Sample Destination: \\TestMachine\DBServer_DB_20130211_1630.bak

PRINT 'Backup ' + QUOTENAME(@@SERVERNAME) + '.' + QUOTENAME(@DBName) + ' to file "' + ISNULL(@Destination, 'NULL') + '"'

BACKUP DATABASE @DBName TO DISK = @Destination WITH COPY_ONLY, COMPRESSION, STATS=5

N.B. Compression is not supported with standard editions.

Comprehensive TSQL options can be found at:
http://msdn.microsoft.com/en-us/library/ms186865.aspx

No comments:

Post a Comment