SQL Server restores a backup under a new name when you type that name in the restore dialog or in the RESTORE DATABASE statement. File paths still point to the original files unless you set new ones. Three methods restore SQL database with different name next to the live one. Free methods use the dialog or T-SQL and need a readable backup. Recovery software moves MDF file data to a new database when no backup exists.
You need a test copy of KitchenDB next to the live database. Your backup is ready but the live files occupy the original paths.
Search results show how to restore SQL database with different name in the Destination box and stop there. Few explain why the file names matter or how to rename a database that already exists.
This guide explains how to create a test copy without touching production, including the rename statements that fix the file names.
Microsoft states in the rename a database reference that renaming does not alter the physical or logical names of the database files.
Which Items Follow the Backup and Not the Name?
Three items follow the backup and not the name you type:
- File paths. Microsoft states that every file returns to its original location by default.
- Logical names. The backup stores them and MOVE clauses refer to them.
- Tail log option. Per Microsoft a ticked box sets the live database to the restoring state when you restore a copy.
Find your situation in the table below.
| Situation | Method |
|---|---|
| Readable backup and a screen | #1 |
| Readable backup and a script | #2 |
| Only an MDF file or a damaged backup | #3 |
| Database already restored under the old name | Rename section below |
3 Ways to Restore SQL Database With Different Name
Backups power the first two methods and the MDF file powers the third.
| Feature | SSMS Dialog | T-SQL Script | Recovery Software |
|---|---|---|---|
| Cost | No fee | No fee | Free scan and a licensed export |
| Best for | One time copies | Repeat test restores | MDF files and damaged backups |
| Needs | Backup file and folder rights | Backup file and logical names | Windows PC and an MDF file duplicate |
| Main limit | Typing errors in the grid | Wrong logical names | Trial export limit |
Method #1: Rename the Copy in SSMS (Free, Needs a Healthy Backup)
Microsoft shows every page in the restore a database backup with SSMS steps. Seven actions restore KitchenDB as KitchenDB_Test:
- Right click Databases under your server and choose Restore Database.
- Select Device as the source and add KitchenDB.bak with the browse button.
- Replace the name in Database under Destination with KitchenDB_Test.
- Open the Files page and edit the paths in the Restore the database files as grid. Microsoft names that grid for a new location of every file.
- Give the data file and the log file new names like KitchenDB_Test.mdf and KitchenDB_Test_log.ldf.
- Clear Take tail-log backup before restore on the Options page.
- Click OK and wait for the confirmation.
Avoid the Overwrite the existing database (WITH REPLACE) box for a copy. Microsoft states that REPLACE also permits a restore to overwrite an existing file that it cannot verify as part of the restored database.
Method #2: Restore With a Script and MOVE (Free, Needs Logical Names)
Scripts repeat the same restore in seconds. Logical names come from RESTORE FILELISTONLY as shown in the guide to restore a database from a BAK file.
RESTORE DATABASE KitchenDB_Test
FROM DISK = N'D:\Backups\KitchenDB.bak'
WITH MOVE N'KitchenDB' TO N'E:\SQLData\KitchenDB_Test.mdf',
MOVE N'KitchenDB_log' TO N'E:\SQLData\KitchenDB_Test_log.ldf',
RECOVERY;
As per Microsoft's RESTORE arguments, MOVE places a file at the path you give and avoids collisions with files that exist. Find the new database name in the first line.
REPLACE removes any existing database of that name per Microsoft. New names need no REPLACE option.
Method #3: Export the MDF File to a New Database With EmailDoctor (Free Preview, Licensed Export)
Some servers have no backup at all. EmailDoctor SQL Database Recovery processes the duplicate of your MDF file and sends its objects to a database with the name you type.
Place an MDF file duplicate in a safe folder first. Trial exports stop at five extra objects and 25 rows of one table.
Scan the Duplicate and Preview Objects
- Open the recovery software on the Windows PC.
- Press Open and select KitchenDB.mdf from the duplicate folder.
- Choose Quick Scan for a healthy file or Advance Scan for a damaged one. Tick Recover Deleted Objects only when you need deleted items.
- Select the SQL Server release yourself or accept Auto detect.
- Review the scan report and open a table from the tree to preview its rows.
Export Under the New Name
- Click Export at the top.
- Select SQL Server Database in Export Options.
- Choose Server Name and the Authentication mode.
- Select Create New Database and set Database Name to KitchenDB_Test.
- Mark the tables and other objects you want to export.
- Select With Schema & Data and press Export.
- Confirm Export Completed and refresh Databases in SSMS.
How to Rename the Database and Its Files After a Restore?
Databases restored under the old name need a rename. Microsoft requires ALTER permission on the database and states that other users cannot access it during the rename. Use single user mode with a rollback to disconnect them.
USE master;
GO
ALTER DATABASE KitchenDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE KitchenDB MODIFY NAME = KitchenDB_Test;
ALTER DATABASE KitchenDB_Test SET MULTI_USER;
GO
File names remain as they were. Microsoft documents NEWNAME for the logical name of a file in the ALTER DATABASE file options.
ALTER DATABASE KitchenDB_Test
MODIFY FILE (NAME = KitchenDB, NEWNAME = KitchenDB_Test);
Microsoft recommends a backup of the master database after a rename on SQL Server. Logins that used the old name as their default database show error 4064 until you set the new name.
ALTER LOGIN [report_user] WITH DEFAULT_DATABASE = [KitchenDB_Test];
Which Problems Stop You When You Restore SQL Database With Different Name?
Five problems stop most copy restores:
| Problem | Reason | Fix |
|---|---|---|
| Restore reports files in use | The original paths belong to the live database | Edit the paths in the Files page grid or add MOVE clauses |
| Source database enters the restoring state | Tail log option was selected for the copy | Clear it on the Options page |
| Files of the live database get overwritten | REPLACE was ticked for a copy | Clear the overwrite option |
| Rename fails with users connected | Other sessions access the database | Use SINGLE_USER with ROLLBACK IMMEDIATE |
| Error 4064 after the rename | A login still points to the old name | Set the new default database with ALTER LOGIN |
Try Method #3 on a duplicate when no backup is readable.
Frequently Asked Questions
Can I Restore SQL Database With Different Name From One Backup?
Yes. Type the new name in Destination or in the first line of RESTORE DATABASE. Edit the file paths too.
Does a Rename Alter the File Names?
No. Microsoft states that renaming a database does not alter the physical or logical names of its files.
Do I Need WITH REPLACE for a Copy?
No. REPLACE removes an existing database of the same name per Microsoft. Select it only when that is your goal.
Can I Rename a System Database?
No. Per Microsoft renaming is not possible for system databases.
Which Permission Renames a Database?
Microsoft requires ALTER permission on the database.
Can I Rename a Database in Use?
No. Per Microsoft the name cannot be altered while other users access the database. Set single user mode with a rollback first.
Do I Need a Master Backup After a Rename?
Yes. Microsoft recommends a backup of the master database after a rename on SQL Server.
Can I Rename the Logical File Name?
Yes. Use MODIFY FILE with NAME and NEWNAME. Microsoft documents the clause in the ALTER DATABASE file options.
Can Recovery Software Use a New Database Name?
Yes. Select Create New Database in the export window and type the name you want.
Does the Trial Version Limit the Export?
Yes. Trial exports are limited to five extra objects and 25 rows of one table. Buying a license ends it.
Author’s Verdict
Typing a new name in the restore dialog sets only the database name. File paths and file names need their own attention.
Use MOVE for repeat restores and the Files page for one copy. Recovery software handles the cases with no backup.
Check the files of the copy before you retire the old database.
- Restore SQL database with different name and set new file paths because restores retain the original ones.
- MOVE and the Files page place the files in new folders.
- REPLACE removes an existing database of that name and a copy never needs it.
- Renames do not alter logical or physical file names.
- Recovery software exports an MDF file under a new database name when no backup exists.
- Try every step on a test server first.