need a solution to backup if a new database is added

Question

need a solution to backup if a new database is added

Hadrian

Hall of Fame

Points: 3742
More actions
November 27, 2014 at 2:10 am

#295388

Dears,
I have this porblem to solve:
Each time a new database is created it has to start a job to backup this new db.
I dont want to use tiggers.
Thnaks,
Hadrian

Viewing 6 posts - 1 through 5 (of 5 total)

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

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

How about polling the sys.databases table (via a SQL Server Agent job) and checking the create_date column based on a specified time frame.

You could then backup any databases that are return by the initial query.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 2

Hadrian, do you want to backup the new database immediately after it is created? If so, why? Or do you want to add it to the list of databases to get backed up regularly as part of a daily process?

Hadrian Hall of Fame Points: 3742 More actions · Answer 3

yes it's a sharepoint farm and they wish to immediteley and automatically the new added database should be dumped starting a backup job.

tripleAxe SSCertifiable Points: 5706 More actions · Answer 4

In that case I'd go with the suggestion from "DBA From The Cold" and look into the sys.sysdatabases table for new databases based on the crdate column with a SQL Agent job running however frequently you need it. Remember to ignore tempdb. You could also query the msdb table backupset to check for recent backups for the database.

select MAX(backup_start_date) from [msdb].[dbo].[backupset] where database_name = 'NewDatabaseNameHere' and type='D'

Hadrian Hall of Fame Points: 3742 More actions · Answer 5

Thanks for your ideeas,but I'm thinking more to use Eventnotifications and Service Broker to start the job immediately.

Did you some iddeas who could be bring all together?

Thanks again,

Hadrian