BUS MS SQL Server Issues

These are some of the problems you may encounter when backing up MS SQL Server, and their solutions.

Transaction Log backups considered harmful

Below you will find some discussion of how to deal with certain problems that come up when you use Transaction Log backups. However, our recommendation for most BUS customers is:

Do Not Use Transaction Log Backups

The reason is that Transaction Log (TL) backups are fragile. There are several ways you can set up with TL backups, and think that you have good backups, but you don't. The BUS software will report successful backups each night, but when you try to restore a database from them it might not work.

The most common case is if some other backup program is also doing backups of your database. We consider it a Good Practice to backup up your data locally in addition to backing it up on the BUS. But if you set up "ntbackup" to make dumps of your database every day, it will interfere with the "reference points" of the Transaction Log backups that the BUS is making. Usually in this case, you can successfully restore the most recent full backup of the db, but all the intervening days on which the BUS was only doing TL backups, those TL backup files are useless. That means all changes since the last full backup are lost. (Hopefully just a few days, but if you are only doing 1 full backup per month it could be weeks of lost data).

The solution is to do Full SQL backups each night, using the In-File Delta feature of the BUS to keep the amount of data transfer to a reasonable size. This approach works for the majority of cases. See the next section for details on setting it up in the BUS client.

MS-SQL Server transaction log backups have errors

You get error messages that look like this:

Error 2008/04/10 01:00  [Microsoft][ODBC SQL Server Driver][SQL
Server]BACKUP LOG is not allowed while the trunc. log on chkpt.
option is enabled. Use BACKUP DATABASE or disable the option
using sp_dboption.

Error 2008/04/10 01:00  Path "C:\Backup\MSSQLServer\1205274809764\TORTOLA\(local)\msdb"
does not exist!

The last part of the error message contains the name of the database which failed to back up ("msdb" in the example above). To fix this, you need to turn off the "truncate log on checkpoint" option for that database. Here is how to fix it in 3 versions of SQL server:

SQL Server 7

1. Run the SQL Server Enterprise Manager
2. Drill down to the Databases
3. Select the db in question (msdb)
4. Click "database properties"
5. Select the Options tab
6. Turn off "Truncate log on checkpoint"

Repeat this procedure for all databases which get this error. See Also: http://download.ahsay.com/support/screenshots/SQL7-TuncateLogOnCheckpoint.jpg

SQL Server 2000

SQL 2000 gives this variation on the error message:

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.
Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

To fix it:

1. Run the SQL Server Enterprise Manager
2. Drill down to the Databases
3. Select the db in question (msdb)
4. Right-Click the database name and select "Properties"
5. Click the Options tab
6. In the Recovery Model pulldown menu, select "Full"

Repeat this procedure for all databases which get this error. See Also: http://download.ahsay.com/support/screenshots/SQL2000-FullModel.jpg

MSDE

MSDE is what you get when you install an application which comes with MS SQL Server to use as its data store, but you haven't installed MS SQL Server for general use. MSDE does not include the Enterprise Manager, so you need to issue this command instead to enable Transaction Logs:

osql -E -S SERVERNAME -Q "ALTER DATABASE DBNAME SET RECOVERY FULL"

Replace SERVERNAME with the server's name and DBNAME with the name of the database having the issue (msdb in our example).