Microsoft SQL Server database backups – What you need to know
These days most businesses and organisations are running their servers 24/7 and most have the Microsoft SLQ Server database service running continuously resulting in the physical database, data files (*.mdf & *.ldf) are flagged as ‘busy' to many overnight backup routines.
Recently a local business had catastrophic hard disc failure and their server had to be rebuilt with new hard discs.
An assumption had been made that all the data in the database was backed up on the overnight backup. When the backup was restored the database files
were missing.
Subsequent checking of some old backup logs showed that each time the backup was run the database files were excluded as the files were ‘busy'.
SQL Server files need to be backed up separately and there are several ways of doing
this:-
-
Via Enterprise Manager or Management Studio, these are the tools that Microsoft provides for managing SQL Server databases. These tools can create a backup copy of the database on demand or on a time scheduled basis. Additionally the database can be backed up whilst the database is operational. The backed up files can then be picked up by the daily server backup. This is a most common way of backing up SQL server databases and it is normal to schedule the SQL Server backup to run and finish before the daily server backup because that way the latest days data ends up on the daily server backup. Please note if you are using the free ‘Express' edition of Microsoft SQL Server, scheduled backups are not available in the default package as it a function provided in the editions of Microsoft SQL Server that you have to pay for (if this is your situation see our third option).
-
Via a backup system (either via a streamer tape or on-line backup) that is specifically designed to backup open SQL server databases. Some newer database backup systems address this point directly by logging onto the SQL Server databases and backing up the data specifically.
-
Via a bespoke SQL Server database backup program. This can be run automatically overcoming the limitations of the Express versions. It will also raise confirmations & notifications of the success or failure of each backup. Computerisation Limited will provide such a tool free of charge to all existing customers. A reasonable charge for this program will be made to new customers.
Computerisation Limited have been business database specialists and customised software developers since 1989. Computerisation Limited, based in Penarth, near Cardiff South Wales develop business software systems and database applications for clients throughout the UK.
