In case of data corruption or loss, you can restore SQL Server database from backup file using SSMS, T-SQL, command line, etc. Here are detailed steps.
If you have backed up your Microsoft SQL Server, you will get bak files which can be stored in any secure location and used to recover databases when needed.
In this article, I will present 4 methods to restore SQL Server database from backup. Pick the one you prefer.
β Restore SQL database via professional backuptool
If you want to perform flexible operations such as restoring multiple SQL databases at once but do not want to use T-SQL scripts, you can also use specialist tool to do it intuitively.
β Restore database in SSMS GUI
You can complete the operation in a graphic interface intuitively. It is suitable for novices, but only allows restoring one database from one backup file at a time.
β Restore SQL database using T-SQL
This way is more powerful and flexible to restore SQL Server backups, but you need to learn basic T-SQL commands in advance. Careless or incorrect use of T-SQL statements may lead to failures or even more severe problems.
β Restore SQL database using Windows command line
If you don't want to log into SSMS every time you backup or restore SQL database, you can use T-SQL commands in Command Prompt, which requires the support of SQLCMD.
Using AOMEI Cyber Backup to restore SQL databases from backup has some advantages over built-in tools like SQL Server Management Studio (SSMS) or SQLCMD.
It can back up multiple databases at once via simple clicks, and manage the backup tasks centrally. This allows you to restore one or more databases from the created tasks, without the need to find specific bak files from local drive or network paths. Besides, it even enables you to backup and restore SQL database from one server to another.
β Easy to Use: AOMEI Cyber Backup has a simple interface, making it user-friendly even for those unfamiliar with complex SQL commands.
β Central Control: You can manage all your backups from one place, which is convenient when you have multiple databases or servers.
β Automatic Scheduling: AOMEI lets you set automatic backups, reducing the risk of data loss. Built-in tools often require manual setup for automation.
β Flexible Restoration: You can restore a whole database or even to a different SQL server. This flexibility helps in various situations like testing or recovery.
β Handles Different Backup Types: Beyond SQL databases, it can also back up files and virtual machines, making it a versatile solution.
Download the 30-day free trial to have a try:
πΉHow to create SQL databases backup:
βΉ Run AOMEI Cyber Backup on the server machine, click Source Device -> Microsoft SQL -> Add Microsoft SQL. Download proxy program and install it on the device with SQL Server installed.
βΉ Next, click
1. Click Backup Task on the left menu bar, select Backup Task -> Create New Task to open the task creating page. Choose backup type as Microsoft SQL Backup, and set the Task Name as you like.
2. Click on Device Name to specify the SQL instances and databases you need to back up. You can select one or multiple database flexibly.
3. Select Target to save backups. You can specify a local or network path.
4. Before Start Backup, you can also set up a Schedule to run the SQL database backup automatically. Meanwhile, you can select the backup method as Full, Incremental or Differential Backup.
A created task will be listed in the Backup Task tab. Then you can edit it or restore SQL database from backup anytime you need.
πΉHow to restore SQL database from backup:
1. Click Backup Task on the left menu bar, locate the task you want to restore, and click
2. Select a backup version that you want to restore. If you’ve backed them up several times (full or differential), you can also make choice among the backups of different dates and modes.
3. Select the target location that you want to restore to. If you restore to original location, you don’t need to select target. Restore to original location is selected by default and you can directly click Start Restore.
If you want to Restore to new location, you need to select target and specify the name of the new database. You can also 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
When the restore is complete, you can see the restored database in Microsoft SQL Server Management Studio.
βNotes:
In Microsoft SQL Server, restoring database from backup via SSMS it the most intuitive way.
1. Launch SSMS and connect to your instance. Right-click Databases and choose Restore Databases… in the menu.
2. Choose Device as the source, then click on the ... icon to Add the backup file. If you can’t find it, try navigating to its storage path, and type the file name (with.bak) manually.
3. Confirm the backup information, and click OK to execute the restoration.
Note: If the restore failed with “The operating system returned the error '5(Access is denied)'…”, you can switch to Files tab, tick Relocate all files to folder and try it again.
1. Connect to the instance you want to restore database to. Click New Query to open a new window, and enter the following T-SQL command:
This is the basic command to restore SQL database from backup. Fill in your own info and click Execute to perform the operation.
Note: If you receive “The tail of the log for the database has not been backed up” during restoration, you can either backup the log first if it contains work you do not want to lose:
Or use WITH REPLACE or WITH STOPAT clause to perform restore and overwrite the contents of the log:
2. If you want to restore database from differential backup, then the commands will be:
Basically, add WITH NORECOVERY in statement is telling SQL Server to wait for more operations. You need to use WITH RECOVERY to finish the restore, or you may cause your SQL database stuck in restoring.
3. If you’ve backed up multiple databases into multiple bak files, and want to restore them at once, you need to use a little more complex T-SQL script, please refer to restore multiple SQL databases.
1. Search for “cmd” in Windows search bar and run Command Prompt as administrator.
2. Enter the following command to restore a full backup, press Enter to execute it.
To restore database from differential backup, the commands would be:
3. If you need to perform the same task frequently, you can also type the commands in a text editor and save it as a batch file with .bat extension. Thus you can double-click the batch file to execute the operation, or use Windows Task Scheduler to automate the task.
For more related info, please refer to batch script to backup SQL Server.
The above methods can help you restore SQL database from backup, but they all have some inconvenience in one way or another. If you want to backup and restore SQL Server flexibly with the simplest operation, you can try the professional tool - AOMEI Cyber Backup.
Q: What happens to the existing data when I restore a SQL database?
A: When restoring a full backup, existing data is replaced with the backup's content. If you're restoring a differential or transaction log backup, changes are applied to the existing database, maintaining continuity.
Q: How can I ensure a successful restoration?
A: To ensure a successful restoration, validate the backup, test the restoration process on a staging environment, check for errors or conflicts, and monitor for consistency and data integrity after restoration.
Q: How do I minimize downtime during a SQL database restoration?
A: To minimize downtime, schedule restorations during off-peak hours, use differential or transaction log backups to reduce restoration time, and consider database mirroring or replication for high availability.
In this article, I introduced several different ways to restore SQL Server databases from backups, such as SSMS GUI, T-SQL, and AOMEI Cyber Backup, which combines the advantages of these methods.
With this tool, you can not only backup and restore SQL database, but also manage other types of computer data. For example, you can perform centralized system backup of all computers on network.