How to build a script to backup all databases in SQL Server?

Question

We need to backup 40 databases inside a SQL Server instance. 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?

Answer 1

Maybe you can check the sp_MSForEachDB stored procedure.

EXEC sp_MSForEachDB 'EXEC sp_backup ?'

Where the ? mark stands for the current database on each iteration.

Answer 2

Something like:

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'BACKUP ' + Name + ' To Disk=''c:\' + Name + '.bak'';'
    FROM sys.sysdatabases

EXEC(@SQL)

Answer 3

You said "verify" too... This would require a 2nd statement per database (RESTORE VERIFYONLY, as well the other answers offered.

I'd investigate using maintenance plans to include index maintenance too...

Answer 4

Use SqlBackupAndFTP. Select "Backup all non-system databases"