By Delia / Last Updated December 20, 2021

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 database with relatively simple operation, but only one database can be restored at a time.

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

restore sql 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.

restore from device

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

restore complete

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 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.

t-sql restore full backup

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

t-sql restore differential backup

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

t-sql restore all databases

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.

cmd restore full backup

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‚ÄĚ

cmd restore differential backup

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 backup and restore SQL database 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

powershell restore database

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.

insert module

Method 5. 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 Centralized Backupper Database 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 them from the 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:

Download Free Trial Win PCs and Servers
Secure Download

Install the software properly, then you can control all the computers with Agent package installed (including your central machine).

request control

Backup databases in MSSQL:

1. Navigate to Tasks > New Task > SQL Server Backup to create a MSSQL backup task.

sql server backup

2. In Step 1, Click + Add Computers to detect the controlled computers with SQL instance, and select the client computer you want to backup in the popping out window.

add computer

3. In Step 2, click + Add to detect instances on the computer. You can then select one or multiple databases accordingly.

add source database

4. Click on Step 3 and hit Add Storage to specify a network share or NAS path. The added storage will be saved for your later use.

add storage

5. Customize the backup task and then you can Start Backup > Create and execute tasks.

Tip: If you want to auto backup SQL database, go set up a Schedule to automate the task. You can also choose to perform full backup or differential backup in Advanced tab.

Restore MSSQL databases from backup:

1. The created backup task will be listed in the Tasks tab. Click Advanced on its top right corner and choose Restore to proceed.

restore database from backup task

2. Select the computer and the databases you want to restore. You can choose the specific date and type (full or differential) of backup.

select database and specific backup to restore

3. Besides restoring to original location, you are even allowed to restore SQL database to another server within LAN. Make choice according to your needs. Then click Start Restore.

Overwrite the existing database (s): With this option ticked, the databases saved in the backup will overwrite the target databases of the same names. Otherwise the target databases with same names will be omitted.

restore to original or new location

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 Centralized Backupper Database Edition, is the most convenient and intuitive.

Besides MSSQL database, you can also use it for other important computer data, such as centralized system backup.