November 23, 2009 at 8:59 am
Hi, I can't attach db file,
Customer send me db from recovery disk, I tryed attach to SQL Server 2005, my command is:
Exec sp_attach_single_File_Db @DbName = 'S_Product',@Physname = 'd:\db\s\S_Product.mdf'
Message is her:
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'S_Product'. CREATE DATABASE is aborted.
Msg 9003, Level 20, State 9, Line 1
The log scan number (12219:10486:25) passed to log scan in database 'S_Product' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
Can You help me, how i repair db.
Thank you
Maros
November 23, 2009 at 9:10 am
Got a backup? Ask your client. It's the best way to fix this.
If not, try hacking the DB back into the server. See the procedure here: http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/
If you go that way, don't immediately set to emergency mode or repair, just hack the DB back into the server, then post with status, any error messages, etc and we'll take it from there. One step at a time.
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
November 23, 2009 at 2:02 pm
Did you get an .ldf file as well with the backup, or did you just get an .mdf file? It appears that from the error that either the log file is missing or is not in a location that it was expecting it to be in.
Joie Andrew
"Since 1982"
November 24, 2009 at 12:21 am
Joie Andrew (11/23/2009)
It appears that from the error that either the log file is missing or is not in a location that it was expecting it to be in.
As far as I'm aware the error indicates some form of corruption in the log or that the ldf file that's been attached is not the one that belongs to the database. I've had this when attaching a db, complete with all ndfs and the ldf.
If the log was missing, either attach would create it, or there would be a file activation error.
File activation failure. The physical file name “D:\Develop\Databases\TestingLogDeletion.ldf” may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
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
November 24, 2009 at 12:46 am
Thank you,
I have not backup. I set emergency mode and repair. Your tip help me. My problem was solve, script is here:
USE [master]
--set emergency mode
ALTER DATABASE S_Product SET EMERGENCY
--set single user
ALTER DATABASE [S_Product] SET SINGLE_USER WITH ROLLBACK IMMEDIAT
ALTER DATABASE [S_Product] SET SINGLE_USER
--repair db
DBCC CHECKDB ('S_Product',REPAIR_ALLOW_DATA_LOSS)
DBCC CHECKDB ('S_Product', repair_fast)
--set online mode
ALTER DATABASE S_Product SET ONLINE
--set back multi user
ALTER DATABASE [S_Product] SET MULTI_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [S_Product] SET MULTI_USER
November 24, 2009 at 8:27 am
simegh (11/24/2009)
I have not backup. I set emergency mode and repair.
You do realise that you may very well have lost data. You will certainly have lost any transactions that had completed but not been written to disk.
Why on earth is there no backup?
--set single user
ALTER DATABASE [S_Product] SET SINGLE_USER WITH ROLLBACK IMMEDIAT
ALTER DATABASE [S_Product] SET SINGLE_USER
Why run the statement twice?
--repair db
DBCC CHECKDB ('S_Product',REPAIR_ALLOW_DATA_LOSS)
DBCC CHECKDB ('S_Product', repair_fast)
No need to run once with REPAIR_ALLOW_DATA_LOSS and once with repair_fast. REPAIR_ALLOW_DATA_LOSS does everything that the other repair levels do, and more. Also, if you check Books Online
REPAIR_FAST
Maintains syntax for backward compatibility only. No repair actions are performed.
--set online mode
ALTER DATABASE S_Product SET ONLINE
--set back multi user
ALTER DATABASE [S_Product] SET MULTI_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [S_Product] SET MULTI_USER
Why run the statement twice?
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply