Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

4 Answers

You could do this using an undocumented database looping procedure.

use [master]
go

set quoted_identifier off

exec sp_MSforeachdb "
if ( '?' not in ( 'master', 'msdb', 'model', 'tempdb' ) )
begin

BACKUP DATABASE [?] TO  DISK = N'J:\SQLBACKUPS\?.bak' WITH NOFORMAT, INIT,  NAME = N'?-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

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'J:\SQLBACKUPS\?.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND

end
"
share|improve this answer
add comment

Something like:

  1. Create a cursor for `SELECT name FROM master..sysdatabases WHERE name NOT IN ('list','of','dbs','to','skip')
  2. Iterate through that and on each loop:
    1. construct your script as an ad-hoc SQL string
    2. (have the above ad-hoc SQL log any results in a small logging DB somewhere)
    3. run it with EXEC (@sql)
  3. Output a simple report based on the stored results for sanity checking

should do the trick.

There are also a number of products on the market that provide very flexible backup options for SQL Server, which may be better for production use if you script gets more complicated (though they are no doubt not cheap).

share|improve this answer
add comment

I know this is not a direct response to your question since you want something with your specific script but I thought I throw this out there.

If you have the full version of MS SQL, not MS SQL Express, you can do the Maintenance Plan route. Here's a PDF from Sunbelt Software that gives a pretty good idea how to use it, you can select here the databases that you want to backup: SQL-2005-maintenance-plan.pdf

share|improve this answer
add comment

I would improve on your script:

:: copyonlybackup.bat
IF EXIST "%1_COPYONLY.BAK" (
  sqlcmd -U sa -P xxxxxxx -S SQL-DB -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY,INIT;"
echo Overwriting previous COPY_ONLY backup of %1 .
) ELSE (
  sqlcmd -U sa -P xxxxxxx -S SQL-DB -Q "BACKUP DATABASE %1 TO DISK = '%1_COPYONLY.BAK' WITH COPY_ONLY;"
  echo Creating new COPY_ONLY backup of %1 .
)

And then call it with

:: RUNSCRIPTS.bat
F:
CD "F:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup"

@REM  Doing simple copies just in case
call COPYONLYBACKUP.bat db1
call COPYONLYBACKUP.bat db2
call COPYONLYBACKUP.bat db3
...

Finally, if you need a loop, do it in the RUNSCRIPTS.bat file I show above, using something kind of like this:

for /f %%a IN (myfile.txt) do call copyonlybackup.bat %%a

To generate the myfile.txt, you can probably get SQL to generate it somehow:

EXEC sp_msForEachDB 'PRINT ''?'''
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.