Easy Multiple Database Backups

bbaker · December 23, 2005 9:41PM

This really seems like a great solid product but I am baffled by the lack of easy multiple database backups. From what I gather itâ€™s possible to backup multiple databases using a specialized stored procedure but this seems rather messy. With over 400 SQL databases, I'm not about to try to create 400 individualized backups!

Is â€œpoint and clickâ€ multiple database backup something thatâ€™s going to be added in a future release? As a potential customer I can say we would be much more interested and inclined to buy with such a feature. Without that key piece though I have to say that we are stuck using the MS SQL 2000 maintenance plans.

Brad Baker

petey · December 24, 2005 3:16AM

In the next version, the backup syntax has been enhanced to support multiple database backups. E.g.

BACKUP DATABASES [*] TO DISK = ....

BACKUP DATABASES [pubs, northwind, master, msdb] TO DISK = ....

BACKUP DATABASES EXCLUDE [master, model, msdb] TO DISK = ...

There is a wizard to assist you in setting up a SQL Server Agent job to run these multiple database backup.

bbaker · December 24, 2005 10:10AM

Currently we have seven SQL maintenance plans with 20-30 databases in each plan. Maintenance Plan 1 runs in Monday and does a full backup, Plan 2 runs on Tuesday and does a full backup, Plan 3 runs on Wednesday etc. Then every day we do incremental backups for all 7 plans.

The reason for breaking up the backups so much is to limit the amount of time that the database backups run. I.E. We don't want a database backup consuming all the server resources for hours and hours at a time (even if it is during less busy hours). As an application service provider, our customers access our systems all hours of the day (and night) thus any prolonged hit in performance has to be kept to a minimum.

Based on what you said below about the syntax it sounds like we would still be manually writing and maintaining 7 different backup queries per server. So for instance

BACKUP DATABASES [database1,database 2,.....database20] TO DISK
BACKUP DATABASES [database21,database 22,.....database40] TO DISK
BACKUP DATABASES [database41,database 42,.....database60] TO DISK

Think about how painful maintaining database lists would become using this approach. To make things even more painful, our databases have a naming convention: customer#_X_shortname. (For example A0001_S_redgate) Try typing 200 of those without making a mistake!

It would take forever to get them all typed in. I suppose we could somehow export a list of names, reformat it, and break it up but itâ€™s still going to be a major headache to maintain. On the other hand if you have a GUI with a list of databases with checkboxes it becomes much easier. So for instance:

[X] A0001_S_redgate
[X] A0001_T_redgate
[X] A0002_S_microsoft
[ ] A0002_T_microsot
[X] A0003_S_netscape
[X] A0003_T_netscape
[X] A0004_S_sun
[X] A0004_T_sun

You can quickly tell in the list above that A0002_T_microsoft is not being backed up. And itâ€™s super easy to just go through and add or remove databases. Thatâ€™s what we are looking for not some clumsy hand written stored procedure.

bbaker · December 24, 2005 10:20AM

I guess what I am saying is that for a small number of databases maintaining a handwritten stored procedure is fine. But for anybody with a sizable number of databases this approach becomes unwieldy and impractical.

I hope at some point you will make multiple database backups as simple as single database backups with a point and click interface. If so then I would definitely be interested in trying out a demo and discussing pricing. Until then though Iâ€™m afraid we can't even consider your product.

Best Regards
Brad Baker

petey · December 24, 2005 11:13AM

Do you mean something like this (http://www.yohz.com/sqb4backup.gif)?

One limitation you might encounter is the length of the command that gets generated, which might exceed the maximum permissible length, depending on how long your database names are.

bbaker · December 25, 2005 7:19PM

Yes. Essentially thatâ€™s what we are looking for. (The native Microsoft SQL maintenance plans have a similar system for selecting multiple databases.)

We want to define multiple backup "jobs" each with their own set of databases, and have them run at diferent times.

Brad

bbaker · December 25, 2005 7:22PM

Is that screen shot from a competing product? a future version of Redgate SQL Backup? or something else entirely?

Brad

petey · December 25, 2005 11:47PM

SQL Backup 4.

bbaker · December 26, 2005 9:43AM

I am running SQL Backup 4 Beta 2 and I don't see a screen that looks like that? As far as I can tell Beta 2 is the latest beta available on the red-gate ftp site. And when I attempted to download a trial the red-gate website indicated the latest version was 3.x. Am I missing something or is this in an unreleased beta?

Brad

petey · December 26, 2005 11:48AM

The screenshot was from the Schedule Backup wizard, available in Beta 2.

swjohnson · December 28, 2005 2:41PM

Brad,

Yes, it would be easier if it was in the interface but I too have over 300 databases on my primary SQL Server (several others have over 100 databases each) and I didn't want a custom procedure for each one. Also, I do a full backup every night for my primary server and it only takes a few hours. The script I use is generic and I use it on all my servers for their backups. You should be able to modify it fairly easily for your use.

With that said would it make more sense to do a full backup once a week and then differential backups every night if you don't want to impact performance very much?

My script that is on each server in the Master DB and called from a scheduled job:

CREATE  PROCEDURE usp_SQLBackup
	@BackupPath varchar(255),
	@CopyToPath varchar(255),
	@SvrName varchar(50),
	@RetainDays tinyint=7,
	@CompressLvl tinyint=3,
	@SendCompletionMail bit=0,
	@NotifyAddr varchar(100)='sqlserverdb@xxxxxxx.com'
AS
/*********************************************************
This stored procedure gets all of the databases
and generates the necessary text for executing
Red Gate's SQL Backup utillity v3.x.  

@BackupPath is the path to put the backup files
@CopyToPath is the path to copy backup files after done
@SvrName name of the server this should report
@RetainDays is the number of days to retain (Max 255)
@CompresssLvl 1=Default, 2=High, 3=Max
@SendCompletionMail 0=Don't Send, 1=Send notice this uses EasyMail SMTP components
@NotifyAddr is the email address to notify 

HISTORY:


***********************************************************/
--declare Variables
DECLARE @dbName SYSNAME
DECLARE @MyString VARCHAR(4000)
DECLARE @Result VARCHAR(4000)
DECLARE @BeginTime DATETIME
DECLARE @EndTime DATETIME
DECLARE @failFlag bit
DECLARE @failCount int

SET NOCOUNT ON

--Create temporary tables for use below
CREATE TABLE #ResultDatabase (DBName sysname, BUResult varchar(4000),BeginTime datetime,EndTime datetime)
CREATE TABLE #ResultString (message varchar (4000))  


--Initialize variables
SET @failFlag = 0  
SET @failCount=0
SELECT @BeginTime=Getdate()

--Open Cursor of databases to backup
DECLARE c1 CURSOR FOR SELECT [Name] FROM master.dbo.sysdatabases WHERE [Name] NOT IN ('pubs', 'tempdb', 'Northwind') ORDER BY [Name]
OPEN c1
FETCH NEXT FROM c1 INTO @dbName
WHILE @@fetch_status &lt;&gt; -1
	BEGIN
	--Generate string for BackupSQL
	SELECT @MyString= '-SQL "BACKUP DATABASE [' + @dbName + '] TO DISK = ''' + @BackupPath + '&lt;AUTO&gt;'' 
         WITH NAME = ''Database (' + @dbName + '), ' + cast(getdate() as varchar(30)) + ''', DESCRIPTION = ''Full Backup on ' + cast(getdate() as varchar(30)) + ' Database: ' + @dbName + ' Instance: (local)  Server: ' + @SvrName + ''', 
         PASSWORD = ''mypasswordNOT'', VERIFY, ERASEFILES = ' + cast(@RetainDays as varchar(3)) + ', MAILTO_ONERROR = ''' + @NotifyAddr + ''', COMPRESSION = ' + cast(@CompressLvl as varchar(1)) + '" -E'

	SELECT @MyString
		--Execute backup of specified database
		INSERT INTO #ResultString
		EXEC master..sqlbackup @Mystring
		
		--Check results of backup
		SELECT @EndTime=GetDate()
		SELECT @failCount = count(*) FROM #ResultString WHERE patindex('%error%', message) &gt; 0  
		
		--If successful insert sucess entry, otherwise set failFlag and note in table
		IF @failCount &gt; 0 
		  BEGIN  
 			INSERT INTO #ResultDatabase (DBName, BUResult,BeginTime,EndTime)
			SELECT @DBName,message,@BeginTime,@EndTime FROM #resultstring  
    		SET @failFlag = 1  
	      END  
    	ELSE 
          BEGIN  
 			INSERT INTO #ResultDatabase (DBName, BUResult,BeginTime,EndTime)
			SELECT @DBName,'Backup Successful',@BeginTime,@EndTime FROM #resultstring
          END  
 	--Cleanup and get next record
	DELETE FROM #ResultString 
	FETCH NEXT FROM c1 INTO @dbname
	END
CLOSE c1
DEALLOCATE c1

--Do notifications
If @SendCompletionMail=1 
	BEGIN
	EXEC Spammer.dbo.usp_InsertSpam2 'SJ','sqlserverdb@xxxxxxxx.com',@SvrName,'sqlserveragent@xxxxxxxx.com',@SvrName,'Full Backup done processing please check',@ProjectID =0,@DateAdded ='1/1/1900'
	END

--Clean up temporary tables
DROP TABLE #ResultDatabase
DROP TABLE #ResultString


GO

Good luck!

Easy Multiple Database Backups

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk