Red Gate forums :: View topic - Exclude Databases option
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Backup 7
SQL Backup 7 forum

Exclude Databases option

Search in SQL Backup 7 forum
Post new topic   Reply to topic
Jump to:  
Author Message
etsuchiya



Joined: 29 Oct 2012
Posts: 3

PostPosted: Fri Dec 07, 2012 8:53 pm    Post subject: Exclude Databases option Reply with quote

Hi
I scheduled a backup job using the EXCLUDE OPTION to set aside thos databases i don't need to regularly backup.... Job is running well without any problems

Our SQL Server manages hundreds of databases some of which don't need to be backed up (demo databases) and therefore are excluded. If a DB is no longer required it is manually excluded from the respective backup jobs.

Question: How does SQL Backup keep track of the Databases that are backed up?. Is this list stored at some location?
I'm looking for a way to automatically exclude a database when it's no longer required to be backed up and update the job accordingly. Deleting the datase obviously would solve it but not a good alternative

Thanks in advanced
Back to top
View user's profile Send private message
petey



Joined: 24 Apr 2005
Posts: 2276

PostPosted: Sat Dec 08, 2012 2:30 am    Post subject: Reply with quote

Each time a backup is ran with the EXCLUDE option, SQL Backup retrieves a list of active databases from SQL Server. It then removes the excluded databases from that list, then proceeds to back up the remaining databases.

You would need to maintain the exclusion list yourself, and add modify the SQL Backup command accordingly. Perhaps the following code snippet might provide some hints on how to do this:

Code:
CREATE TABLE SQBTEST_DBsToExclude (db nvarchar(128))
GO
INSERT INTO SQBTEST_DBsToExclude VALUES ('db1')
INSERT INTO SQBTEST_DBsToExclude VALUES ('db2')
INSERT INTO SQBTEST_DBsToExclude VALUES ('db3')
INSERT INTO SQBTEST_DBsToExclude VALUES ('db4')
INSERT INTO SQBTEST_DBsToExclude VALUES ('db5')
GO

DECLARE @cmd NVARCHAR(MAX)
DECLARE @dblist NVARCHAR(MAX)

SET @dblist =
(SELECT TOP 1
 SUBSTRING(
 (
  SELECT ( ', ' + db)
  FROM SQBTEST_DBsToExclude t1
  FOR XML PATH('')
 ), 3, 2048)
FROM SQBTEST_DBsToExclude)

SET @cmd = '-sql "BACKUP DATABASES EXCLUDE [' + @dblist + '] TO DISK = [<AUTO>]"'
SELECT @cmd
-- EXEC master..sqlbackup @cmd

_________________
Peter Yeoh
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 7
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group