Help with initial SQL database backup

[kevinbanovic]

Hi all,

I'm hoping that someone on here can help me. Here's the scenario and the issue I'm facing:

We have multiple SQL servers here in our environment. Each SQL server hosts multiple SQL instances. The main (default) instance on each box is the instance I am dealing with. The main instance on each server has >100 databases of varying sizes. It is common occurance that multiple DBs get created over the course of a day. Problem is, if the DB is created AFTER the BUEXEC full backup runs, the log backups fail (because the DB has never been backed up) until the next full backup of the server runs (every week on Saturday night). This leaves us with a DB in a potentially vulnerable state for up to a week.

I'm wondering if there is a way to automate a full backup of a database upon creation. In other words, can we set a stored procedure in SQL that will create a backup job on our BUEXEC server to backup the newly created database automatically? I'm not really sure how to handle this. SQL creates a .PS1 file that will launch BEMCLI on the backup server and run the job? Any other way to handle this issue?

Thanks in advance for any advice/direction,

-K

[CraigV · Accepted solution]

I've never seen this as part of any version of BE...application-aware backups basically. Might be worth heading over to the Ideas section and adding it in.

The only other thing I can think of is to create a backup job and place it on hold, or set it to be run manually. Then create a script on BEMCLI that can be called to run the job...all manual intervention unfortunately.

THanks!

[pkh · Accepted solution]

What you are proposing is to run the full backup of a newly created database separately and then run the log backup as part of your daily job.  I am not sure whether it can be done, but definitely it would mess up the backup chain and complicate your recovery.

Since there are a lot of changes, I would suggest that you run daily full backups.  Recovering a database from logs is quite time-consuming.  Suppose you have to recover a database on a Friday.  You are going to have a lot of logs to go through.