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 '