BUS MS SQL Server Issues - Revision history

Cheeks: /* Transaction Log backups considered harmful */

2009-07-14T23:32:32Z

Transaction Log backups considered harmful

← Older revision		Revision as of 17:32, 14 July 2009
Line 9:		Line 9:

	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.		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==		==Setting up MS-SQL Backups without using transaction logs==

Cheeks: /* Restore MS-SQL Database, without transaction logs */

2009-07-14T23:25:16Z

Restore MS-SQL Database, without transaction logs

← Older revision		Revision as of 17:25, 14 July 2009
Line 22:		Line 22:
	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.		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 ~~knows~~ how to do that, please email help@swcp.com .		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==		==If you must use transaction log backups==

Cheeks: /* MS-SQL Server transaction log backups have errors */

2009-07-14T23:24:40Z

MS-SQL Server transaction log backups have errors

← Older revision		Revision as of 17:24, 14 July 2009
Line 24:		Line 24:
	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 knows how to do that, please email help@swcp.com .		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 knows how to do that, please email help@swcp.com .

	==MS-SQL Server transaction log backups have errors==		==If you must use transaction log backups==
			===MS-SQL Server transaction log backups have errors===

	You get error messages that look like this:		You get error messages that look like this:
	Error 2008/04/10 01:00 [Microsoft][ODBC SQL Server Driver][SQL		Error 2008/04/10 01:00 [Microsoft][ODBC SQL Server Driver][SQL
Line 35:		Line 37:

	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:		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===		====SQL Server 7====
	# Run the SQL Server Enterprise Manager		# Run the SQL Server Enterprise Manager
	# Drill down to the Databases		# Drill down to the Databases
Line 45:		Line 47:
	See Also: http://download.ahsay.com/support/screenshots/SQL7-TuncateLogOnCheckpoint.jpg		See Also: http://download.ahsay.com/support/screenshots/SQL7-TuncateLogOnCheckpoint.jpg

	===SQL Server 2000===		====SQL Server 2000====
	SQL 2000 gives this variation on the error message:		SQL 2000 gives this variation on the error message:
	The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.		The statement BACKUP LOG is not allowed while the recovery model is SIMPLE.
Line 60:		Line 62:
	See Also: http://download.ahsay.com/support/screenshots/SQL2000-FullModel.jpg		See Also: http://download.ahsay.com/support/screenshots/SQL2000-FullModel.jpg

	===MSDE===		====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:		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"		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).		Replace SERVERNAME with the server's name and DBNAME with the name of the database having the issue (msdb in our example).


	==See Also==		==See Also==
	[[Category: BUS]]		[[Category: BUS]]

Cheeks: /* Setting up MS-SQL Backups without using transaction logs */

2009-07-14T23:23:52Z

Setting up MS-SQL Backups without using transaction logs

← Older revision		Revision as of 17:23, 14 July 2009
Line 12:		Line 12:
	==Setting up MS-SQL Backups without using transaction logs==		==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 knows how to do that, please email help@swcp.com .

	==MS-SQL Server transaction log backups have errors==		==MS-SQL Server transaction log backups have errors==

Cheeks at 22:49, 14 July 2009

2009-07-14T22:49:29Z

← Older revision		Revision as of 16:49, 14 July 2009
Line 1:		Line 1:
	These are some of the problems you may encounter when backing up MS SQL Server, and their solutions.		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.

			==Setting up MS-SQL Backups without using transaction logs==


	==MS-SQL Server transaction log backups have errors==		==MS-SQL Server transaction log backups have errors==

Cheeks: New page: 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...

2008-11-07T19:56:09Z

New page: 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...

New page

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===
# Run the SQL Server Enterprise Manager
# Drill down to the Databases
# Select the db in question (msdb)
# Click "database properties"
# Select the Options tab
# 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:
# Run the SQL Server Enterprise Manager
# Drill down to the Databases
# Select the db in question (msdb)
# Right-Click the database name and select "Properties"
# Click the Options tab
# 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).

==See Also==
[[Category: BUS]]