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 ' |
