In that blog post let’s talk about one of the crucial questions in the life of every DBA it is SQL Server database backup and restores process, which you can cover using backup and restore utility SQLBackupAndFTP. You can install this tool on your machine or personal computer and it will do all work for you. So here is a short tutorial how you can protect your SQL Server database using backup and restore utility.

Backup and Restore with SQLBackupAndFTP

Backup with SQLBackupAndFTP

Using SQLBackupAndFTP you can simply tune a backup job, which will make all SQL Server database backups automatically. Here is short instruction on how to do it:

1. Create a backup job
   
2. Connect to your Server, it can be Local SQL Server, Azure SQL Database, or Remote SQL Server, and click “Test” to check the connection. After the connection is established, save the settings by clicking “Save & Close”
   
3. Select databases which you require to backup from the list
   
4. Specify a database backup destination
   
5. Tune a backup schedule
   
   The backup job is ready. Now all your databases will be backed up according to the selected schedule.

Restore with SQLBackupAndFTP

With SQLBackupAndFTP you can restore your backups that were created via SQLBackupAndFTP, “CREATE BACKUP” command, SQL script or a .bacpac file.

The easiest way to restore backups that were created with SQLBackupAndFTP is to click on “Restore form Backup…”

If you need to restore any other backups, you have to create a restore job, you can do it with the help of the following steps:

1. Create a restore job
   
2. Choose a location from which backups should be restored
   
   If you need to recover from a specific file on a local computer use “File” option.
3. Choose a server to which you are going to restore your backups
   
4. Choose any recovery point from the ones found in the selected storage
   
5. Restore by clicking “Run Now” button
   

 

Backup and restore using T-SQL Commands

Of course, to keep your database safe and sound you should use backup and restore utility, it’s the easiest way to make SQL Server database backup and restore backups if the failure occurs. We have talked about how to use backup and restore utility and now we will show how to backup and restore SQL Server database using T-SQL Commands.

Backup and Restore Full Backup

Use the next T-SQL Command to create a full backup of your database.

BACKUP DATABASE Adventureworks TO DISK = 'D:\\Adventureworks_full.bak'

Apply the following command to restore the full backup of your SQL Server database. This backup will overwrite your database if such is exist or create a new SQL Server database.

RESTORE DATABASE Adventureworks FROM DISK = 'D:\\Adventureworks_full.bak'

Once you have restored your full database backup you can easily restore a differential or transaction log backups. If you want to do it, restore your full backup using NORECOVERY option. This option leaves a backup on restoring state and lets you restore extra differential or transaction log backups.

RESTORE DATABASE Adventureworks FROM DISK = 'D:\\Adventureworks_full.bak'

Backup and Restore Differential Backup

The T-SQL Command to create a differential backup almost the same as to create a full backup, all you need is to add ‘WITH DIFFERENTIAL’

BACKUP DATABASE Adventureworks TO DISK = 'D:\\Adventureworks_diff.dif' WITH DIFFERENTIAL

The T-SQL Command to restore the differential backup is the same as the Command to restore a full backup. All you need to do is don’t skip to add NORECOVERY command.

RESTORE DATABASE Adventureworks FROM DISK = 'D:\\Adventureworks_full.bak' WITH NORECOVERY
GO
RESTORE DATABASE Adventureworks FROM DISK = 'D:\\AdventureWorks_diff.dif'
GO

Backup and Restore Transaction Log Backup

Here is the T-SQL Command to backup Transaction Log backup

BACKUP LOG Adventureworks TO DISK = 'D:\\Adventureworks_log1.trn'

If you are going to restore a transaction log backup, keep in mind, that your SQL Server database must certainly be in the restoring state. Here is the simple T-SQL Command to restore SQL Server transaction log backup.

RESTORE LOG Adventureworks FROM DISK = 'D:\\Adventureworks_log.trn'

Restore multiple transaction log files using NORECOVERY option

Since we covered before you need to add NORECOVERY option to set the database in a restoring state. Below, you can discover the sample how to restore your SQL Server database using 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

That’s all. As you can see the easiest way to make scheduled backups and then if the failure occurs is use the backup and restore utility such as SQLBackupAndFTP – backup your databases automatically  and One-Click Restore to restore your SQL Server databases.