Restore sql server 2k backup with different file name

I have a sql2k database (incidentally merge replicated to remote location).

I backed up a database then deleted some records. I now need to restore the backup to a new database on the same machine (to get at some of the records I deleted). As I understand it it will use the same filename for the restored database - meaning it cant be done on the same machine.

How can I restore it as a new database?

Thanks!...
niicoAsked:
Who is Participating?
 
ptjcbConnect With a Mentor Commented:
You are right - you cannot restore to a new db with the same filename on the same server.

Create a new db. Call it anything you want.

If you are using the Restore Database wizard in EM, go to the second tab, "Options" and click the "Force Restore over existing database" then change the "Restore As" listings to point to where your back up is.

If you use QA and t-sql then:

RESTORE DATABASE Northwind
   FROM disk = 'E:\db_backups\Northwind_db_200503302200.BAK'
   WITH RECOVERY, REPLACE,
      MOVE 'Northwind_DATA' TO 'E:\db_backups\Northwind.mdf',
      MOVE 'Northwind_Log' TO 'E:\db_backups\Northwind_log.ldf';
0
 
Kevin3NFCommented:
Concur with ptjcb:

WITH MOVE is the key part for you...you are telling SQL Server to either change the physical file name, location, or both.
0
 
Brendt HessSenior DBACommented:
Concur as well.  We actually use this as part of a production methodology on our servers, allowing for continuous static data access while updating into a different, identical copy of the DB.

Your Key:

Change the destination of the MOVE to some new directory is safest.  So, if your data files are in E:\SQL\DB\DATA, restore with MOVE to E:\SQL\DB\TEMP
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
niicoAuthor Commented:
Thanks for that both of you.

When you say change the 'Restore As' listings - can you please tell me exactly where that option is.


Thanks again...
0
 
niicoAuthor Commented:
Just to clarify in your example, is 'Northwind' the old database name or the new one? Are Northwind_Data and Northwind_Log file names? If so should they have .mdb & .ldf extensions?

thanks...
0
 
Kevin3NFConnect With a Mentor Commented:
Northwind would be the old database name, assuming you want to leave the old db intact.

RESTORE DATABASE Northwind_New
   FROM disk = 'E:\db_backups\Northwind_db_200503302200.BAK'
   WITH RECOVERY, REPLACE,
      MOVE 'Northwind_DATA' TO 'E:\db_backups\Northwind.mdf',
      MOVE 'Northwind_Log' TO 'E:\db_backups\Northwind_log.ldf';

Northwind_data and northwind_log are "logical" file names.  Only Physical file names have.mdf and .ldf by default
0
 
ptjcbCommented:
Thanks Kevin3NF - that did clarify that I am overwriting database files with the backups of the other database.

RESTORE DATABASE Northwind_New
   FROM disk = 'E:\db_backups\Northwind_db_200503302200.BAK'
   WITH RECOVERY, REPLACE,
      MOVE 'Northwind_New_DATA' TO 'E:\db_backups\Northwind.mdf',
      MOVE 'Northwind_New_Log' TO 'E:\db_backups\Northwind_log.ldf';

Added the "_New" to the logical file names.

For example, if I created a Northwind_New, then I would also have to have different logical names - the Northwind_New_Data and Northwind_New_Log. (After the move requirement). I want to overwrite them with the backup from the Northwind db.

0
 
niicoAuthor Commented:
Thanks a lot.

Here is a useful link I found that may help others explaining how to get logical filenames (amongst other things): http://www.devx.com/getHelpOn/10MinuteSolution/16503/1954?pf=true
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.