On ESDM-SQL1 all databases that are added should have their backup strategy set to SIMPLE (as opposed to FULL).

This means that we can recover to last nights backup in the event of disaster – but we don’t attempt to roll back todays transactions (as you would have to with a bank).  Our hosting contracts should reflect this – probably by just saying that we use “SQL Simple backup recovery model”.  If anyone has any questions on this please ask.

If we do NOT set for simple mode then log files can get extremely large (many GBs) and we do not backup log files

You can check / change the properties of a database using the database properties form:

If the recovery model is set to Full – you can change it to simple without affecting users of the database.

If it has been running in FULL mode for a while – check the size of the log file in: D:\SQLSERVER\Log, if this is large it needs to be shrunk (one off task once db has been changed to SIMPLE backup)

Run – e.g.:

DBCC SHRINKDATABASE ('CAMS-Kent-20130413', TRUNCATEONLY)

Again this can normally be run with care while others are using the database – it may take 20 seconds or so if the log file is several GB.  If you then check the log file you should see its tiny.

This command lists all the databases that are in FULL recover mode (and so should be changed)

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model]
FROM sys.databases Where [recovery_model_desc] = 'FULL'