Verifying SQL database backups automatically

Backups are the starting point for any serious disaster recovery strategy. Creating SQL database backups on a regular basis is just the first step. Equally important is to make sure they are also reliable and restorable. This is the only way to avoid unpleasant surprises in case of a disaster

August 2, 2013

Use database backups as live SQL Server databases to reduce downtime

Being able to revert to the latest available backup immediately is a key requirement that every SQL Server DBA needs to meet. There are so many scenarios in which immediate action needs to be taken – starting from actual disk failure or corruption, system failure all the way to UPDATEs without WHERE clauses, malicious INSERTs or accidental DELETEs, DROPs or TRUNCATE operations. However, usually this is easier said than done

Even if you take meticulous care of your database backups, have the last available database backup verified and at hand and are sure that restoring it won’t overwrite any valid changes made to the database since the accident has occurred – the fact that the system will experience downtime until the backup is fully restored still remains. Luckily, downtime caused by the time needed for a full database backup restore can be minimized

April 12, 2013

Create a database script from a backup without restoring it

There are certain situations when creating a build script from a database is necessary – you might need a specific object (table, view, stored procedure, user, etc.) recreated or rolled back to a state it had earlier. If an online database doesn’t contain this object anymore or contains a newer version of it, the only places to look at are full database backups. If your database is versioned under source control, you can easily find object there but let’s assume you only have SQL backups.

April 5, 2013

Using SQL database backups instead of live databases in a large development team

In scenarios where a development project of a large scale is being completed, DBA skills can really be put to the test. Due to the nature and the dependencies of the software which is being developed, it’s not uncommon for the DBA to be in charge of dozens of environments, used by multiple development teams working on different aspects of the project. In some cases, this scale of development doesn’t only require for multiple SQL Server instances to be set up across environments – more often than not it requires different versions of SQL Server to be accessible across environments as well

April 4, 2013

Use SQL database backups to reduce the performance impact of heavy reporting

Depending on your particular environment, database reporting can have a heavy impact on the database performance, can execute queries which run for dozens of minutes or both. This is usually the case with reports which require complex queries having multiple calls to SQL Server’s aggregate functions to be executed against very large data sets. The effect on the database performance is particularly severe in scenarios where the production database stores data which is changed often – as data modification operations require exclusive locks, in order to preserve the integrity of the database, SQL Server will go ahead with the data modification operation until the query initiated by the report is still running. This increases the chances of a deadlock occurring; especially in cases where another set of data modification instructions, dependent on the ones which are waiting for the reporting to finish has already been applied. Therefore, reporting can cause performance degradation in a production environment. So, how can reporting be optimized to prevent such a heavy load on production databases?

April 4, 2013

How to verify SQL database backups

When disaster strikes, the only thing more frustrating than not having an up to date and relevant database backup is having a corrupt backup. When you first create a backup file, it should be good, with ‘should’ being the operative word. Every time the file is copied to another location, there is a risk of file corruption. A foolproof way to ensure that the file is still usable is to restore the backup itself, and run DBCC CHECKDB immediately against the newly restored SQL database.

April 4, 2013