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.
Index
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
Enter a suitable name for the Maintenance Plan.
Click Change to edit the schedule
Select the Back Up Database (Full) check box
Click Next
Click Next
Restores
We can restore a database in MS SQL Server using management studio. To restore a database please follow the below-mentioned steps.
Set Source as Device and locate the required backup file.
Click OK
Click OK
Select Options
Check the Overwrite the existing database (WITH REPLACE)
Set Recovery state to RESTORE WITH RECOVERY
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]
RESTORE DATABASE [SBXTAXTEST] FROM DISK = N'K:\Manual_Backups\SBXTAXTEST_backup-030118-0608.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5
GO
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.
To check status of current active backup, please use below query –
SELECT command,
s.text,
start_time,
percent_complete,
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
WHERE r.command in (‘RESTORE DATABASE’, ‘BACKUP DATABASE’, ‘RESTORE LOG’, ‘BACKUP LOG’);