-- Standard Backup Script
-- Author: Mike Hillwig, October 12, 2008

-- Due to the nature of our DR/standby server strategy, this script should work as
-- a standard across all MSSQL servers. It was written to be used on both SQL 2000 and 2005.
-- When the last SQL 2000 server is upgraded, we can change the CURSOR declaration to pull
-- from sys.databases.   

-- Our strategy is to back up all databases in two places to disk, locally and on a remote file system. 
-- Once the database backups are written to disk, Veritas will then back them up to long-term storage.
-- This works in our environment because we have lots of small databases. I would revisit this strategy
-- when our databases start to reach the 100 gigabyte range. 

-- We do this twice a day, and we use a standard filename structure, keeping two backups. This allows us to
-- automate the recovery process on the standby servers. 

-- This script requires a few parameters. The first one WILL change every installation. The next four
-- MAY vary depending on the server configuration. The last two (labels for AM/PM) shouldn't change, but
-- I left that flexible in case the need arises. 


DECLARE @v_LocalDrive nvarchar(1)
DECLARE @v_LocalFolder nvarchar(20)
DECLARE @v_RemoteServer nvarchar(20)
DECLARE @v_RemoteDrive nvarchar(1)
DECLARE @v_RemoteFolder nvarchar(20)
DECLARE @v_AMLabel nvarchar(20)
DECLARE @v_PMLabel nvarchar(20)

SELECT @v_RemoteServer = 'SECONDARYSERVER' -- This is the name of the standby server where the backups are stored
SELECT @v_RemoteDrive = 'D' -- Drive on the standby server where backups are stored
SELECT @v_RemoteFolder = 'SQLBACKUP' -- Folder on remote server to store backup files
SELECT @v_LocalDrive = 'D' -- Drive on THIS (primary) server where local backups are stored
SELECT @v_LocalFolder = 'SQLBACKUP' -- Folder on THIS server to store backup files
SELECT @v_AMLabel = 'AM'
SELECT @v_PMLabel = 'PM'

-- Determine if this is an AM or PM backup and set the label accordingly. 

DECLARE @v_CurrentLabel nvarchar(20)
SELECT @v_CurrentLabel = @v_AMLabel WHERE DATEPART(hour, current_timestamp) < 12 
SELECT @v_CurrentLabel = @v_PMLabel WHERE DATEPART(hour, current_timestamp) >= 12

-- Open a cursor of the databases on this system, excluding tempdb, model, and demo databases

DECLARE @v_dbname nvarchar(30)
DECLARE @v_SQL nvarchar(1000)
DECLARE c_databases CURSOR FOR
SELECT [name]
FROM sysdatabases
WHERE name NOT IN ('tempdb', 'model', 'northwind', 'pubs', 'adventureworks')
ORDER BY [name]

-- Note: backup filenames are constructed like this: SOURCESERVER_DESTINATIONSERVER_DATABASENAME_AMPMLABEL.BAK
-- This allows us to know what server was the source of the data and where it was stored. 

-- We loop through the cursor twice. The first time stores everything on the local drive. That should always
-- be the fastest to complete. The backup to the remote happens on the second pass. This will still allow the
-- local backup to complete even if the standby server is down. 

OPEN c_databases
FETCH NEXT FROM c_databases INTO @v_dbname
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SELECT @v_SQL = 'BACKUP DATABASE [' + @v_dbname + '] to DISK = ''' + @v_LocalDrive + ':\' + @v_LocalFolder + '\' + @@servername+ '_' + @@servername + '_' + @v_dbname + '_' + @v_CurrentLabel + '.BAK'' WITH INIT'
		PRINT (@v_SQL)
		EXEC (@v_SQL)

		
	END
	FETCH NEXT FROM c_databases INTO @v_dbname
END

CLOSE c_databases

OPEN c_databases
FETCH NEXT FROM c_databases INTO @v_dbname
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SELECT @v_SQL = 'BACKUP DATABASE [' + @v_dbname + '] to DISK = ''\\' + @v_RemoteServer + '\' + @v_RemoteDrive + '$\' + @v_RemoteFolder + '\' + @@servername+ '_' + @v_RemoteServer + '_' + @v_dbname + '_' + @v_CurrentLabel + '.BAK'' WITH INIT'
		PRINT (@v_SQL)
		EXEC (@v_SQL)
		
	END
	FETCH NEXT FROM c_databases INTO @v_dbname
END

CLOSE c_databases



DEALLOCATE c_databases