Automate daily (newly created database) full backups; script help

[microosoft]

Hello all, thanks for reading this post.

I have a server which has Microsoft SQl Server and TSM TDPSql installed on it. i have around 50 SQL server databases on it with active usage. I run a full backup every month and differential backups every day.

But when a new SQL database is created, its backup fails because 'its full backup does not exist'. So I would like to make a little change to my sqldiff schedule script. Right now all it says is

%sql_dir%\tdpsqlc backup master full /tsmoptfile=%sql_dir%\dsm.opt /logfile=%sql_dir%\sqldiff.log >> %sql_dir%\sqldiffsched.log
%sql_dir%\tdpsqlc backup * diff

I would like to add a little script here that would take a full backups of the databases created that particular day along with full backup of master. And then, run the differential.

Something like select * from sys.databases where create date is 'today'. Can anyone help?

Thanks,
Ben

[moon-buddy]

My two cents

Just run the full backup manually unless you create new databases every hour! If you create new databases once a day, don't bother automating it.

On the other hand, I question why you have just one FULL backup a month - scary!

[microosoft]

Thanks for your reply.

My application creates databases every hour.

Thanks,
Ben

[moon-buddy]

When you say create every hour - do you mean a new instance on an existing database?

[microosoft]

Sorry , I should have been more clear. I have this application that is used by (public) users. Whenever a new user registers on our webpage, Application creates a new mssql database for him/her. We have a recent upsurge in our application usage and more and more users are registering. Hence more database creations, around 20-30 a day.

So, as I was saying before, I run all the database backups on to tape through TSM. I have no problem with the ones that have full backup, as the differentials run fine. But the ones that are newly created are failing on full backups. Right now, I am running full backups for the newly created ones manually every day. I was wondering if there is a way to automate it.

Thanks,
Ben

[moon-buddy]

Look at this post: http://adsm.org/forum/showthread.php...hlight=tdp+sql

You can use the script I posted - modifying it of course - to run at the end-of-day a full backup of the newly created DB PRIOR to DIFF backup.

[microosoft]

Thanks, Ed.

BTW, I was looking for something similar on the forum. I found this link very useful.

http://adsm.org/forum/showthread.php...080#post107080

Solved my purpose.

Thanks.