up vote 1 down vote favorite
share [g+] share [fb]

I have a MS SQL 2008 database backup maintenance plan that does a Full backup and then two differential backups throughout the day. If a new database has been created the differential chokes because there is no full to base it off of (at least, this is my guess).

I see that I can manually create a database backup by going to the database and choosing it as a task, but this doesn't auto name it like the plan does and I don't want to mess up the flow of this. Manually kicking of the Full plan again seems like it is a lot of overhead for a couple of small databases.

What is the typical way to handle this situation?

link|improve this question
78% accept rate
feedback

2 Answers

up vote 0 down vote

Your assumption about the differential is correct, because there's no full backup off the new database.

If I create a new database and there is an existing differential plan as you have, I'd kick off a manual full backup for that new database as a matter of course, just to keep things consistent.

However, you can get creative with scripting out the existing maintenance task, and adding in some error checking to see whether the database has a full backup already, and if not, creating one.

I prefer this route, because you have full control over the script.

EDIT: This link has some more detail about how to achieve this.

link|improve this answer
So when kicking of the full ... how is the naming done to make sure it won't interfere with the current scheduled plan that automatically names them? – Kyle Brandt♦ Jul 29 '10 at 0:23
Looking at it now what is throwing me off is that the other databases have the proper destination filled in .. .the new one doesn't have it filled in... – Kyle Brandt♦ Jul 29 '10 at 1:19
I've added a link to my answer for you to have a look at. – Randolph West Jul 29 '10 at 1:42
Seems like a possible viable long term alternative, but would still like an answer within the scope of the current scheme ... – Kyle Brandt♦ Jul 29 '10 at 1:56
feedback
up vote 0 down vote accepted

Okay I think I figured it out:

  1. In Management Studio, Go to Management::Maintenance Plans::Full Backups (in my case) and right click that backup task and chose edit.
  2. Click view T-SQL , find the relevant backup commands for particular databases
  3. Run powershell or cmd.exe and run sqlcmd and connect to the DB. Then run the backup commands within sqlcmd
link|improve this answer
It's a good alternative. – Randolph West Jul 29 '10 at 3:18
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.