As a SQL Server user, you may find yourself frequently performing backup and restore tasks. These tasks are crucial for data protection and disaster recovery. In this article, we will discuss some useful SQL Server scripts that can simplify and enhance your backup and restore processes.

1. Backup Script

The first script we will discuss is a backup script. This script allows you to easily specify the database name, path, and date for your backup. It also includes options for compression, copy-only, and backup statistics. Here’s an example:

-- BACKUP DATABASE <database_name> TO DISK = '<path>\<filename>.BAK' WITH COMPRESSION, COPY_ONLY, STATS = 10

By uncommenting and customizing this script, you can quickly perform ad-hoc backups with the desired options.

2. Restore Script

The second script is a restore script. Similar to the backup script, it allows you to specify the database name, path, and file names for the restore operation. Additionally, it includes a sample of the MOVE clause, which is useful for ad-hoc restores. Here’s an example:

/*
RESTORE DATABASE <database_name> FROM 
DISK = '<path>\<filename>.BAK'
WITH MOVE '<logical_name>' TO '<new_mdf_file>',
MOVE '<logical_name>_log' TO '<new_ldf_file>',
STATS = 10
*/

By uncommenting and customizing this script, you can easily perform ad-hoc restores with the ability to specify new file locations if needed.

3. Monitoring Script

The next script is a monitoring script that provides information about running backup and restore operations. It displays the session ID, command, percent complete, estimated completion time, elapsed time, and ETA in minutes and hours. Here’s an example:

/*
SELECT r.session_id, r.command, CONVERT(NUMERIC(6,2), r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2), r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000), (SELECT SUBSTRING(text, r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 
ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE')
*/

This script can be useful when you are performing large backups or restores and want to monitor their progress and estimated completion time.

4. Backup History Script

The last script we will discuss is a backup history script. This script retrieves information about completed backups, including the database name, backup type, name, physical device name, backup finish date, logical device name, device type, size in MB, backup time in minutes, and whether it is a copy-only backup. Here’s an example:

USE msdb
GO
SELECT TOP 100 backupset.database_name, backupset.type, 
backupset.name, backupmediafamily.physical_device_name, backupset.backup_finish_date,
backupmediafamily.logical_device_name, backupmediafamily.device_type, 
CAST(backupset.backup_size/1024/1024 as int) AS Size_in_MB,
datediff(minute, backupset.backup_start_date, backupset.backup_finish_date) backuptime_minutes, is_copy_only
FROM backupset
JOIN backupmediafamily
ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE 1=1
   AND backupset.type = 'D' -- Full (D), Differential (I) or Log (L) 
-- AND physical_device_name NOT LIKE 'VNB%' -- Ignore backups going to tape
-- AND backupset.database_name like '' -- Only backups for a specific DB or group of DBs
ORDER BY backupset.backup_finish_date DESC, backupset.database_name DESC

This script allows you to view the backup history, including the size, duration, and destination of each backup. It can be particularly useful when working on a new instance or when you need to estimate the size and duration of a backup.

These scripts have been proven to be useful in various backup and restore scenarios. Feel free to customize and use them according to your specific needs. They can save you time and provide valuable insights into your SQL Server backup and restore operations.

Happy scripting!