for every new database...do this...

Question

Post reply

for every new database...do this...

b4better

SSChasing Mays

Points: 627
More actions
April 14, 2008 at 8:27 am

#205703

is this difficult to do?
for every new database that is added, then a backup job is created automatically?
take every new database 'name' from sysdatabases, and somehow pass that into
a backup script.
let this run once a day; say at 11pm, and that should cover creating a basic backup
job.
i don't know how to do this exactly... any pointers? would i need to create a cursor
for starters?
forgive me if this is a stupid question.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

If you created a maintenance plan for "All User Databases", it would pick up any newly added databases.

As an alternative, I suppose you could create a trigger that created a SQL agent job.

b4better SSChasing Mays Points: 627 More actions · Answer 2

phew... 🙂

maintenance plan... what a time saver.

thanks!

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

If you would like to do the same with T-SQL I have a stored procedure that could help you.

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

Ola Hallengren

http://ola.hallengren.com

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

With 2005 DDL triggers you could use a Server scoped trigger on Create Database to create a job to backup that database. I have not done this, but it could be done.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 5

Or, better yet, just write your backup job to walk through sys.databases, and backup every online user database.

Eddie Wuerch
MCM: SQL

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

This is just how my backup stored procedure works. You just use it like this.

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

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

Ola Hallengren

http://ola.hallengren.com

b4better SSChasing Mays Points: 627 More actions · Answer 7

cool. DDL Triggers are really handy.

i'm putting together all kinds of automation now.