I would avoid doing hourly differential backups as each differential contains all of the changes since the previous full backup. Transaction log backups remain small as they only contain the changes since the last transaction log backup was taken.
Now that I have confirmed the process works successfully, I was going to pop my documentation onto the edugeek wiki, but editing has been turned off.
@vikpaw Yes, all I had to do was run the sims.db_p_install script
I do daily FULL backups. It's too much space to do more than that. Haven't tackled the logs, though used to. Mostly the requirement is restoring for testing or pulling back / checking on some old data. If it truly was a disaster, one day's loss wouldn't be that bad in the grand scheme of things.
If anyone is doing differential or incremental, be careful that you only have one backup system (i.e. backupExec, or NT backup, or manual, or SQL studio) talking to the database else you will corrupt the backup regime. Two different systems which is what i had at one stage, each going in and doing their own thing, means only the full backups will work. I learnt the hard way, when i was doing my own system and the NM had BackupExec with an SQL agent going in and doing something else.
Good point, Vik.
Originally Posted by vikpaw
Backup operations in tandem, whether it's an SQL script or a third party agent like Veritas will affect the log file, and these competing backup operations are certain to ruin the backup chain.
Sometimes however, you will need to make an ad-hoc backup, perhaps to test something. In this case it's possible to make a backup with the COPY_ONLY flag set either from an SQL statement or through the manager console. In either case, this 'COPY_ONLY' backup has absolutely no effect on the log files, which is very, very useful if you need to make a one-off backup without ruining your backup set.
Copy Only Backup for SQL Server 2005 and SQL Server 2008
Copy-Only Backups (SQL Server)
We had a four hour loss a few months back and the hassle was immense, so down to an hour now, could try 15mins as per @jinnantonnixx, but that will have to wait for another day.