?
Solved

Replacing MDF & LDF files from a tape backup

Posted on 2004-11-09
8
Medium Priority
?
412 Views
Last Modified: 2008-02-01
We have a database named 'lime' which became corrupt after a data import on Monday.

Path to the files is
E:\MSSQL Server\Data\MSSQL\Data\lime_Data.MDF
and
E:\MSSQL Server\Data\MSSQL\Data\lime_Data.LDF

I moved the lime_Data.MDF & lime_Data.LDF files to another location before restoring previous copy of the files from the image backup tape made on Friday into E:\MSSQL Server\Data\MSSQL\Data\

Now when use the SQL manager it shows lime(suspect). I tried putting back Modays files but still get the same 'suspect' mark on the database

Obviously moving the files and replacing them with previous ones has caused a problem. How can I get SQL to accept the files from Friday. (or any of the files!!)



0
Comment
Question by:hassalla
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 720 total points
ID: 12535947
Drop the database, create a new one with the same name and same files, stop sql server, overlay your database and log files, start sql server.
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 1200 total points
ID: 12536150
Hi hassalla,

I would be more tempted to use sp_attach_db to see if this works first, the example below creates a new copy of the pubs db from file copies of the mdf & ldf while SQL server is stopped:

sp_attach_db 'pubs3', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs3.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs3_log.ldf'

Essentially you can do exactly the same, but just try a different db name than your current db first like lime2.
more details about sp_attach_db here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp
0
 
LVL 15

Assisted Solution

by:mcmonap
mcmonap earned 1200 total points
ID: 12536178
FYI you can also use sp_detach_db to drop the current suspect databases without deleting the underlying mdf and ldf files
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 21

Assisted Solution

by:mastoo
mastoo earned 720 total points
ID: 12536460
I would guess either works.  BOL says:

sp_attach_db should only be executed on databases that were previously detached

which is the case here.
0
 
LVL 19

Assisted Solution

by:Melih SARICA
Melih SARICA earned 80 total points
ID: 12539078

What kinda data import could drop a DB in to suspect statement ?

Melih SARICA

0
 

Author Comment

by:hassalla
ID: 12543360
some terms I don't recogise here
DROP - does this mean (dtm) DETACH?
OVERLAY - dtm COPY ?
BOL - ?
0
 
LVL 15

Assisted Solution

by:mcmonap
mcmonap earned 1200 total points
ID: 12543509
Hi hassalla,

BOL is books on line, MSSQLs online help, drop in the sense that mastoo has used it means 'delete', and 'damage' in the way non_zero has used it. Overlay as mastoo has used implies file copying the files over the top of the existing files.
0
 

Author Comment

by:hassalla
ID: 12546214
THANKS TO EVERYONE WHO CONTRIBUTED
all is well now.
I used the
sp_attach_db 'dbname'
method to clear things up.

no doubt I will be back with more questions about SQL as it took me a while to figure out stored procedures and hot ot run them. RTFM might have helped me.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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