This tutorial is a great guide for newbies in SQL Server who never backup the database and for people with some experience, because you will learn some tips that you may not know.
As a DBA, developer or someone in charge of a database, you should know how to back up a database.
This tutorial is a great guide for newbies in SQL Server who never backup the database and for people with some experience, because you will learn some tips that you may not know. You will learn how to backup a database using different options available in SQL Server and how to restore it in case of disasters or emergencies.
You can backup your database using SQL Server Management Studio (SSMS), run jobs to backup automatically or more.
We will teach you how to backup a database and how to restore it using different methods.
The first method that we will use to back up a database is using the SSMS. To back up a database, the simplest method is the following to select the backup option:
Using a simple OK, you will have your backup ready in the path specified:
Optionally you can generate the script by selecting the script option:
This option is useful if you want to automate the backup. The script option allows to generate the T-SQL backup commands automatically:
BACKUP DATABASE [testdb] TODISK = N'C:\sql\test.bak' WITH NOFORMAT, NOINIT,NAME = N'testdb-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,STATS = 10
There are several options that are useful when you backup a database.
First, we can talk about the recovery model. There are three types of recovery models:
- The simple method stores less information in the log. It is useful to reduce the information in the database log.
- Bulk-logged stores all the information of the transactions in the transaction logs except the ones related to bulk-logged commands. For example, if I insert bulk of data, the log may increase a lot. I can change the recovery model from full to bulk logged and that way my database log will not grow aggressively.
- Full recovery model stores all the information in the transaction log.
To change the recovery model, you can use the SSMS and in the database properties select options and change the recovery model:
Another option is to change the recovery model using T-SQL:
ALTER DATABASE [testdb] SET RECOVERY BULK_LOGGED WITH NO_WAIT
When the recovery model is set to simple, you cannot create backups of the transaction logs. The recovery model is visible when you backup a database:
Another useful option is the Copy-only backup. This option allows creating a backup that is independent. You can have a backup set, when you create a copy-only backup it is not included in a set of backups.
There are 3 main types of backups:
- Full backup is a backup of the complete database.
- Differential backup is a backup of the changes since the last full backup. It takes less space than a full backup because it does not store all the data.
- Transaction log backups the Transaction log.
Another interesting option is the Files and filegroups. If your database is big, you can divide your data into multiple files or filegroups and backup only certain files or filegroups:
To create files and filegroups, you can use the files and filegroups page in the database properties:
Backing up individual filegroups or files instead of the entire database can reduce the time spent during the backup.
To schedule a backup, you can create a new job in the SQL Agent:
In the job step, add the t-sql command to create the backup:
In the schedules page, you can create a new schedule. The following example shows how to run a backup daily at 10:00 PM:
There are other interesting options to backup a database. One of the options is to append to the existing backup set. This is the option by default. The other option is to overwrite all existing backups sets. This second option is used if you are only interested in the last backup version.
It is a good practice to backup and store your backup in a different place. You could save it in a Server stored in a different server and better if this server is stored in a different location.
SQL Server allows to store for example the backups in Azure using the Backup to URL option:
Restoring a database
If your database is corrupted, or if some information is stored by mistake, it is possible to restore your database using the backups available.
The easiest option is to use the restore option in SSMS:
It is possible to restore the database or just files and Filegroups. There is an option to restore a page. That option is useful when a page is corrupted in the database. You can use the DBCC CHECK DATABASE command to verify if your pages are damaged.
The following command shows how to restore a backup using T-SQL transactions:
BACKUP LOG [testdb] TODISK = N'C:\Backup\testdb_LogBackup_2018-05-06_22-17-43.bak' WITH NOFORMAT, NOINIT,NAME = N'testdb_LogBackup_2018-05-06_22-17-43', NOSKIP, NOREWIND, NOUNLOAD,NORECOVERY ,STATS = 5
RESTORE DATABASE [testdb] FROMDISK = N'C:\Backup\testdb.bak' WITHFILE = 8,NOUNLOAD,STATS = 5
First, the last log backup is back up and then you restore from the backup device.
The following example shows how to restore using a specified time:
Stellar SQL Database Toolkit
Stellar SQL Database Toolkit includes great tools to recover corrupt backups, databases, and passwords. You can download the software from the product page.
If your backup is corrupt for some reason, you can repair it using the SQL Backup Recovery included in the Stellar SQL Database Toolkit. You only need to select your backup file and press repair and the software will repair it for you.
In this article, we have shown how to create backups and we also learned about recovery models and types of backups. We also learned how to schedule a backup. It is possible to create backups with SQL Server Integration Services, PowerShell, cmd and using programming languages, but I think SSMS and T-SQL are the most popular ways to do it.
Finally, we learned how to restore a database using the backups provided. If you have comments or questions about this topic, do not hesitate to write us.