Use Batch File to Restore SQL Server Database from Backup

Benefits of using batch file to restore SQL Server database

A batch file is an executable file consisting of a series of commands that can invoke other programs. Usually you can use text editor like Windows Notepad to create and modify it. In the practical application of SQL database recovery, for example, you will need to enter restore script in a text file, and save it with .bat format.

With such a batch file, you can simply execute it by double-clicking to restore database without logging into SSMS. And since the script in the file is entered by you, the specific recovery settings is also customizable. For users who need to recover the same database frequently, or perform some more advanced operations, this eliminates the hassles of setting it up each time.

How to create a batch file for SQL Server recovery

1. First, right-click your desktop and choose New > Text Document to create a text file, and open it.

2. In the text editor window, enter the restore command of SQL database:

SqlCmd -E -S Sever\Instance -Q "RESTORE DATABASE DatabaseName FROM DISK ='FilePath\Name.bak'"

This is the basic command to restore SQL database from bak file, you just need to fill in your own information. Here’s my example:

SqlCmd -E -S .\MSSQLSERVER_01 -Q “RESTORE DATABASE databasename FROM DISK='D:\Backup\example.bak'”

If you want to restore database from a differential backup, please restore the full backup WITH NORECOVERY first, then restore the differential backup WITH RECOVERY. The commands will be like:

SqlCmd -E -S Sever\Instance -Q “RESTORE DATABASE DatabaseName FROM DISK='Path\Fullbackup.bak ' WITH NORECOVERY”
SqlCmd -E -S Sever\Instance -Q “RESTORE DATABASE DatabaseName FROM DISK='Path\Diffbackup.bak' WITH RECOVERY”

3. After completing the script, click File > Save As to give the text file a name, and change its extension into .bat. Thus it will become an executable file. You can double-click the file to execute the script in it, or even automate it with Windows Task Scheduler.

Note:

The "SQLCMD" required in the script is shipped with SQL Server 2014 and lower versions. In higher versions, you may need to download it from this page.

What if the batch file fails to restore database?

If you are worried that the batch file will fail, you can add "PAUSE" at the end of the script, so that the CMD window will suspend and you will see the failure message returned.

For example, a common error is "The tail of the log for the database has not been backed up", and you can follow the instructions to BACKUP LOG WITH NORECOVERY in advance, or restore the database WITH REPLACE or WITH STOPAT.

How to restore multiple databases for backup files at once?

If you have only a few specific databases to restore, then just repeat the above commands and fill in the database names & corresponding backup files separately. However, if you need to restore a large number of databases and want to simplify the operation to the maximum extent, you can consider using another way of SQL Server backup and recovery.

Easy way to restore multiple SQL databases at once

If you have a large number of databases to backup & restore and don't want to go through the complicated setup every time, compared with batch file, using the professional centralized data management tool AOMEI Centralized Backupper Database would be a much easier solution.

It allows you to select multiple databases or even multiple instances of SQL Server to backup at once, and restore them directly from the backup task with a few clicks. Everything is very easy and intuitive to operate.

Also, as an enterprise-level backup software, it supports not only your current computer, but all the desktops, laptops, virtual machines, workstations and servers within LAN. You can install it as a central console and easily backup all computers on network.

Download the 30-day free trial to try it out:

After installation, Centralized Backupper Database will auto detect all the available machines in the LAN. You can then Install client program (remotely or manually) on computers you want to manage, and Request Control over them.

How to backup SQL databases

1. Go to Home tab and choose SQL Server Backup to get started.

2. The setup is divided into three steps. In Step 1, click + Add Computers to detect all the controlled computers with SQL instance. Then you can select a computer from the result window.

3. In Step 2, click Add to select the specific source data. You could select multiple databases or even instances on the computer.

4. Click on Step 3, and hit Add Storage on the popping out window to specify the path of a network share or NAS. Then you can click Start Backup to create and execute the task.

Tip: You can click Settings to manage the backup encryption, compression, notification, etc. or click Schedule to set up the frequency (Once/Daily/Weekly/Monthly) to auto execute the task. You can also choose to perform full backup or differential backup.

How to restore databases from the backup

1. The created backup task will be added to the Tasks tab. When you need to restore SQL databases, just click Advanced on its top-right corner and choose Restore.

2. Select the source computer and source data respectively. You could simply restore all the databases included in the backup file, or select only a few of them to restore.

If you’ve executed the backup task for several times, you can also select which backup to restore from, full or differential.

3. At last, choose to Restore to original location or Restore to a new location. With the second option, you can even restore database to another SQL Server of another client computer. Click Start Restore to execute the operation.

Conclusion

There are many ways to restore a SQL Server database. In terms of flexibility and the ease of execution, you can use batch file to restore SQL Server database. But the complexity of scripts can easily make a restoration fail due to a small syntax or spelling error.

Fortunately, AOMEI Centralized Backupper Database Edition provides an intuitive GUI to help you backup and restore multiple databases, automatically perform tasks, restore database to another computer, etc. without using complex scripts. And as a comprehensive client-server backup software, it also supports files, partitions, disks, operating systems on Windows computers.