?
Solved

Restore SQL Server 2000 database

Posted on 2011-04-18
8
Medium Priority
?
520 Views
Last Modified: 2012-05-11
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
Comment
Question by:kshumway
8 Comments
 
LVL 4

Expert Comment

by:LeDaouk
ID: 35421681
try to restore on another machine
0
 
LVL 4

Expert Comment

by:LeDaouk
ID: 35421682
then extract your data and do what ever you want
0
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 2000 total points
ID: 35421744
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Expert Comment

by:kaminda
ID: 35421849
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
 
LVL 1

Expert Comment

by:veasnamuch
ID: 35422005
  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
 
LVL 6

Expert Comment

by:havj123
ID: 35422831
Try to restore the DB with different name. If restored then create the insert script for that table and update wherever you want.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35423472
Hi, There are mdf and ldf already exists, please change the name of them. It will resolver your issue.
0
 

Author Closing Comment

by:kshumway
ID: 35423484
Thank you Daniel.  It worked perfectly!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question