AOMEI Cyber Backup
Restore SQL Backup to New Database Step by Step (3 Methods)
Want to restore your SQL backup to new database with different name, or even restore to another server? In this article I will show you exactly how to do that in both two circumstances.
By Crystal / Updated on June 14, 2023
Case: Can I Restore SQL Backup to Different Database?
I have a backup of Database1 from a week ago. The backup is done weekly in the scheduler and I get a .bak file. Now I want to fiddle with some data so I need to restore it to a different database - Database2. Is there any other way of restoring it to Database2, or at least, how do I browse through the data of that .bak file?
- stackoverflow.com
In some cases, we may want to restore SQL backup to new database with a different name to distinguish it from the source database. Sometimes we may even want to transfer the database to another instance or server for migration.
So how do we restore backup to new database in SQL Server? In this article, I summed 3 methods to help you accomplish this. If you find them complicated, you can also choose professional software.
How to Restore SQL Backup to New Database
The following 3 methods are applicable to different scenarios.
â–¶To restore SQL backup to new database with different name, please choose Method 1 or 2.
â–¶To migrate database from one instance/server to another, all the methods apply, but Method 3 is more direct.
Tip: Before you begin, please make sure no one else is using, or you may get your SQL database stuck in restoring.
Method 1: Use SSMS GUI to Restore SQL Backup to New Database
The backup and restore feature of Microsoft SQL Server Management Studio (SSMS) can help you achieve this operation. Just make sure you've created SQL backups successfully, and then do the following to restore them.
1. Launch SSMS, and connect to your target instance. Now please don’t create a new empty database to receive the data from the backup, you just need to right-click Databases and choose Restore Database.
2. In the prompt window General page Source section, select Device > … >Add, to specify the location and select the backup file you want to restore. The name will be filled automatically in the blank of Database. Then click OK.
3. When you restore SQL backup to the same server, the operations next will be different from restoring to another server.
✦Restore SQL Database to the Same Server with Different Name
In Destination > Database, write a new name which is different from the former one.
Click OK, and it will restore the backup to a new database.
✦Restore SQL Database to Another Instance/Server
You can also restore SQL Server database backup from bak file to another server, but in this case you don't have to change the name. *If you really want to change it, you can rename the database in the blank of Database, Destination section.
Select Files in the left menu, and tick Relocate all files to folder in Restore database files as section. Then Click OK. When it’s done, Refresh the database list and you can see the database is here.
Method 2: Use T-SQL Commands to Restore SQL Backup to New Database
If you are familiar with T-SQL, you can try this method instead of backup and restore GUI.
1. Launch SSMS and connect to your server, right-click the target instance name and select New Query.
2. Input following statements to find the logical filename of your backup.
Note: In ‘filepath\backup.bak’ you need to write where your backup is putted in and the file name of your backup. For example: C:\mydb\backup.bak
3. Click Execute and you will see the logical filename in Result section.
4. Input following statements to restore this backup to new database (If you are restoring the backup to another server, you don't need to rename the database). You don’t have to create a new database before either.
WITH
MOVE 'logical_filename' TO 'filepath\newname.mdf',
MOVE 'logical_filename_log' TO 'filepath\newname_log.ldf'
✦Restore SQL Backup to the Same Server with Different Name
As for the 'filepath\newname.mdf' and 'filepath\newname_log.ldf' part you need to write the path where the new database should be put in.
If you don't know where the folder is, you can right-click your target instance and select Properties > Database Settings > Data to find out.
✦Restore SQL Backup to Another Instance/Server
Before you start, make sure your target instance has the permissions of access to the backup file.
In 'filepath\newname.mdf' and 'filepath\newname_log.ldf' you need to write the path of the target instance, instead of the original instance.
Click Execute. When you see the result of success, click Refresh, and the database is in list.
Note: If you received the error report: Cannot open backup device. Operating system error 5 (Access is denied), the reason could be that the account you use to log in to SQL server does not have permission to read or write to the target share. You can view this article to learn solutions: SQL Server Backup to Network Share Access Denied
Method 3: Use Detach/Attach to Move SQL Database to Another Instance/Server
If you haven’t backup the database yet, Detach/Attach is a convenient way for you to directly move database from one instance/server to another.
1. Right-click the database and select Tasks > Detach.
2. In the prompt window, select Drop Connections and Update Statistics as need. Click OK to detach the database.
The database will be detached then, but the files will remain in the same folder.
3. Right-click Database in another instance, select Attach.
4. Click Add… in the prompt window, find the database files in their correspondent folder, normally they are still in DATA, end with .mdf and .ldf.
If you can’t find them, you can just select the correspondent folder and input filenames in search box, then click OK directly, and they will be shown in database details section.
Click OK to attach the database to this instance.
And if you just want to copy the database to another instance/server, and you are not using the Express version, Copy Database Wizard is a simpler tool for you. To learn how you can view this article Backup and Restore SQL Database from One Server to Another
Centralized Tool to Restore SQL Backup to Another Instance/Server
To make the restoration to another server easier, you can choose AOMEI Cyber Backup, to use simple clicks to backup and restore SQL database to another instance, even another computer, without any complex configuration and reinstallation.
Compared to traditional ways, this software is easier, and it allows you can centrally backup and restore on the computer which installed this software. And the functions include:
â—† Auto Backup: run VMware/Hyper-V virtual machine backup and SQL Server Backup (including SQL express)automatically.
â—† Flexible Backup Methods: offer full/incremental/differential backup as well as retention policy to meet your specific needs.
â—† Easy to Restore: Restore SQL databases to another computer from intuitive interface without the complexity of backup procedures.
â—† Email Notification: Monitor backup performance and provide timely warning of abnormal performance information.
â—† Role Assignment: create a sub-account to manage SQL Server backups remotely.
Here’s a 30-day Free Trial for you:
To properly install Cyber Backup, you can follow these steps:
1. To properly use AOMEI Cyber Backup Trial Edition, especially if you want to manage multiple computers within LAN, you need to access to Source Device > Add Microsoft SQL. Click Already installed proxy and select the proxies you need to add.
Note: If you do not install the AOMEI Cyber Backup Agent program into your computer, please download proxy program to local first.
2. Click "..."--> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication. Enter the credentials and click Verify.
Then you can schedule your SQL database backup and restore it to another instance with ease.
Backup and Restore SQL Database to Other Instances/Servers
Click Backup Task on the left menu bar, then click Create New Task to start create database backup task.
Step 1. Choose backup type as Microsoft SQL Backup, and you could also try VMware Backup or Hyper-V Backup.
Step 2. Select the databases you want to backup and specify a destination to store the backup files.
Step 3. Schedule backup methods and time, then click Start Backup.
✦ How to Restore SQL Backup to Another Instance or Computer
Step 1. The created backup task will show in Backup Task tab, you can manage or restore it here instead of locating a specific bak file on the disk. Just click ""--> Restore.
2. Select the backup that you want to restore
3. Specify a target location that you want to restore to.
You could restore to original/new location.
- "Restore to original location": If you choose the option "Over the database with the same name", the detected database with the same name will be overwritten automatically when restoring. Otherwise, the database with the same name will be skipped.
- "Restore to new location": Select target and specify the name of the new database. You can also modify the storage location.
Summary
In this article, I introduced 3 ways to restore SQL backup to a new database, or to another server.
If you think these ways are not intuitive enough, you can also choose a simpler tool: AOMEI Cyber Backup. In addition to SQL database, it is also a centralized backup software for virtual machines. You can create VM backups and SQL backups from a central console.
