How to automate the process of restoring a SQL database

It’s very likely that you frequently refresh a development or test environment with recent production SQL server database backups. However, depending on the size and contents of a production database, this process might take a large amount of disk space and be pretty slow since the SQL server database backup needs to be fully restored. This is where ApexSQL Restore comes into play

March 31, 2015

How to restore specific table(s) from a SQL Server database backup

If restoration of an original database to a previous state is not an option, in the case of accidentally updated, dropped, deleted, damaged data, it is still possible to restore these tables if a database backup was created prior to the accident.

February 4, 2015

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

Restore a database to a point in time – part 1

In various scenarios, a SQL Server restore a database to a point in time might be required. Typically, the following situations require this type of recovery:

  • A data corruption occurred by a user malicious action. For example, a user or DBA might delete or update the contents of one or more tables by mistake, drop database objects that are still required during an update to an application, or perform a large batch update process that fails
  • A database upgrading process goes awry or a database upgrade script fails
  • A complete database recovery after a media failure cannot be performed, because the required transaction log and database backups are missing
June 20, 2013

Improve the performance of an ETL process

Due to the sheer volume of data usually involved in an Extract – Transform – Load (ETL) process, performance is positioned very high on the list of requirements which need to be met in order for the process to go as smoothly as possible. Here are some guidelines which will help you speed up your high volume ETL processes

April 23, 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

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