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.

5 Methods to restore database in SQL Server:

• Method 1. Restore SQL database with SSMS GUI
• Method 2. Restore MSSQL database using T-SQL statement
• Method 3. Restore SQL databases with command line
• Method 4. Restore SQL Server using PowerShell
• Method 5. Restore one or multiple SQL databases intuitively

Method 1. Restore SQL database with SSMS GUI

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

*In SQL Server, you can only restore database from 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 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. 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 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:

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

Backup databases in MSSQL:

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

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.

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

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.

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.

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

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.

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.