How to Restore a SQL Server Database with Move to New Location

Overview of Restoring SQL Database with Move

SQL database recovery is the process of copying data from a backup to a database server to recover lost or corrupted data. It ensures data integrity and availability in case of system failures or data corruption. One advanced technique in SQL database restoration is using the MOVE option.

The MOVE option is used to relocate the database files to a new location during restoration. This is particularly useful when restoring a database to a different server or directory structure.

You can go over the three ways to restore SQL database to new location in this post. Select the solution you want.

• Restore a SQL database with Move scripts
• Restore SQL database with a professional backup tool
• Restore file to new database with SSMS

How to Restore a Database with Move in SQL Server

If you are familiar with using scripts to restore a SQL Server database, this part describes the steps for you to restore SQL databases with a MOVE script.

1. Retrieve the logical file names of the database using the following command:

RESTORE FILELISTONLY FROM DISK = 'PathToBackupFile';

2. Use the logical file names to map them to new physical file locations:

RESTORE DATABASE [DatabaseName]
FROM DISK = 'PathToBackupFile'
WITH MOVE 'LogicalName1' TO 'NewPath1\PhysicalName1.mdf',
MOVE 'LogicalName2' TO 'NewPath2\PhysicalName2.ldf';

3. Consider a scenario where you need to restore a database named "SalesDB" to a new server. The logical names are "SalesDB_Data" and "SalesDB_Log". The commands would be:

RESTORE FILELISTONLY FROM DISK = 'C:\Backups\SalesDB.bak';
RESTORE DATABASE SalesDB
FROM DISK = 'C:\Backups\SalesDB.bak'
WITH MOVE 'SalesDB_Data' TO 'D:\SQLData\SalesDB.mdf',
MOVE 'SalesDB_Log' TO 'D:\SQLLogs\SalesDB.ldf';

💡Note: The MOVE clause is not necessary if the database being restored already contains the same quantity and kind of files as the backupset being restored.

How to Restore SQL Database to a New Location Easily

AOMEI Cyber Backup is an excellent solution for this task. It provides an automated and user-friendly way to back up and restore SQL Server databases. With its easy-to-operate interface, simple and concise page layout, and straightforward restore process, AOMEI Cyber Backup simplifies database management, making it accessible even for those with minimal technical expertise.

• Automated Backup: Schedule VMware/Hyper-V virtual machine backup and SQL Server Backup to run automatically at convenient times.
• User-Friendly Interface: The software features a simple and concise page layout, making it easy to set up and manage backups.
• Efficient Restore Process: Restoring your SQL database to a new location is a straightforward process, thanks to the intuitive design of AOMEI Cyber Backup.

✍Restoring a SQL database to a new location with AOMEI Cyber Backup is a seamless process. Follow these steps for a successful restoration:

1. Open the AOMEI Cyber Backup application. Choose the backup file of the SQL database you want to restore from all available backups, making it easy to click the Restore section.

2. Select a backup version that you want to restore.

3. Enter the new location where you want to restore the database. This can be a different directory on the same server or a different server entirely.

4. Click the Start Restore button to begin the process. AOMEI Cyber Backup restores your database files to the new location easily.

AOMEI Cyber Backup is a powerful backup and recovery solution designed to protect your critical data. It allows you to restore database from backup in a few steps in case of any data threat.

Conclusion

Restoring a SQL database to a new location doesn't have to be a daunting task. Restoring an SQL database with the MOVE option allows for flexibility in relocating database files. Meanwhile, with AOMEI Cyber Backup, the process is simplified and efficient, allowing you to focus on more critical tasks. By following the steps outlined in this guide and leveraging the powerful features of AOMEI Cyber Backup, you can ensure a smooth and secure restoration process.