Generic Backups

I support developers.  In our dev/QA environment, people add, delete, and change the recovery model of databases all the time without telling me.  How do I keep track of which databases need to be backed up?

I don’t.  I back up everything.

Master has information on all databases.  That means master can give you a list of what needs to be backed up.  Of course, you don’t want to back up tempdb (because you can’t restore tempdb), and I haven’t customized model, so:

Declare @DBname varchar(255),
@RowsToProcess int,
@CurrentRow int,
@SQL nvarchar(max),
@today nvarchar(50),
@servername nvarchar(50)

set @today = convert(nvarchar,GETDATE(),12)
set @servername = REPLACE(@@SERVERNAME,’\’,’-‘)

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), dbname nvarchar(max) )
INSERT into #maintenancetemp (dbname) select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’
SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
SET @CurrentRow=@CurrentRow+1
SELECT
@DBname=dbname
FROM #maintenancetemp
WHERE RowID=@CurrentRow
set @SQL=’BACKUP DATABASE [‘ + @DBName + ‘] TO DISK = N”S:\backups\’ + @servername + ‘-‘ + @DBName + ‘-‘ + @today + ‘.bak” WITH FORMAT, INIT, NAME = N”Full ‘ + @DBName + ‘Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 10’
–print @SQL
EXEC SP_EXECUTESQL @SQL
END

drop table #maintenancetemp

Here’s the full backup script that loads that into a temp table and loops through the rows and backs them up.  You can, of course, customize this script to suit your preferred file name convention and backup location.  You can, of course, also plop that into a job or, if you’re running something like Windows Internal Database or SQL Express, run it via sqlcmd and Task Manager.

What about log backups?  If it’s just dev/QA data, is it important?  Is QA going to jump out of a window if they lose data?

I don’t ask.  I just back it up.  Master knows what’s in full recovery, too:

select name from sys.databases where name <> ‘tempdb’ and name <> ‘model’ and recovery_model IN (1, 2)

This log backup script assumes hourly backups, which might be overkill for your environment and might not.  I’ve seen “dev” and “QA” environments that were treated like prod because they were actually for training and the company lost money if training couldn’t take place, so I’d rather err on the side of being backed up.  Again, customize to suit your environment.

I’ve started running these against any new internal server-based SQL Server instance I find out about.  We do have instances of SQL Server 2005 Express and the like, for testing, so sqlcmd continues to be my friend.