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

Restore SQL Server 2000 database

One table in my current database needs to be restored.  I have a backup of my database (.bak) from last week that I would like to restore to a new database so I can then extract the table.  When I try to restore the backup, it says that the .mdf cannot be overwritten.  It is being used.  Use WITH Move to identify a valid location for the file..  It says the same for the .ldf file.  

How do a get a copy of the database from the .BAK so I can get the information from this one table?
0
kshumway
Asked:
kshumway
1 Solution
 
LeDaoukCommented:
try to restore on another machine
0
 
LeDaoukCommented:
then extract your data and do what ever you want
0
 
Daniel_PLDB Expert/ArchitectCommented:
You can restore this database to same server from which it was taken. You need to restore database to different filenames/filespath because there already are files from live database.
To perform successfully you need
 
--check logical filenames of your database
RESTORE FILELISTONLY FROM DISK = '<path to backup file>';
GO
--restore database to different name and filepath
RESTORE DATABASE <dbname> FROM DISK = '<path to backup file>'
WITH MOVE '<logical data filename>' TO '<new path for this file>',
MOVE '<logical log filename>' TO '<new path for this file>';
GO

Open in new window


For test_db it would look like:
 
--check logical filenames of your database
RESTORE FILELISTONLY FROM DISK = 'D:\BACKUP\test_db.bak';
GO
--restore database to different name and filepath
RESTORE DATABASE test_db_copy FROM DISK = 'D:\BACKUP\test_db.bak'
WITH MOVE 'test_db_data' TO 'E:\MSSQL\Data\test_db_data.mdf',
MOVE 'test_db_log' TO 'F:\MSSQL\Data\test_db_log.ldf';
GO

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
kamindaCommented:
I agree with Daniel, just to add some thought if this fail safe was not there in SQL server you would have overwritten your current db with the last week backup. :)
0
 
veasnamuchCommented:
  I think you can restore your backup to a new database name then import the missing table from that restore database to your current one.

Suppose your current database name is ABC, so it default file would be ABC.MDF and ABC_Log.LDF. When you restore,  please change in Options, Move to physical filename to the path you prefered, and the database file name to ABCD.MDF and ABCD_Log.LDF for example.

DB.JPG
0
 
havj123Commented:
Try to restore the DB with different name. If restored then create the insert script for that table and update wherever you want.
0
 
Alpesh PatelAssistant ConsultantCommented:
Hi, There are mdf and ldf already exists, please change the name of them. It will resolver your issue.
0
 
kshumwayAuthor Commented:
Thank you Daniel.  It worked perfectly!
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now