In that post, let’s talk about one of the primary sections in the daily life for each DBA this is SQL Server backup and restore tutorial. The greatest way to backup and restore your SQL Server database is to apply SqlBak. Using SqlBak, you can be sure that your SQL Server database is safe. Below you can find simple SQL Server backup and restore tutorial.
SQL Server Backup and Restore Tutorial
Backup SQL Server
So let’s start and create SQL Server database backup using SqlBak. Go to your Dashboard page and click “Add new job”.
The next step you need to do is to select SQL Server databases you want to backup from the list.
Press “Add destination…” in the “Store backups in destinations” menu then select a place where you prefer to store your SQL Server database backups.
In the “Add destination” window, select a place where you prefer to store your backups.
That is all; also, you can create a backup schedule that will make SQL Server database backup automatically. To do it, press click “Schedule backups” and create a backup schedule.
SQL Server Backup and Restore Tutorial
Here we are going to discuss a few ways how to restore SQL Server database backup. Let’s start from the easiest way – it’s SqlBak.
Restore SQL Server using SqlBak
Now we are ready to restore our SQL Server database backup. Go to your Dashboard page and choose your backup job. At the opened page, select a backup you need to restore from the “Backup history & restore” section by clicking the “Restore” button.
Now you get into the “Restore Backup” window, where you need to select backups you need to restore
To restore SQL Server database backup on your server, simply click “Restore”. If you need to restore SQL Server backup on another server, choose the needed one from the “Restore to” failed, make all necessary settings and then click “Restore”.
After the restore process is completed, you will receive the following message.
Restore SQL Server using T-SQL Command
With the help of T-SQL Commands, you can also backup and restore SQL Server. Here is the simple SQL Server backup and restore tutorial.
Restore a Full Backup
Use the next T-SQL Command to restore the full backup of your SQL Server database. This particular backup will overwrite your database if such is exist or produce a new SQL Server database.
RESTORE DATABASE Adventureworks FROM DISK = ‘D:\Adventureworks_full.bak’
After you have restored your full database backup, you can restore a differential or transaction log backups. If you need to do it, restore your full backup using the NORECOVERY option. Such an option leaves a backup on the restoring state and allows you to restore further differential or transaction log backups.
RESTORE DATABASE Adventureworks FROM DISK = ‘D:\Adventureworks_full.bak’
Restore a differential backup
The T-SQL command to restore the differential backup is similar to the Command to restore a full backup. All you need to do is don’t miss to add the NORECOVERY option.
RESTORE DATABASE Adventureworks FROM DISK = ‘D:\Adventureworks_full.bak’ WITH NORECOVERY
GO
RESTORE DATABASE Adventureworks FROM DISK = ‘D:\AdventureWorks_diff.dif’
GO
Restore a transaction log backup
If you have to restore a transaction log backup, kindly bear in mind that your SQL Server database should be in the restoring state. This means that it is required to restore a full backup and a differential backup, if needed, earlier.
RESTORE LOG Adventureworks FROM DISK = ‘D:\Adventureworks_log.trn’
Restore multiple transaction log files using NORECOVERY option
As we covered right before, you require to include NORECOVERY command to keep the database in a restoring state. Below, you could find the sample how to restore your SQL Server database with the help of the following restore scenarios:
- Full backup
- Differential backup
- Transaction log backup 1
- Transaction Log backup 2
RESTORE DATABASE Adventureworks FROM DISK = ‘D:\Adventureworks_full.bak’ WITH NORECOVERY
GO
RESTORE DATABASE Adventureworks FROM DISK = ‘D:\Adventureworks_diff.dif’ WITH NORECOVERY
GO
RESTORE LOG Adventureworks FROM DISK = ‘D:\Adventureworks_log1.trn’ WITH NORECOVERY
GO
RESTORE LOG Adventureworks FROM DISK = ‘D:\Adventureworks_log2.trn’ WITH RECOVERY
GO
Restore SQL Server using SQL Server Management Studio (SSMS)
Beneath, we are going to talk about how to restore the SQL database from backup using SSMS (SQL Server Management Studio).
- Sign on to your SQL Server and right-click on the “Database” folder and select “Restore Database”
- Press the button under the “Source” part alongside “Device”
- In the “Select backup device” click “Add”
- Choose the backup file or files (.bak) you want to restore
- In the “Restore Database” window specify the database’s name you are going to restore and click “Ok” to begin
That’s it. Your SQL Server database is restored!