How to Restore Microsoft SQL Databases? (5 Methods)

How to restore MSSQL database

Although the computer technology has been developed to a fairly high level, hardware failures, system errors, misoperation, etc. still occur occasionally and cause abnormal interruptions in running transactions. It may affect the correctness of data in SQL Server, and even lead to database damage or total loss.

When the unexpected happens, you'll need to restore MSSQL database, which requires you to make a backup in advance. If you haven’t done it, please refer to SQL Server Management Studio backup.

To restore one or two SQL databases, you can simply use Method 1. For all databases of an instance, Method 2 can help. But if you want to restore multiple databases without using commands, Method 5 will be an easier solution.

Method 1. Restore SQL database with SSMS GUI

SSMS provides a restore method with GUI. It allows you to restore the MSSQL database with a relatively simple operation, but only one database can be restored at a time.

*In SQL Server, you can only restore database from the bak file. So make sure you've created backup files beforehand.

1. Launch SSMS and connect to your instance. Right-click Databases and choose Restore Database.

2. Here are 2 approaches for restoration, i.e. select source as Database or Device. If your backup file isn’t stored in the default directory, then please choose Device.

3. Add the bak file you want to restore. Then click OK to perform the backup.

Tips:

☛ If you are trying to restore SQL database to another server but receive error 3154 "the backup set holds a backup of a database other than the existing database", this is because you created an empty database with the same name on the target instance, which is a redundant step. In fact, you just need to add the backup file as normal, and a new database will be auto created accordingly.

☛ If the restoration failed because “The operating system returned the error '5(Access is denied)'…”, please switch to the Files tab, check Relocate all files to folder, and try it again.

Method 2. Restore MSSQL database using T-SQL statement

T-SQL script may not be the easiest way to started with, but once you know the basic commands, you can execute some operations more conveniently and flexibly.

The basic command to restore SQL database from a full backup is:

RESTORE DATABASE database_name FROM DISK = 'path\file.bak'

For example, I want to restore a MSSQL database named “example”, the command could be:

RESTORE DATABASE example FROM DISK = 'D:\backup\example.bak'

Note: If you receive an error “The tail of the log for the database has not been backed up” , you can either backup the log (if it contains work you don’t want to lose) with BACKUP LOG WITH NORECOVERY, or use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

To restore from a differential backup, you should restore the last full backup WITH NORECOVERY beforehand, and then restore the differential backup WITH RECOVERY to finish. Otherwise you may get your SQL database stuck in restoring.

The command will be:

RESTORE DATABASE database_name FROM DISK = 'path\file_full.bak' WITH NORECOVERY
GO
RESTORE DATABASE database_name FROM DISK = 'path\file_diff.bak' WITH RECOVERY
GO

If you want to restore not only one or two databases, but all databases of an instance from bak files, you can first execute this script to enable xp_cmdshell:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

And then execute the following script to restore all databases:

DECLARE @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)

INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B filepath\*.bak'
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell

OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cmd NVARCHAR(MAX) = 'RESTORE DATABASE [' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '] FROM DISK = N''filpath' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.bak'' WITH FILE = 1, NOUNLOAD, STATS = 10'
EXEC(@cmd)

FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END

Method 3. Restore SQL databases with command line

You can actually restore SQL database without logging into SSMS, which requires SQLCMD. It is shipped with SQL Server in 2014 and lower versions. If you are running higher versions, you may need to download it separately.

With SQLCMD, you can restore MSSQL with Command Prompt. Search for “CMD” and run it as administrator. Then use the following command to restore SQL database:

SqlCmd -E -S server_name -Q “RESTORE DATABASE database_name FROM DISK='path\file.bak'”

Likewise, if you encounter the “tail of the log for the database has not been backed up” error, try backing up the log via BACKUP LOG WITH NORECOVERY beforehand, or using the WITH REPLACE or WITH STOPAT clause of RESTORE statement to overwrite the contents of the log.

If you want to restore a differential backup, you need to restore the last full backup WITH NORECOVERY in advance, and then restore the differential backup WITH RECOVERY:

SqlCmd -E -S server_name -Q “RESTORE DATABASE database_name FROM DISK='path\file_full.bak' WITH NORECOVERY”
SqlCmd -E -S server_name -Q “RESTORE DATABASE database_name FROM DISK='path\file_diff.bak' WITH RECOVERY”

For more details, you could refer to backup and restore SQL database with command line.

Method 4. Restore SQL Server using PowerShell

Similarly, you can use PowerShell to make backup, and restore SQL database from bak file without logging into SSMS.

1. Search for "powershell" in the Windows, select Windows PowerShell ISE and run it as administrator.

2. Type the command “Set-ExecutionPolicy Unrestricted” and press Enter to execute it. Click Yes on the pop-up prompt to change the execution policy.

3. Now, enter “Import-Module SQLPS –DisableNameChecking” to load the SQL module. Then use the following command for database restoration:

Restore-SqlDatabase -ServerInstance server_name -Database database_name `
-BackupFile path\file.bak -ReplaceDatabase

Here’s my example:

Restore-SqlDatabase -ServerInstance DESKTOP-AC51C0M -Database mydb `
-BackupFile D:backup\mydb_20210719_140639.bak -ReplaceDatabase

Tip: If you receive “The term 'Restore-SqlDatabase' is not recognized” error, please search for “restore” in the right-side module window, and select Restore-SqlDatabase module to Insert.

Method 5. Backup and restore one or multiple SQL databases intuitively

While you have all these options for MSSQL database recovery, they are either error-prone or difficult to restore multiple databases at once. Therefore, I'd like to recommend a third-party SQL Server backup software for easier management.

AOMEI Cyber Backup allows for centralized SQL Server backup and restore, which means you can backup as many databases as you like on any network connected computer, and restore SQL database from backup intuitively.

There's no T-SQL command involved and all the operations can be done via simple clicks. Download the 30-day free trial to have a try:

💠Set up the MSSQL database backup and restore software:

1. Run the downloaded .exe file to install AOMEI Cyber Backup on the computer you want to use as central console, and launch its main interface. Click Source Device -> Microsoft SQL -> Add Microsoft SQL.

2. Choose Download proxy program or Copy link to download the Agent program and install it on the device with SQL Server installed. Then, switch to Already installed proxy to select the device.

3. Next, click  -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

💠How to backup multiple databases in SQL Server:

1. Click Backup Task -> Create New Task, and choose the Backup Type as Microsoft SQL Backup.

2. Click on Device Name to specify the SQL instance and databases for backup. You can select one or multiple databases as you need.

3. Select a Target storage to save your SQL backups. You can speicfy a local path or network path.

4. Configure the Schedule to execute the SQL database backup daily, weekly or monthly, and choose the backup method as Full, Incremental or Differential Backup.

Click Start Backup to create and execute the task. The created backup tasks will be listed under Backup Task tab.

✍More useful features:

• Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

💠How to restore MSSQL databases from backup:

1. Click Backup Task on the left menu bar, locate the task you want to restore, and click  -> Restore.

2. Select a backup version that you want to restore MS SQL databases from.

3. Next, select the target location that you want to restore to. There are 2 options:

• Restore to original location (by default): in-place recovery that can revert MSSQL databases to an older verison.
• Restore to new location: select another target to perform the out-of-space recovery. You can specify the name of the new database and modify the storage location.

4. After all the settings, click Start Restore to begin the restore progress, and wait patiently for it to complete. You can click on the  button to see restore details or cancel it.

Conclusion

When you encounter a problem with MSSQL database and want to recover it from a backup, you can refer to the 5 methods provided in this article - the 5th one, AOMEI Cyber Backup, is the most convenient and intuitive.

Besides MSSQL database, you can also use it for other important enterprise data, such as virtual machine backup.