Selectively excluding certain databases in 2005 Maintenance Plan

Question

Selectively excluding certain databases in 2005 Maintenance Plan

Private Gripweed

Right there with Babe

Points: 772
More actions
May 22, 2008 at 1:09 pm

#380663

Hello all. Running SQL 2005 SP2.
I have a few databases on my server that reside in, and must be backed up to, EFS encrypted folders, as the client requires that all at-rest files be encrypted.
This requires that I manually select particular databases to back up in the Maintenance Plan. Of course the issue with this is that new databases are not automatically added as they would be if I used the "All User Databases" option.
I found a very simple T-SQL script that uses a cursor to accomplish this.
****************************************
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
************************************
Pretty simple, eh? I could just exclude my "secure" databases along with the system databases and run it, and run a separate job for the "secure" databases, pointing them to the EFS folder.
Problem is, I would like each database to get backed up to its own subfolder.
Any ideas on how I can accomplish that?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

mobasha SSCertifiable Points: 5334 More actions · Answer 1

u can create a sub folder on the system with the same name off the database u r backup, then pass the path as dynamic sql.

..>>..

MobashA

Private Gripweed Right there with Babe Points: 772 More actions · Answer 2

Thanks. Not really a SQL guy- this has been thrown at me. Can you detail a little further?

Thanks.

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 3

I think that I have a solution that can do this for you. It's a backup stored procedure that supports exclusion of databases as well as creation of subdirectories.

EXECUTE dbo.DatabaseBackup @databases = 'DB1,DB2', @Directory = 'D:\Backup', @BackupType = 'FULL', @verify = 'Y', @CleanupTime = 24

EXECUTE dbo.DatabaseBackup @databases = 'USER_DATABASES,-DB1,-DB2', @Directory = 'E:\Backup', @BackupType = 'FULL', @verify = 'Y', @CleanupTime = 24

http://ola.hallengren.com/sql-server-backup.html

Please let me know if you have any questions.

Ola Hallengren

http://ola.hallengren.com

mobasha SSCertifiable Points: 5334 More actions · Answer 4

SET @fileName ='C:\Backup\' + @name+'\'+@name + '_' + @fileDate + '.BAK'

..>>..

MobashA

Private Gripweed Right there with Babe Points: 772 More actions · Answer 5

Oh, man. Of course.

Thanks. That's very helpful. Thanks for the quick replies. :hehe::)

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 6

You also need something to create the folders. I'm using xp_create_subdir in my stored procedure. It's undocumented, but it's the one that the Maintenance Plans are using.

Ola Hallengren

http://ola.hallengren.com

alimcitp SSC Eights! Points: 930 More actions · Answer 7

Depends what you are trying to do. If you are trying to create the maintenance plan for backing up the transaction log of all databases on sever than maintenance plan wizard will only show you those databases which recovery model is set to full or bulklogged.

Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate

Private Gripweed Right there with Babe Points: 772 More actions · Answer 8

Your job looks very impressive. But the concept of tokens and how they are used is way above my head. I am not sure I will be able to build the job as I have no idea how to do that.

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 9

Thanks, Private Gripweed. Just send me an e-mail and I'll help you to get started. It's very easy.

http://ola.hallengren.com/About.html

Ola Hallengren

http://ola.hallengren.com

Private Gripweed Right there with Babe Points: 772 More actions · Answer 10

Thnaks. Let me look a little further, now that I reaized that your script created the jobs. If I am still stuck I will e-mail you. Thanks for your very kind replies, your very well-written page, and all of your assistance. You're a real pal to the SQL community and to this grasshopper/ Kohai..