BUS MS SQL Server Issues

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

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).