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.

There are some cases where this method will not work well, and you will have to fall back to using TL backups:

• If your database is very large and very active. The In-File Delta capability of BUS will keep you from having to upload the full database every night, but it's not nearly as space-efficient as the TL backups. As a general rule, if your database is under 1 GB in size this method should work fine. If it's over 1 GB and is modified every day, it may consume more space on the backup server than you want to pay for.
• If your business employs a Database Administrator, that person should be capable of using the TL backup method, and guaranteeing that it is working working correctly. If, as in most businesses, the DBA duties fall to someone whose primary job is something else, the complexities of TL backups will likely cause problems.

As always, there is no substite for Doing A Test Restore to make sure your backups are functioning.

Setting up MS-SQL Backups without using transaction logs

To set up a new MS SQL backup set: in the BUS client, select Backup Set, New, MS SQL Server Backup Set. Select the server name from the pull-down menu, and enter the SQL Server's admin username and password. The Login ID is usually "sa". Click Next, then check off the list of databases you want to back up on the next screen.

Click Next to edit the backup schedule. The default setup will have 2 schedules: a weekly full dump and a daily transaction log dump. Delete the transaction log dump schedule. Edit the remaining "Complete Backup" schedule: click Properties, change the type from Weekly to Daily, set the time to your preferred backup time.

Restore MS-SQL Database, without transaction logs

When you go to the Restore option in the BUS cilent, you'll see one file for your database, called something like DATABASE_dbname.bak (where the "dbname" part is replaced with the name of your database). If you want to restore the database as it existed on a specific date, click the "show all files" setting. Then you'll see a file listed for each day. Select the date you want, and then hit the "Restore Files" button.

After you have the DATABASE_dbname.bak file, go into the "Enterprise Manager" (or whatever it's called for the version of SQL Server you are using). Drill down to the database you want to restore, right-click the db, select All Tasks, then Restore Database. On the Restore Database screen, select From Device. then click the Selected Devices button. On the next screen click the Add button and browse to the DATABASE_dbname.bak file you just restored from the BUS server. Then click OK a few times to restore the database.

Caveats:

• We do not know how to restore the database under a different name. For example, if you had a mishap today, you might want to restore yesterday's backup and be able to compare records in the restored db and the current (possibly messed-up) db. If you know how to do that, please email help@swcp.com .

If you must use transaction log backups

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