How to Script a backup for each database on an MSSQL Engine?

Posted by Geo on Server Fault See other posts from Server Fault or by Geo
Published on 2009-08-04T18:29:00Z Indexed on 2010/03/22 22:01 UTC
Read the original article Hit count: 285

We need to backup 40 databases inside an MS SQL Server Engine. We backup each database with the following script:

BACKUP DATABASE [dbname1] TO  DISK = N'J:\SQLBACKUPS\dbname1.bak' WITH NOFORMAT, INIT,  NAME = N'dbname1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'dbname1' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'dbname1' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''dbname1'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'J:\SQLBACKUPS\dbname1.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

We will like to add to the script the functionality of taking each database and replacing it in the above script. Basically a script that will create and verify each database backup from an engine.

I am looking for something like this:

For each database in database-list
    sp_backup(database) // this is the call to the script above.
End For

any ideas?

© Server Fault or respective owner

Related posts about mssql

Related posts about windows-server-2003