/*
Written by : Andre Pageot
Date : 06/06/2012
Description :
This procedure will backup all databases in a SQL server with the exception of reportserver
Each database will have its checkpoint marked and transactio log truncated before the backup occurs
Each Database will be on a 5 day rolling backup. Baseline on @BaseLineDay (Monday) and differential throughout the week
Monday's baseline will delete all previous backups in the rolling set,
so be sure to have them in another location if you need to revert
The rolling set will backup for two weeks rolling, two baselines and each baseline will contain a weeks worth of differentials
Mon Tues Wed Thurs Frid
B1 D1 D2 D3 D4--Base line 1 with differentials throughout the week
B2 D1 D2 D3 D4--Base line 2 with differentials throughout the week
B1 D1 D2 D3 D4--Overwrite baseline one
B2 D1 D2 D3 D4--Overwrite baseline 2
--This stragtey allows a 2 week rolling backup
*/
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDay VARCHAR(20) -- used for file name
DECLARE @BaseLineDay VARCHAR(10)
DECLARE @SQL varchar(max) --the backup script to execute
DECLARE @BaseLineNumber varchar(1) --Which baseline we are working with
SET @path = 'E:\SQL\BAK\'
SET @BaseLineDay = 'Monday'
if(DATEPART(wk, GETDATE()) % 2) = 0
BEGIN
--print 'even'
SET @BaseLineNumber = 2
END
ELSE
BEGIN
--print 'odd'
SET @BaseLineNumber = 1
END
SELECT @fileDay = (SELECT DATENAME (DW,GETDATE()) as Day)
DECLARE db_cursor CURSOR FOR
SELECT name = D.Name--QUOTENAME(D.name)
FROM sys.databases D
WHERE (
NOT D.database_id BETWEEN 1 AND 4 -- master, tempdb, model, and msdb
AND NOT D.name LIKE 'ReportServer%' -- Report Server
AND NOT D.is_distributor = 1 -- Replication
)
AND D.source_database_id IS NULL -- not a snapshot
AND D.state_desc = N'ONLINE' -- is online
AND D.user_access_desc = N'MULTI_USER' -- open for all users
AND D.is_read_only = 0;
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_SET_' + @BaseLineNumber + '.BAK'
SET @SQL = 'USE ' + @name
SET @SQL = @SQL + '
CHECKPOINT --forces the dirty pages in memory to be commited to the transaction log file'
SET @SQL = @SQL + '
DBCC SHRINKDATABASE (' + @name + '); --new way to truncate the log file (commit all transactions to the mdf)
--perform our backup depending on what day it is (Fridays have a new baseline)
IF (''' + @fileDay + ''' = ''' + @BaseLineDay + ''')
BEGIN
BACKUP DATABASE ' + @name + ' TO DISK = ''' + @fileName + ''' WITH INIT
END
ELSE
BEGIN
BACKUP DATABASE ' + @name + ' TO DISK = ''' + @fileName + ''' WITH DIFFERENTIAL
END
'
print @SQL
exec (@SQL)
--SET @fileName = @path + @name + '_' + @fileDay + '.BAK'
--DBCC SHRINKDATABASE (@name, TRUNCATEONLY);
--BACKUP DATABASE @name TO DISK = @fileName WITH INIT
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor