Friday, May 04, 2012

Stripe SQL Backups on multiple files

-- FULL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)  
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName   

SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND 




--DIFFERENTIAL Backups
DECLARE @DBName SYSNAME, @BUFileName VARCHAR(256) , @BULocation VARCHAR(256), @SQLCommand NVARCHAR (1000)  
SET @DBName = 'mydbname'
SET @BUFileName = @DBName + '_' + DATENAME(dw, GETDATE()) +  '_' + DATENAME(dd, GETDATE()) + '_'
  + DATENAME(mm, GETDATE()) + DATENAME(YEAR, GETDATE()) +
  + '_' + DATENAME(hh, GETDATE()) + 'h' + DATENAME([MINUTE], GETDATE()) + 'm_Full_'
SET @BULocation = 'K:\MSSQL\Backups\'
-- PRINT @BUFileName   

SET @SQLCommand = 'BACKUP DATABASE ' + @DBName + ' TO
   DISK = ''' + @BULocation + @BUFileName + '1.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '2.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '3.BAK''' +',
   DISK = ''' + @BULocation + @BUFileName + '4.BAK''' +',
   WITH DIFFERENTIAL,
  NOFORMAT, INIT, NOREWIND, NOUNLOAD,
  COMPRESSION, STATS = 10'
-- PRINT @SQLCommand
EXEC sp_executeSQL @SQLCOMMAND