Thursday, February 17, 2005

Full vs Simple when it comes to SQL

So you have SBS Premium (or you spent too much on SQL) and you're having issues backing up.

Well, you can do one of two things. You can install the hotfix for NTBackup (It's a free support call). However, while this will make your backups successful, the copy of SQL in your back-up will be corrupted and not available for restore.

The second option is to change your SQL databases to simple, by using the SQL Manager. You simply find the databases (Microsoft SQL Servers --> SQL Server Group --> SERVERNAME --> Databases. Right click and choose the properties on each database. On the Options tab, change the Recovery Mode from "Full" to "Simple". This essentially turns off up-to-the-minute restore (ie no log files). NTBackup will successfully backup your SQL databases, and will be able to restore it up to the time of the backup (which is pretty consistent with the rest of the SBS server). Unfortunately though, you will not be able to recover up to the minute of the failure. This is by far the easiest to configure.

The third and final option is to move your SQL databases to a volume that is not included in the backup. It has to be a seperate volume all together (not just a removed folder) since NTBackup envolks VSS writers based on what applications are installed on that volume. You can then use the SQL backup tools in the SQL enterprise manager to backup the SQL databases to a volume that *is* included in the backup, and then backup the backup files from the SQL backup. This of course makes the recovery of SQL somewhat time consuming. :)