SQL backup scheduling

[NBUAdmin]

I want to create a SQL policy for automatic scheduled backups. I want to be able to have 4 schedules in the policy with different retention levels.

daily differentials for 1 month

weekly fulls for 3 months

monthly fulls for 2 years

yearly fulls for 8 years

When I set it up like this all the schedules get the retention of the Application backup schedule and not the automatic schedule. Also I can't seem to get the differential to only run on the daily schedule. 

I've created seperate policies for each retention with 2 different bch files, one for full and one for differential but this seems like a pain. Is it posible to do this in one policy instead of 4?

[Omar_Villa]

Hi,

   Unfortunaly for SQL backups the best way to do it is just like you did with 4 splited policies, this is because of the automatic schedule who depends/work-with, with the default one. About the differential backups, for SQL backups only fulls are available. Dont forget to create the policies for the SQL logs.

Hope this helps.

Regards

[Omar_Villa]

Here is a tip from the Netbackup MSSQL Admin Guide - Page 20

Tip

If you put multiple batch files in the same MS-SQL-Server policy, they will execute

during each Automatic Backup session for that MS-SQL-Server policy. If you have a

variety of SQL Server backup operations that you wish to have executed on

different schedules, then consider creating multiple MS-SQL-Server policies with

differing Automatic Backup schedules; and then assign each batch file to the policy

that uses the desired Automatic Backup schedule.

[Stumpr2]

Omar is spot on. However I would like to add that you can combine more than 1 server into a policy. for instance you could have a script called yearly.bat and as long as it is in the default SQL location for scripts and named exactly the same on several servers and you are using $ALL for the db listing, then you can place as many servers into the policy as you desire.

[msoucy]

Hi,

I want to be able to automate backup/restores of full and differential backups of MS SQL 2005 database. I currently have the backup/restore of a full via the *.bch files I created in the Backup/Restore SQL Objects GUI. What I need to figure out is how to get a different done for both backup/restore without having to use the GUI to create the batch file. So far I have yet to find any explicit information on this. We are wanting to automate this fully, i.e. create database, take offline via net stop "mssqlserver" /y then copy off database to sum/diff it after backup/restore, bring online via net start "mssqlserver" and net start "sqlserveragent" then do backup (I created the policy via the wizard but we are planning on doing this via automation too so info on that would be helpful) and restore, take database offline, diff/sum them, bring online, perform database modification via sqlcmd -i <scriptname> then take offline, copy over, online it, backup/restore, offline, diff/sum, etc....

Are there any examples of doing this for differentials? I see that when I used the SQL Object GUI for backup/restore in the SQL Agent that it puts in system timestamp info like the following:

OBJECTTYPE DATABASE

DATABASE "Test_Database"

NBIMAGE "<hostname>.MSSQL7.<HOSTNAME>.db.Test_Database.~.7.001of001.20070621104847..C"

DUMPOPTION INCREMENTAL

DATABASE "Test_Database"

NBIMAGE "<hostname>.MSSQL7.<HOSTNAME>.inc.Test_Database.~.7.001of001.20070621111538..C"

I would assume it'd be a bit of work but point me in the right direction and I'll go from there. I assume if I can find NBIMAGE via one of the commands I should be able to put together the batch files on the fly. That's pretty much what we want to do with the exception of the database name since that's set in the create/populate script as well as the modification scripts I have already working fine.

Thanks in advance, this is something that I need to get functional by next week so the pressure is on my end ;)

Mike