Back-Up and Restore of MS SQL Server Databases

With the help of this article, we will discuss basic MS SQL Server Database backup and restore, how to create a maintenance plan and strategies of restoring in SQL server. 

Back-up Types

We can take mainly four types of backup in SQL server database, namely –

  • Full
  • Differential
  • Transaction Log
  • Copy Only

Configuring Maintenance Plan Backups

The steps below should be used if you need to set up a recurring maintenance plan to create full, differential, and transaction log backups with the required cleanup of old backup files.

First, set up the daily full backup by running the Maintenance Plan Wizard.



Click Next

mssql maintanceplan wizard

Enter a suitable name for the Maintenance Plan.

Click Change to edit the schedule

mssql maintanceplan wizard step1

Set it as required (in this case every day at 12:00:00AM)
Click OK
mssql maintanceplan wizard step2

Select the Back Up Database (Full) check box

Click Next

mssql maintanceplan wizard step3


Click Next

mssql maintanceplan wizard step4

Set all fields as above (Folder: may be different so change that as required).
Click Next
mssql maintanceplan wizard step5
Click Next

mssql maintanceplan wizard step6
Click Next
mssql maintanceplan wizard step7
Click Close


We can restore a database in MS SQL Server using management studio. To restore a database please follow the below-mentioned steps.

Restore step1

Set Source as Device and locate the required backup file.

Restore step2.jpg

Restore step3.jpg

Restore step4

Click OK

Click OK

Select Options

Check the Overwrite the existing database (WITH REPLACE)

Set Recovery state to RESTORE WITH RECOVERY

Restore step5

Click OK

Wait for the restore to complete.

Restore using Command Line

The command line code for the restore operation above would look as follows.

USE [master]

The above article has provided you with enough information about how to configure daily backups and restore a database from the backup. The step-by-step guide is specially provided to help beginners understand the process and start testing the different processes in their environment. Please let us know if you have any suggestions.

How to check state of current active backups?

To check status of current active backup, please use below query –

SELECT command,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ‘ hour(s), ‘
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + ‘min, ‘
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ‘ sec’ as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ‘ hour(s), ‘
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + ‘min, ‘
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ‘ sec’ as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

Leave a Comment

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