How to create a simple database recovery solution using SQL Server log shipping

To prevent accidental data loss, it is always good to ensure that there is a disaster recovery solution available. This can be easily achieved by having a standby copy of a primary database on another SQL Server instance, which can be achieved via log shipping.

SQL Server Log shipping is a solution that provides disaster recovery protection at the database level. A log shipping configuration includes one primary server, hosting a live database, and one or more secondary servers that host database copies. The process is fairly simple – a database is backed up and restored from a primary server to the secondary server(s). At regularly scheduled intervals, a transaction log backup and restore is performed at the primary and secondary servers to keep the databases in sync.

April 6, 2015

SQL Server database backup encryption

A database is one of the most important parts of every information system and therefore is an often target of hackers. Encryption is the process of obfuscating data with the use of a key and/or password making the data unintelligible to anyone without a corresponding decryption key or a password.

April 1, 2015

How to schedule a SQL Server backup

Having a good backup and restore plan is an important part of a disaster recovery strategy. This article will describe 3 different solutions/approaches for creating a scheduled backup in SQL Server

As a part of a backup strategy several types of backup can be used together.

March 31, 2015

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

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