Automatically generate SQL Server recovery scripts from backup files in one directory

Problem

One of the immediate challenges for database administrators is to back up and restore databases. Backups are done in the automatic scheduling table, but the recovery can take many different versions, you may need to restore a product database, recover a development package, or test the database, or create a copy of the database in another place. There are ways to automate the recovery process and create scripts, but this method shows a way to read only the contents of the directory that exists in the backup file.

Expert answers

Here's a simple way to read the contents of a directory and create a recovery command that needs to be performed to recover the database. This script can be used for all backups, differential backups, and transaction log backups.

Before we begin, the following scripting scenario assumes the following:

1. The restored database has the same name as the backup database.

2. The restored database and backup database are stored in the same location.

3, the file name has the following format

Dbname_yyyymmddhhmm.xxx

4. The file name extension is as follows

Full backup –bak

Differential backup –dif

Transaction log Backups –trn

5, xp_cmdshell is available

6. There is no missing transaction log that could destroy the recovery chain

So, let's follow these steps to create our own backups:

Make full backups at midnight

Start a differential backup every three hours 3:15

Starting at 1 in the morning. Log backups every half hour

At nine o'clock in the morning, we may get the following backup file created on September 10, 2008, which complies with the above rules and is named customer.

Customer_200809100000.bak

Customer_200809100100.trn

Customer_200809100130.trn

Customer_200809100200.trn

Customer_200809100230.trn

Customer_200809100300.trn

Customer_200809100315.dif