Attach Database Problem
-
Hi Everybody,
Recently my SQl 7.0 was corrupted, so i re-installed sql 7 before that physically moved all mdf and ldf to different place. Now, the problem is when i am attaching the databases some of them are working fine and for some databases i am getting this error.
My T-Sql Command is like this:
exec sp_attach_db @dbname = 'garage', @filename1='e:\mssql7\garage__data.mdf',
@filename2='e:\mssql7\garage_log.ldf'
Error:
Server: Msg 9004, Level 21, State 1, Line 1
The log for database 'garage' is corrupt.
Connection Broken
I created one database with the name 'garage' and copied the old mdf file on the new one but it is also giving error. Is there any other way to attach the database? Thanks in advance.
Ramaa
-
Hi
Try to move log file to another location and while attaching database files specify only .mdf file. If SQL Server Creates new log for you, you will be lucky!!!
-
Hi Vikas,
I moved log file to some other location and i am attaching the database with this command.
EXEC sp_attach_single_file_db @dbname = 'garage',
@physname = 'e:\mssql7\garage_data.mdf'
I am getting this error.
Server: Msg 945, Level 14, State 2, Line 1
Database 'garage' cannot be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'garage'. CREATE DATABASE is aborted.
Ramaa
-
Hi,
RAMA
CAN YOU PROVIDE TO ME SUCH .mdf file , only then i can provide you to a best solution because i have to do some R & D on it.
Regards,
Amit Gupta
(M.C.D.B.A.)
-
Hi
Try with Enterprize manager
Attach,detach
-
Hi Amit & Srinivasa Rao,
Thank you for your suggestions.
Amit I am unable to send the .mdf file because its company policy. Tell me how to do the job if you have any ideas?
regards
Ramaa
-
Hi,
Ok Then you can follow these steps and confirm to me it worked or not.
1) Start SQL Server in single user mode.
2) Go to your control panel and services.
3) Stop SQL Server
4) Add the -m switch in the parameters pane below.
5) Start SQL Server
6) Run sp_resetstatus with the @dbname parameter. (ie : sp_resetstatus @dbname = "pubs")
7) Perform detailed DBCC checks (CHECKDB, CHECKALLOC, etc)
Run a few random queries to see if you experience any problems.
If no problems occur, stop and start SQL Server and open the database to production.
As an absolute last resort, you can place your database in emergency mode. By placing it in this mode, you will be allowed to copy data out of the database, even if the data is corrupt. To place your database in emergency mode, use the following command:
SP_CONFIGURE 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
UPDATE master..sysdatabases set status = -32768 WHERE name = 'pubs'
GO
SP_CONFIGURE 'allow updates', 0
RECONFIGURE WITH OVERRIDE
You can then BCP data out and place it into a different database.
Thanks,
-
Hai Amit,
Thank you friend for your explanation.
My database is not yet attached.How can i run sp_resetstatus on the database? I am newbie to sql server pls....What ever you gave the explanation for the database in suspect mode am i right?
regards
Ramaa
-
Hi Rama,
As you said that you are New So, You can't do it.
if you provide the mdf file to me than i sured to you i will attach it.
Regards,
Amit Gupta.
-
At this point, I think you are going to have to restore from your backups. (You DO have backups, don't you?) It looks like one or more of your databases were badly corrupted and is unrecoverable. Another thought, did you have .ndf files for those databases? Those are secondary files for SQL Server databases and have to be restored with the .mdf.
-SQLBill
-
Also, yes you are correct that Amit's directions are for a database that is attached and in suspect mode. So it won't work for you until you get your database attached.
-SQLBill
-
Try:
DBCC rebuild_log('<database name>','')
If the rebuilt action is good you need to run DBCC CHECKDB afterwards and possibly take your data out of that DB!! (DTS or bcp come in handy)
Regards,
* Noel -
Hi Amit,
Sorry i am unable to reply you i was busy with some other work. Finally i got the backup from my sys admin and i restored the db successfully. Thank you for offering help and thank you Noel.I will do some R&D with all suggestions.
Ramaa
-
Fortunately, seems to have hapy end.... here.
But,.... i have some questions...
Under which circumnstances you SQL Server was corrupted?
How did u realise that all the DBs cannot be accessed?
The tools provided by SQL Server for restoring / rebuilding didnt provide any help?
Why you choose the solution of "Re-Installation"?
------------
When you 've got a hammer, everything starts to look like a nail... -
Hi Andreas,
In EM i found some of the databases are in suspect mode. Then i did some sp_resetstatus and checkdb. After restarting the server, i am unable to start the server by giving the error like 17207 unable to access the server.Then i posted the problem in forums. I got some information from our friends but no use.I have to rush to up the server.. no way.I re-installed the sql 7.
Ramaa
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply