February 1, 2011 at 9:50 pm
Hi All,
We have an SQL 2008 database of around 12GB. I was trying to create a copy of the database through the Microsoft SQL Management Server Studio. The server is in production mode, and it was taking too long to copy, and had locked off all users, so i canceled the copy, but it was still taking too long so I closed the Management Server Studio and restarted it. The database was not listed, and I cannot reattach it. I have traveled the whole internet looking for a recovery solution to no avail. I have these file .ldf (1MB), .mdf(62MB) .ndf(12GB).
How can I recover the database? The backup I have is three weeks old.
February 1, 2011 at 10:24 pm
What's the error on trying to attach?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 4:55 am
When I attach the database, I get the error ---
An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
---
That's all, the error I get.
February 2, 2011 at 5:06 am
Nothing else? No details in the referenced hyperlink?
Can you try and attach using t-SQL script? (the dialog can generate the code for you, there's a script button at the top). Need to see the errors before I can really say anything useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 6:18 am
Thank you very much GilaMonster.
After running the t-script, I get this message.
Msg 5173, Level 16, State 2, Line 1
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
--
Apparently, My partner renamed the .ldf .mdf and .ndf and created a new database and pasted the files to be owned by the new database.
February 2, 2011 at 6:26 am
From the message he missed one or more files. All of the files involved have to be from the same database, you can't mix and match (that includes the log file)
Check that when your colleague copied the old files over he copied all of them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 7:10 am
Thanks,
Suppose we get the .mdf and .ndf, but no log file, can we move from there?
February 2, 2011 at 8:58 am
Maybe, but you'll need to delete the log file from the database that you created (the one whose mdf and ndf you overwrote). SQL knows that log doesn't belong to the mdf, and it won't start the DB up if that's the case.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 2, 2011 at 9:01 am
Here is my T-SQL code
USE [master]
GO
CREATE DATABASE [training] ON
( FILENAME = N'G:\test\UTraining_Data.mdf' ),
( FILENAME = N'G:\test\Training_log.LDF' ),
( FILENAME = N'G:\test\Training_1_Data.ndf' )
FOR ATTACH
GO
if exists (select name from master.sys.databases sd where name = N'training' and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC [training].dbo.sp_changedbowner @loginame=N'trainingHQ\Administrator', @map=false
GO
and error is
Msg 232, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - The pipe is being closed.)
This is after I have renamed the .ldf, .ndf, and .log to the original file names
February 2, 2011 at 9:06 am
Oh, time to hack the database back in and see if that will help.
http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx
If you get it 'attached', check the state (select name, state_desc from sys.databases), then post back with the state, and any message or errors related to this database from the error log
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2011 at 1:48 am
OK, When I create a similar database and swap the files with the damaged file, then try to get the database online again, I get the error
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Database 'training' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
ALTER DATABASE statement failed.
Log file 'G:\test\Training_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. (Microsoft SQL Server, Error: 5173)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=5173&LinkId=20476
February 3, 2011 at 1:53 am
-
February 3, 2011 at 2:18 am
What files are you swapping?
M&M
February 3, 2011 at 2:37 am
I have three files
.mdf (primary database)
.ndf (secondary database)
.ldf (log)
And some different versions of the files that I'm not sure of.
February 3, 2011 at 3:07 am
lordmwesh (2/3/2011)
Log file 'G:\test\Training_log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. (Microsoft SQL Server, Error: 5173)
The log file is not from the same database as the other two file. Either you're not replacing the log file, or the log file you're using is from some other DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply