Saturday, December 15, 2007

Lazy DBA: Backup all your databases using a script

I have a compilation of scripts which I use on a daily basis to help me make my work a bit easier. I always mention during my presentations that the laziest people are administrators who happen to have a programming background. Here's one that I frequently use. A message on my instant messenger popped up asking me how to quickly perform backups of databases in SQL Server. I told him to write a BACKUP DATABASE command. Now, he was telling me about how much time it would take since they have like hundreds of databases. The solution: write a script. The script below performs a backup of all the databases on a SQL Server 2000 instance and dumps it in a local folder.

DECLARE @IDENT INT, @sql VARCHAR(1000), @DBNAME VARCHAR(200)
SELECT @IDENT=MIN(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')

WHILE @IDENT IS NOT NULL
BEGIN
SELECT @DBNAME = NAME FROM SYSDATABASES WHERE DBID = @IDENT
/*Change disk location here as required*/
SELECT @SQL = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''F\BACKUP\'+@DBNAME+'.BAK'' WITH INIT, STATS=10'
PRINT "==========================================="
EXEC (@SQL)
PRINT "Backup for database " + @DBNAME + " has been created"
SELECT @IDENT=min(DBID) FROM SYSDATABASES WHERE [DBID] > 0 AND DBID>@IDENT AND NAME NOT IN ('PUBS', 'NORTHWIND', 'TEMPDB')
END

This excludes the tempdb, Northwind and Pubs databases should you have it in your instance. Restoring is, of course, a different story. You need to start with the system databases (master and msdb, in my case) before you can restore the user databases. In a future blog post, I'll have a script to read the backups generated by this script and restore them all. See how lazy I can be?

2 comments:

HulaGirl said...

This is a sweet little script. I've modified it for use with SQL Server '05 and use it at home and at the office. Thanks!

bassplayer said...

Hi HulaGirl,

Great to hear that my blog is of great value to you. One thing about this script is that it works with any edition of SQL Server - even the Express and Embedded Editions. You see, Express Edition does not come with the SQL Server Agent service so you cannot automate the script using SQL Server jobs but nobody is stopping you from using Task Scheduler to run this script using osql/sqlcmd

Google