Backup 120 databases - Script? 2

[jdam]

Hi guys

I am hosting 120 databases on SQL server 2000 standard edition. Currently to backup the databases I stop the SQL Service, backup the MDF and LDF files using backup software, then restart the SQL service. I now need to have 24/7 uptime.

To remove the blackout period used for backups I need to run a backup from inside SQL server, then backup the backup.

Is there a script or something I can use that queries the master DB for the names of the databases, then backs them all up, one at a time in a loop or something like that?

 

[DBASkippack]

I recommend creating SQL Maintenance Plan (read BOL - Simple to Set up in SQL Server) But if you really want a stored proc, I've attached one -- this will backup your LOGS & Databases w/out any downtime. You can execute this concurrent w/ your online & batch applications.

Here ya go --

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
drop PROCEDURE spr_backup_all_db
go
CREATE PROCEDURE spr_backup_all_db
AS
-- Get date for operation start
PRINT 'Process Started'
SELECT GETDATE()

-- create variables
DECLARE @command varchar(400),
@database varchar(30)

-- start cursor to fetch all db's that can have log dumps
DECLARE cur_database CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE [name] NOT IN ('tempdb', 'master', 'pubs', 'northwind')
FOR read only
OPEN cur_database
FETCH NEXT FROM cur_database INTO @database
PRINT '****************************************'
PRINT 'Setting Truncate on Checkpoint to False'
PRINT '****************************************'
PRINT ' '
WHILE @@fetch_status = 0
-- ensure that dboption 'truncate' is off
BEGIN
SELECT @command = 'sp_dboption '''+ @database +''' , ''trunc. log on chkpt.'', ''false'''
EXECUTE (@command)
-- get next database
FETCH NEXT FROM cur_database INTO @database
END
-- Clean up cursor
CLOSE cur_database
DEALLOCATE cur_database

PRINT '************************************************'
PRINT '************************************************'
PRINT ' Begin End of Day Backups'
PRINT '************************************************'
PRINT '************************************************'
PRINT ' '

-- create cursor for all db's except tempdb and master
DECLARE cur_database CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE [name] NOT IN ('tempdb', 'master', 'pubs', 'northwind')
FOR read only
OPEN cur_database
FETCH NEXT FROM cur_database INTO @database
WHILE @@fetch_status = 0
BEGIN
-- print current database name
PRINT ' '
PRINT '****************'
PRINT @database
PRINT '****************'
PRINT ' '
-- Check if log backup device exists
IF (SELECT count(*) FROM master..sysdevices WHERE cntrltype = 2 AND name = @database + 'LogBackup') = 0
BEGIN
-- If log device NOT exists check for data device (count = 0)
IF (SELECT count(*) FROM master..sysdevices WHERE cntrltype = 2 AND name = @database + 'Backup') = 0
BEGIN
-- If data device AND log device NOT exists (count = 0) create data and log device and backup up both with overwrite
SELECT @command = 'sp_addumpdevice ''disk'', ''' + @database + 'LogBackUp'', ''e:\' + @database + 'LogBackup.bak'''
EXECUTE (@command)
PRINT ' '
PRINT 'Creating Log Backup Device'
SELECT @command = 'sp_addumpdevice ''disk'', ''' + @database + 'BackUp'', ''e:\' + @database + 'Backup.bak'''
EXECUTE (@command)
PRINT ' '
PRINT 'Creating Database Backup Device'
SELECT @command = 'BACKUP DATABASE ' + @database + ' TO ' + @database + 'Backup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Database Backed Up and Initialized'
SELECT @command = 'BACKUP LOG ' + @database + ' TO ' + @database + 'LogBackup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Transaction Log Backed Up and Initialized'
END
ELSE
-- If data device DOES exits AND log device NOT exits create log device and dump log with overwrite
BEGIN
SELECT @command = 'sp_addumpdevice ''disk'', '''+ @database + 'LogBackUp'', ''e:\' + @database + 'LogBackup.bak'''
EXECUTE (@command)
PRINT ' '
PRINT 'Creating Log Backup Device'
SELECT @command = 'BACKUP LOG ' + @database + ' TO ' + @database + 'LogBackup WITH init'
EXECUTE (@command)
-- IF the transaction dump fails for any reason do a database dump and then the transaction dump
IF (@@error = 3032)
BEGIN
SELECT @command = 'BACKUP DATABASE ' + @database + ' TO ' + @database + 'Backup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Backing up Database due to error in log backup'
SELECT @command = 'BACKUP LOG ' + @database + ' TO ' + @database + 'LogBackup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Transaction Log Backed Up and Initialized'
END
ELSE
BEGIN
PRINT ' '
PRINT 'Transaction Log Backed Up and Initialized'
END
END
END
ELSE
-- If log device DOES exists check for data device
BEGIN
IF (SELECT count(*)FROM master..sysdevices WHERE cntrltype = 2 AND name = @database + 'Backup') = 0
-- If log device DOES exists AND data device NOT exists (count = 0) Create data device and backup both with overwrite
BEGIN
SELECT @command = 'sp_addumpdevice ''disk'', '''+ @database + 'BackUp'', ''e:\' + @database + 'Backup.bak'''
EXECUTE (@command)
PRINT ' '
PRINT 'Creating Database Backup Device'
SELECT @command = 'BACKUP DATABASE ' + @database + ' TO ' + @database + 'Backup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Database Backed Up and Initialized'
SELECT @command = 'BACKUP LOG ' + @database + ' TO ' + @database + 'LogBackup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Transaction Log Backed Up and Initialized'
END
ELSE
-- if log device DOES exist AND data device DOES exist, dump log with append
BEGIN
SELECT @command = 'BACKUP LOG ' + @database + ' TO ' + @database + 'LogBackup'
EXECUTE (@command)
IF (@@error = 3032)
-- IF the transaction dump fails for any reason do a database dump and then the transaction dump
BEGIN
SELECT @command = 'BACKUP DATABASE ' + @database + ' TO ' + @database + 'Backup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Backing up Database due to error in log backup'
SELECT @command = 'BACKUP LOG ' + @database + ' TO ' + @database + 'LogBackup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Transaction Log Backed Up and Initialized'
END
ELSE
BEGIN
PRINT ' '
PRINT 'Transaction Log Backed Up and Initialized'
END

PRINT ' '
PRINT 'Transaction Log Backed Up With Append'
END
END -- get next db
FETCH NEXT FROM cur_database INTO @database
END
-- Clean up cursor
CLOSE cur_database
DEALLOCATE cur_database
-- move end of date backup sets
PRINT ' '
PRINT '***********************************'
PRINT 'Moving Backup files *.BAK'
PRINT '***********************************'
PRINT ' '
PRINT 'Deleting Old Archive Set'
-- delete old set
SELECT @command = 'master..xp_cmdshell ''del e:\Archive\*.bak'''
EXECUTE (@command)
-- copy new set to archive
PRINT ' '
PRINT 'Copying New Set to Archive Folder'
SELECT @command = 'master..xp_cmdshell ''copy e:\*.bak e:\Archive\'''
EXECUTE (@command)

-- create cursor for new backup set
DECLARE cur_database CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE [name] NOT IN ('tempdb')
FOR read only
OPEN cur_database
FETCH NEXT FROM cur_database INTO @database
WHILE @@fetch_status = 0
BEGIN
PRINT ' '
PRINT '****************'
PRINT @database
PRINT '****************'
PRINT ' '
-- backup all db execept temp with overwirte
IF @database = 'Master'
-- Test if master has a backup device
BEGIN
IF (SELECT count(*)FROM master..sysdevices WHERE cntrltype = 2 AND name = @database + 'Backup') = 0
BEGIN
SELECT @command = 'sp_addumpdevice ''disk'', ''' + @database + 'BackUp'', ''e:\' + @database + 'backup.bak'''
EXECUTE (@command)
PRINT ' '
PRINT 'Creating Database Backup Device'
SELECT @command = 'BACKUP DATABASE ' + @database + ' TO ' + @database + 'Backup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Database Backed Up and Initialized'
END
ELSE
BEGIN
SELECT @command = 'BACKUP DATABASE ' + @database + ' TO ' + @database + 'Backup WITH init'
EXECUTE (@command)
PRINT ' '
PRINT 'Database Backed Up and Initialized'
END
END
ELSE
SELECT @command = 'BACKUP DATABASE ' + @database + ' TO ' + @database + 'Backup WITH init'
EXECUTE (@command)
PRINT 'Backing up Database With Init'
-- Backup all logs except for master with overwrite to restart the days logs
IF @database = 'Master'
BEGIN
PRINT 'NO LOG BACKUP FOR MASTER'
END
ELSE
BEGIN
SELECT @command = 'BACKUP LOG ' + @database + ' TO ' + @database + 'LogBackup WITH INIT'
EXECUTE (@command)
PRINT 'Backing up Log With Init'
END
-- Get next database
FETCH NEXT FROM cur_database INTO @database
END
-- clean up
CLOSE cur_database
DEALLOCATE cur_database

PRINT 'Backups Completed'
SELECT getdate()



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[jdam]

WOW

That is huge!!

Right now I've gone ahead and set up an Maintence Plan to do the backup, but I would like something else in order to backup:

Full - once per week.
Diff - every night.

sort of a:
for each NAME in Master.sysdatabases
do the backup.

nice and simple...

Suggestions?

 

[SQLBill]

Check out FAQ183-4107. It shows the commands MSForEachDb and MSForEachTable.

-SQLBill

Posting advice: FAQ481-4875

 

[mrdenny]

Do your self a favor and write your own scripts to do the backups. Don't use the maint plans. The sp_MSForEachDB will work nicly for you.

And don't forget about transaction log backups (unless you are in simple recovery mode).

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLWilts]

I agree with MrDenny - script them and MSForEachDB and MSForEachTable, although undocumented (and therefore unsupported - may be "depreciated" in future service packs) are superb tools - I use them extensively.

 

[jdam]

much better.

Now to get it to work. Note: I'm fairly inexperienced when it comes to using stored-proc's/

so something like this...

CODE

EXEC sp_MSforeachdb @command1 = 'backup database " + dbname + "to disk= "\\backupserv\BackupShare\DowDataServ\" + dbname + ".bak'
go

how do I get the database name from the sp in order to fill in the backup command?

 

[jdam]

DUH

Maybe I should try reading the FAQ completely first.

Except I get an error message

CODE
exec sp_msforeachdb @command1='BACKUP DATABASE ? TO DISK = "G:\?.bak"'
go


gives me an error like:
sytax error near 'DATABASE.bak'

any suggestions?

 

[jdam]

Again DUH

Got the syntax fixed up.

Here's a followup question. Now that this is enabled, how would I backup a database that has a numeric name? The command sipped my numbered DB's then started with A etc.

 

[Catadmin]

My idea is kind of slipshod, but it should work.

Try saving the db name in a variable, then the T-SQL statement plus the dbname in another variable, then EXEC @MyVar.

See if that works.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[jdam]

Found the answer:

CODE

exec sp_msforeachdb @command1='IF ''?'' <> ''tempdb'' BACKUP DATABASE [?] to disk=''\\backupserv\BackupShare\DowDataServ\FULL\?.bak'''

go

also for Differential:

CODE

exec sp_msforeachdb @command1= 'IF ''?'' <> ''tempdb'' BACKUP DATABASE [?] to
disk=''\\backupserv\BackupShare\DowDataServ\DIFF1\?.bak''
WITH DIFFERENTIAL'
go


Now scheduled and running fine. Thanks everyone and here are some stars!