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?

... and restores; how do you restore with a script?

bohdan_trotsenko
– bohdan_trotsenko

2014-06-25 11:10:15 +00:00
Commented Jun 25, 2014 at 11:10 — bohdan_trotsenko
– bohdan_trotsenko, Commented Jun 25, 2014 at 11:10

Ruslan · Accepted Answer · 2009-08-05 10:09:43Z

2

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

answered Aug 5, 2009 at 10:09

Ruslan

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2015-08-20 04:40:52Z

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.

edited Aug 20, 2015 at 4:40

CommunityBot

11 silver badge

answered Aug 4, 2009 at 19:50

Jhonny D. Cano -Leftware-

18.1k15 gold badges84 silver badges103 bronze badges

Comments

gbn · Accepted Answer · 2009-08-04 20:02:01Z

1

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

answered Aug 4, 2009 at 20:02

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

Alan Jackson · Accepted Answer · 2009-08-04 19:52:01Z

0

Something like:

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

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

EXEC(@SQL)

answered Aug 4, 2009 at 19:52

Alan Jackson

6,5212 gold badges33 silver badges32 bronze badges

Comments

Mark R. · Accepted Answer · 2013-01-28 17:17:59Z

0

SQL Backup Master has the ability to back up all databases on a given server instance and then send the backups to Dropbox, FTP, Amazon S3, Google Drive, or local/network folder.

answered Jan 28, 2013 at 17:17

Mark R.

1593 silver badges4 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related