SQL Server Backup and Restore Tutorial

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”.SQL Server Backup and Restore Tutorial

The next step you need to do is to select SQL Server databases you want to backup from the list.SQL Server Backup and Restore Tutorial

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. SQL Server Backup and Restore Tutorial

In the “Add destination” window select a place where you prefer to store your backups. SQL Server Backup and Restore TutorialThat 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.
SQL Server Backup and Restore Tutorial

If you need to restore another SQL Server database – click on this backup job and choose the backup you need to restore.SQL Server Backup and Restore Tutorial

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. SQL Server Backup and Restore Tutorial

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”.SQL Server Backup and Restore Tutorial

After the restore process will be completed you will receive the following message. SQL Server Backup and Restore Tutorial

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:

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).

  1. Sign on to your SQL Server and right-click on the “Database” folder and select “Restore Database”.
  2. Press the button under the “Source” part alongside “Device”.
  3. In the “Select backup device” click “Add”.
  4. Choose the backup file or files (.bak) you want to restore.
  5. 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!

 

[Total: 1    Average: 5/5]

Leave a Reply

Your email address will not be published. Required fields are marked *