Compresses, encrypts, secures and monitors SQL Server backups.
Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey
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
- Posts: 3
- Joined: Mon Oct 29, 2012 9:48 pm
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: Select all
CREATE TABLE SQBTEST_DBsToExclude (db nvarchar(128))
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')
DECLARE @cmd NVARCHAR(MAX)
DECLARE @dblist NVARCHAR(MAX)
SET @dblist =
(SELECT TOP 1
SELECT ( ', ' + db)
FROM SQBTEST_DBsToExclude t1
FOR XML PATH('')
), 3, 2048)
SET @cmd = '-sql "BACKUP DATABASES EXCLUDE [' + @dblist + '] TO DISK = [<AUTO>]"'
-- EXEC master..sqlbackup @cmd
SQL Backup Consultant Developer
Associate, Yohz Software
- SQL Backup goodies under the hood, updated for version 7
- Posts: 2233
- Joined: Sun Apr 24, 2005 12:34 pm
Return to SQL Backup 7
Who is online
Users browsing this forum: No registered users and 0 guests