Right Way to Backup and Restore SQL Server Database

In this article, we will show the right way to backup and restore SQL Server Database and how to restore them using SQL Server.

Requirements

The following software will be required to create a backup.

1. First, we will need SQL Server with any version included SQL Server 2019, 2017, SQL Server 2016, SQL Server 2014 and SQL Server 2012.

We will cover SQL Server on premises and focussed on SQL Server for Windows. SQL Server for Linux is a little different and will not be covered here.

Also, Azure SQL contains automatic backups, so you do not need to worry about it and that is why we will not cover in this article.

Getting started

Here are few important points related to backup and restore SQL Server Database:

• Use the SQL Agent to automate backups

A good practice to backup a database is to do it automatically. We will usually automate the backup using the SQL Server Agent.

The SQL Server Agent is available in most of the SQL Server editions (Enterprise, Standard, etc. ) except in the Express edition:

• Try to backup when there are not many users online

The best time to backup is at night when there are not many users online. Backup consumes a lot of resources and it might be slower the performance when people is using it.

• Always test the backup

Once that the backup is completed, you can restore the database with another name to verify that the backup is working fine. It is a best practice that will avoid problems in case of emergencies. There is nothing worse than having a database corruption and then restore from the backup and notice that it never worked.

• If possible, try to automate restoration also

It is possible to automate the backup and restoration using the SQL Server Agent.  You can use one step to backup and on success, you can try a restoration. Use another name of database to restore in order to avoid problems with the database online. In general, use another machine to test the restoration if possible.

• Combine your backups types

Differential backups can save you a lot of space instead of many full backups. Especially, if not many changes were done in the database.

Also, you can backup per files or file groups instead of full backups.

• Always keep your backups in a safe place

Do not ever store your backups in the same hard drive or server than the data files of your current database. Always keep the backups in another server and if possible, make sure that the other server is in another city, office or country. You never know if a natural disaster will occur. If possible try to store the backups in a safe place.

• Compress your backup

If possible, use the compress option to compress your backup.  Not all the SQL Server editions support this feature, but if it supports, use it. It will save a lot of space:

• Try to encrypt your backups

In order to have security you can use Transparent Data Encryption to secure your backups and store your data. This feature will avoid someone to get your data from your database.

 Software Approach

• Use Stellar Repair for MS SQL to repair the database if it is corrupted.

If the database is corrupted and you cannot backup it, you can always use Stellar Repair for MS SQL. This software allows to repair corrupt databases. Once repaired, you can successfully create a backup.

Microsoft Most Valuable Professional (MVP) Review:

Technet-MVP Review Reference

Conclusion

In this article, we learned the best practices to backup and restore SQL databases. We learned how to backup, where to backup, increase the security. If you have more questions, please contact us and we will gladly help you on this topic.