Backup and Restore Utility SQLBackupAndFTP & One-Click Restore

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 & One-Click Restore. You can install these tools on your machine or personal computer and they 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 Utility

From the very beginning let’s take a look at the backup utility – SQLBackupAndFTP.

Backup utility – SQLBackupAndFTP

Using this tool you can simply tune a backup job, which will make all SQL Server database backups automatically. Here is short instruction how to create a backup job.

  1. Download, install and connect SQLBackupAndFTP to your SQL Server instance. To do it press “Connect to SQL Server / Azure”, specify your SQL Server credentials and click “Test” to check the connections. Press “Save & Close” to save the settings. Backup and Restore Utility
  2. Select databases which you require to backup from the list.Backup and Restore Utility
  3. Specify a database backup destination by clicking “Add backup destination” link.Backup and Restore Utility
  4. Tune a backup schedule. To do it tick off Schedule this job” and press “Advanced backup schedule”. Make all necessary settings and click “Save & Close”.Backup and Restore Utility
  5. The backup job is ready. All you need to do is to press”Save” to run the job according to the schedule or click “Run Now” to make a backup immediately.
Restore utility – One-Click Restore

If you want promptly restore SQL database from backup (full backup), then the best method is to apply easy instrument One-Click Restore. All you need to do is to choose a .bak or .zip backup file and choose a database where you want to restore it.Backup and Restore Utility

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:

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.

[Total: 1    Average: 5/5]

Leave a Reply

Your email address will not be published.