Backing up all DBs using sp_msforeachdb

amikolaj tips and tricks

This will be the first in a series of post on a couple of my new favorite undocumented commands. I’m talking about sp_msforeachdb and sp_msforeachtable. These guys are awesome.

Recently I needed to backup all the databases on a SQL 2000 server. I could have created a maintenance plan and just used it as a one-off but I figured I’d get a little creative. I used sp_msforeachdb to back each DB up.

Check it out:

1
2
3
4
5
6
7
8
9
10
11
12
13

EXEC Sp_msforeachdb
' IF DB_ID(''?'') > 4
 Begin
BACKUP DATABASE [?] TO  DISK = N''\\server\Backups\?.BAK'' WITH NOFORMAT,
 INIT,  NAME = N''?-Full Database Backup'',SKIP, NOREWIND, NOUNLOAD
declare @backupSetId as int
 select @backupSetId = position from msdb..backupset where database_name=N''?''
and backup_set_id=(select max(backup_set_id)
from msdb..backupset  where database_name=N''?'' )
if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database ''''?'''' not found.'', 16, 1)
end
RESTORE VERIFYONLY FROM  DISK = N''\\server\Backups\?.BAK''
WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND End '
Share