SQL Server Database Best Practices

Backing up a SQL Server database is not enough. You need to also ensure that you can restore the database from backup, quickly and efficiently. However, it is commonly seen that backups fail at the time of restoring. According to an online survey, ""58 percent of data backups fail when restoration is attempted".

Identifying the backup and recovery requirements, setting up an automated backup schedule, and backup restoration testing are some of the best database backup practices you need to implement for successful backup and restore process in SQL Server.

This article discusses five SQL Server database backup best practices worth exploring.

1. Identify your backup and recovery requirements

The backup and recovery requirements for each database may vary. For instance, if you have a database that is updated at night, then having a day's backup of the database taken right after the nightly update is all you would need. But if you had a database that is updated the entire day, you can lose a day's transactions if the database fails before the next day backup.

For minimal data loss and downtime when restoring a database, it is crucial to plan your backup and recovery requirements before developing a backup solution for your database.

There are two important types of requirements you must consider:

Recovery Point Objective (RPO), i.e., the amount of data you can afford to lose.
Recovery Time Objective (RTO), i.e., how long the database can be down without causing any significant damage to your business.

For more details, read: RTO and RPO: Useful Considerations for SQL Server Disaster Recovery

Besides the RPO and RTO requirements, your backup plan must cover:

What needs to be backed up: For SQL Server, you must have both system and user databases backed up.
What backup type needs to be used: You must determine whether you should take Full and log backups; Full, Differential and Tail log backups, copy-only backups, or file and group backups.

2. Don't use the same physical location or device as your database files

Avoid storing your database backup on the same physical location or drive as your database files. If the physical drive fails (malfunctions or crashes), you will lose everything. So, ensure that the same physical storage is not used for storing the SQL Server database backups. Also, remember the 3-2-1 backup rule that states:

There shouldn't be only one copy of anything; rather there should be three copies of data;
Two of the backup copies can be stored locally (onsite) but on different types of storage media;
At least one copy should be stored to an offsite location (such as cloud storage).

The 3-2-1 backup strategy is a good rule of thumb for database administrators to keep the data safe in almost any data loss scenario.

3. Set automatic backup schedule

Set an automated schedule to run your backup procedures. Scheduling the backups regularly will ensure they are up-to-date. For scheduling backups, you can use any of these two methods:

You can use SQL Server Agent to create a scheduled backup job to automatically run backups at the determined interval ? daily, weekly, or monthly.
You can use the Maintenance Plans wizard to create, execute, and automate the backup of SQL database. You can find the wizard under Management in SQL Server Management Studio (SSMS).

4. Test your backups

Besides scheduling database backups, you must also test them to ensure their validity and integrity. This is because backups can get corrupt, putting your business data at risk. If a backup test fails, you can fix the problem before losing any data in the event of a disaster.

When it comes to backup testing, here are a few pointers worth noting:

When creating a database, determine the recovery model you need to use. The Full recovery model is useful for performing point-in-time restores. Avoid using this recovery model, if the data within your database changes frequently. When performing transaction log backups containing bulk operations, you can set the database recovery model to the Bulk-logged recovery model. Set the database to a Simple recovery model if its data changes infrequently.
Document your backup testing plan to provide guidance and information necessary to perform data recovery. Microsoft recommends keeping the documentation copy in a runbook. It should contain details of how backups are to be tested, parties responsible for backup testing, and the frequency of testing backups (weekly, monthly, etc.).
Automate your backup testing strategy. It is important as it will reduce the burden on the staff responsible for performing routine backups. Refer to this link for more information.
After restoring the backups, it is recommended to use DBCC CHECKDB to perform database consistency checks to verify that the backup media wasn't damaged.

5. Keep right tools in hand

In unforeseen circumstances, when there is no backup or the backup has turned corrupt, database administrators need to be prepared to deal with backup and restore failure. Ideally, executing the DBCC CHECKDB command with ""REPAIR_ALLOW_DATA_LOSS" option can help fix database corruption. But it can result in data loss or may fail, causing further downtime. You can keep some tools in hand to minimize the time and effort you spend in repairing the database. One such tool is Stellar Repair for MS SQL.

The SQL recovery tool from Stellar® is designed to help SQL database administrators fix severely corrupted databases, without having to write long lines of code. The software has an intuitive graphical user interface that makes the process of repairing SQL database (MDF) files a hassle-free task.

Check out this video to find out more about how the software works.

Conclusion