• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

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!...
0
niico
Asked:
niico
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
ptjcbCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Kevin3NFCommented:
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now