How to setup daily backups with ms sql server agent 2005

Question

Just wondering if anyone can let me know of a good procedure for setting up daily incremental and full backups to any location from a instance of MS SQL 2005.

Bonus points for anyone for can describe how to do it without Server agent (for Express).

Answer 1

1. Plan your recovery strategy. You need to think about how you want to recover the database. How long can you be down? How many backups do you want to have to cycle through to get the restore done. This will determine the right mix of full, differential, and even transaction log backups.
2. Once you've planned your recovery strategy, lay it out on a timeline to make sure it makes sense. This also allows you to check intervals when your going to take your backups.
3. Determine the basic T-SQL commands for each backup step. You should write to local disk (or SAN attached storage that appears as local disk) as this is the fastest way to take the backup and precludes a network issue from causing the backup itself to fail. You're looking at BACKUP DATABASE and potentially BACKUP LOG here.
4. Determine how you're going to get the backups off the system to another storage media. Are you going to copy to a network share? Or are you going to backup straight to tape? How long will these backups be retained in that location? These questions will determine your naming convention and what other mechanisms you might need to perform.
5. Script it out. If you're on an agentless system, you can use Task Scheduler, but you won't get email notification when it fails, meaning someone is going to need to check this regularly. But since we're talking SQL Server 2005, you can use SQLCMD to execute the T-SQL commands to create the backups.
6. If you're just doing a backup to tape from this system, then probably all that's left is to craft the batch file which calls SQLCMD and the T-SQL commands. If you need to copy off the files, you'll need to add the appropriate copy commands. If you need to do something fancier like naming the backup files in a particular manner, you may need to use a bit of scripting like VBScript to generate the proper commands. What I've done in this situation is use VBScript to create the batch file I'll execute next. You can make an ADO connection from VBScript and execute the backup commands that way, but I tend to like the batch file approach because then I see exactly what was executed.

Answer 2

A few more things to add:

• make sure you use the WITH CHECKSUM option on your backups. It tests data file page checksums as it reads them to make sure it's not backing up corruptionn. It also calculates a checksum on the entire backup, which you can use when you....
• periodically restore your backups to make sure that they're valid. At least run RESTORE VERIFYONLY ... WITH CHECKSUM, which will basically do everything except laying down the bits back on disk, including retesting all the checksums. Best bet is to actually restore them on another system.
• practice recovering from a disaster - which is why you're taking the backups in the first place. Ensure that the backups you're taking allow you to recover to your RTO and RPO.

Couple of blog posts with a lot more info to expand on what everyone's said: http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-validating-backups.aspx and http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-having-the-right-backups.aspx

Cheers

Answer 3

K. Brian Kelley did a great job explaining the whole process.

Here is a great script for automating the process of backup a express database. In Part 2 is also a Edition for 2008 and a executable so you have not use sqlcmd.

ExpressMaint can be downloaded at Codeplex

Answer 4

The following free SQL Server Videos will also help give you an idea of concepts, best practices, and hands-on steps you'll need to implement (though they're targeted at non-SQL Express installations):

SQL Server Backups Demystified

SQL Server Backup Options

SQL Server Backup Best Practices

Backups with SQL Server Management Studio

Restoring Databases with SQL Server Management Studio

Also, as Paul mentions, I HEARTILY recommend regularly practicing how to restore/recover your databases. Because learning how to do it after a crash is positively the WORST time to figure it out... and there's no guarantee your backups are even working correctly unless you're REGULARLY testing them out.