In that post let’s talk about one of the primary section 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 in 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” and then select “Add backup job”.
The next step you need to do is to select SQL Server databases you want to backup from the list.
Press “Add backup destination” in the “Store backups in selected 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 which will make SQL Server database backup automatically. To do it press click “Schedule backup (full, diff, tran log)” 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 here you can promptly restore the last SQL Server database backup or you can restore the selected one. If you need to restore the last one – click “Restore” button.
If you need to restore another SQL Server database – click on this backup job and choose the backup you need to restore.
Now you get into “Restore” window, here you need to select where you are going to restore SQL Server database backup on your computer or restore to another server or computer.
To restore SQL Server database backup on your computer simply click “Restore”. If you need to restore SQL Server backup on another server or computer press “on another computer” link, make all necessary settings and then click “Restore”.
After the restore process will be 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 NORECOVERY option. Such option leaves a backup on 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 as the Command to restore a full backup. All you need to do is don’t miss to add 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 it 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 how to restore 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!