SQL Server database shrink – How and when to schedule and perform shrinking of database files

From time to time, DBAs find themselves in a situation where a SQL Server database becomes too large for their production environment, and needs to be shrunk in size in order to free space and allocate it back to the system.

Before shrinking a SQL Server database or database files, it is very important to understand how the process works and what are the immediate consequences of the shrinking process.

January 4, 2016

Why, when and how to rebuild and reorganize SQL Server indexes

The purpose of SQL Server index is pretty much the same as in its distant relative – the book index – it allows you to get to the information quickly, but instead of navigating through the book, it indexes a SQL Server database.

January 4, 2016

How to back up a database without the index data

This article explains how to create filegroups and move indexes into a different file group, and how to backup a database without indexes with the purpose to reduce the amount of data required to perform backups thus reducing backup time and space required. We will also show how to use the ApexSQL Backup solution for filegroups backup as a first part of the series in which we will show how to backup and restore a database without indexes, and to recreate the indexes after a restore.

July 2, 2015

How to automate DBCC CHECKDB after a database restore

Why running DBCC CHECKDB?

DBCC CHECKDB checks the logical and physical integrity of all the objects in a database and provides information of any corruption.

As performing DBCC CHECKDB is a resource exhaustive task it is recommended to run it on a production server when there is as less traffic as possible, or even better, as one of the ways to speed up the DBCC CHECKDB process, is to transfer the work to a different server by automating a process and run CHECKDB after a database restore. As a backup process is a copy of a database and a restored database will be exactly the same as an online database therefore if there were any inconsistencies or issues they will be in the backup, and found in a restore. By using this approach both restores will be tested and backups verified without any impact on a production database.

June 22, 2015

How to perform a SQL backup and restore history cleanup

SQL Server stores a complete history of all SQL backup and restore operations, and other historical activities such as activities like Database Mail, Jobs, Log Shipping, Policies, Maintenance Plans, etc. on a server instance in the msdb database.

June 12, 2015

Manage multiple database backups across different SQL Server instances

One of the most common ways to ensure that the recovery will be possible if a data-file corruption or any other disaster occurs is to create a recovery plans for this scenario. The most popular recovery plans include regular creation of database backups which can later be used to restore a database to a nearest available point in time, prior to disaster.

In order to create and apply successful recovery plan, it is important to create a solid backup schedule and to manage backups of multiple databases across different SQL Server instances.

In this article we will create a SQL Server scheduled backup by using a SQL Server Agent job and ApexSQL Backup.

May 6, 2015

How to schedule a database backup restore in SQL Server

When recovering from a SQL Server database failure, a database administrator needs to restore a set of SQL Server database backups in a logically correct and meaningful restore sequence. With this in mind, to the goal is to devise a disaster recovery strategy by creating a solid backup plan, as well as a proper database restore plan in SQL Server. This article will describe 2 different solutions for creating and scheduling a database restore in SQL Server.

April 8, 2015

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