error attaching single db .mdf file

Hi,
The dev deleted some of the ldf files mistakenly and are unable to attach the mdf file.
How can i re-create the db with the existing mdf file and a new ldf file. Can someone suggest.
Thanks,
Arzan
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:8A81A4E5-F699-4552-A0C0-8310527A7EA5@.microsoft.com...
> Hi,
> The dev deleted some of the ldf files mistakenly and are unable to attach
the mdf file.
> How can i re-create the db with the existing mdf file and a new ldf file.
Can someone suggest.
> Thanks,
> Arzan
|||Hi,
Try using the procedure "sp_attach_single_file_db" to attach the database
with specifying the MDF file only.
EXEC sp_attach_single_file_db 'dbname', 'c:\mssql\data\dbname.mdf'
THis will create a new database with exiting mdf and new ldf file.
This command will fail in below scenarios:-
1. If the database is not detached using sp_detach_db
2. If you have multiple LDF files in your old database.
If your sp_attach_single_file_db command fails then probaly you have to
restore the database from good backup available.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:8A81A4E5-F699-4552-A0C0-8310527A7EA5@.microsoft.com...
> Hi,
> The dev deleted some of the ldf files mistakenly and are unable to attach
the mdf file.
> How can i re-create the db with the existing mdf file and a new ldf file.
Can someone suggest.
> Thanks,
> Arzan
|||Thanks Hari, the problem has been resolved.
"Hari Prasad" wrote:

> Hi,
> Try using the procedure "sp_attach_single_file_db" to attach the database
> with specifying the MDF file only.
> EXEC sp_attach_single_file_db 'dbname', 'c:\mssql\data\dbname.mdf'
> THis will create a new database with exiting mdf and new ldf file.
> This command will fail in below scenarios:-
> 1. If the database is not detached using sp_detach_db
> 2. If you have multiple LDF files in your old database.
> If your sp_attach_single_file_db command fails then probaly you have to
> restore the database from good backup available.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:8A81A4E5-F699-4552-A0C0-8310527A7EA5@.microsoft.com...
> the mdf file.
> Can someone suggest.
>
>

error attaching single db .mdf file

Hi,
The dev deleted some of the ldf files mistakenly and are unable to attach th
e mdf file.
How can i re-create the db with the existing mdf file and a new ldf file. Ca
n someone suggest.
Thanks,
Arzanhttp://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:8A81A4E5-F699-4552-A0C0-8310527A7EA5@.microsoft.com...
> Hi,
> The dev deleted some of the ldf files mistakenly and are unable to attach
the mdf file.
> How can i re-create the db with the existing mdf file and a new ldf file.
Can someone suggest.
> Thanks,
> Arzan|||Hi,
Try using the procedure "sp_attach_single_file_db" to attach the database
with specifying the MDF file only.
EXEC sp_attach_single_file_db 'dbname', 'c:\mssql\data\dbname.mdf'
THis will create a new database with exiting mdf and new ldf file.
This command will fail in below scenarios:-
1. If the database is not detached using sp_detach_db
2. If you have multiple LDF files in your old database.
If your sp_attach_single_file_db command fails then probaly you have to
restore the database from good backup available.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:8A81A4E5-F699-4552-A0C0-8310527A7EA5@.microsoft.com...
> Hi,
> The dev deleted some of the ldf files mistakenly and are unable to attach
the mdf file.
> How can i re-create the db with the existing mdf file and a new ldf file.
Can someone suggest.
> Thanks,
> Arzan|||Thanks Hari, the problem has been resolved.
"Hari Prasad" wrote:

> Hi,
> Try using the procedure "sp_attach_single_file_db" to attach the database
> with specifying the MDF file only.
> EXEC sp_attach_single_file_db 'dbname', 'c:\mssql\data\dbname.mdf'
> THis will create a new database with exiting mdf and new ldf file.
> This command will fail in below scenarios:-
> 1. If the database is not detached using sp_detach_db
> 2. If you have multiple LDF files in your old database.
> If your sp_attach_single_file_db command fails then probaly you have to
> restore the database from good backup available.
> Thanks
> Hari
> MCDBA
>
> "Arzan" <Arzan@.discussions.microsoft.com> wrote in message
> news:8A81A4E5-F699-4552-A0C0-8310527A7EA5@.microsoft.com...
> the mdf file.
> Can someone suggest.
>
>

error attaching single db .mdf file

Hi,
The dev deleted some of the ldf files mistakenly and are unable to attach the mdf file.
How can i re-create the db with the existing mdf file and a new ldf file. Can someone suggest.
Thanks,
Arzanhttp://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:8A81A4E5-F699-4552-A0C0-8310527A7EA5@.microsoft.com...
> Hi,
> The dev deleted some of the ldf files mistakenly and are unable to attach
the mdf file.
> How can i re-create the db with the existing mdf file and a new ldf file.
Can someone suggest.
> Thanks,
> Arzan|||Hi,
Try using the procedure "sp_attach_single_file_db" to attach the database
with specifying the MDF file only.
EXEC sp_attach_single_file_db 'dbname', 'c:\mssql\data\dbname.mdf'
THis will create a new database with exiting mdf and new ldf file.
This command will fail in below scenarios:-
1. If the database is not detached using sp_detach_db
2. If you have multiple LDF files in your old database.
If your sp_attach_single_file_db command fails then probaly you have to
restore the database from good backup available.
Thanks
Hari
MCDBA
"Arzan" <Arzan@.discussions.microsoft.com> wrote in message
news:8A81A4E5-F699-4552-A0C0-8310527A7EA5@.microsoft.com...
> Hi,
> The dev deleted some of the ldf files mistakenly and are unable to attach
the mdf file.
> How can i re-create the db with the existing mdf file and a new ldf file.
Can someone suggest.
> Thanks,
> Arzan

error attaching db...

Hello,

I tried to attach my database (SQL Server 2000 w/ SP3) at work after taking it home to work on it and it gave me an error.

I tried to attaching my backup one as one (which use to work) and now does not. I treid to attach other DB's and they are fine.

The error message displayed on the screen is:

----
Microsoft SQL-DMO (ODBC SQLState:HY000)

X Error 3264
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 51
Process ID: 1004
----

The error message recorded in Event Viewer is:

------
Event ID: 17055

17066 :
SQL Server Assertion: File: <recbase.cpp>, line=1374
Failed Assertion = 'm_nVars > 0'.

------

Any ideas what could have gone wrong? or is it simply a matter of reinstalling SQL server.

thanks,

JPCheck this SQLMAG forum link (http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4702) for information.

HTH|||Originally posted by Satya
Check this SQLMAG forum link (http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4702) for information.

HTH

Hey satya,

thanks for the heads up. I don't understand why it should be a SP issue because both of them are the same and it use to work before. Last week, I did the same... detach the DB, worked on it at home and rettached it back at work... no problem.

Oh well, such is life ... full of disappointments. I'll reintall the SP and see what happens. Maybe I'll do a windows update as well cuz we never know if it does affect anything else.

I'll report back should I get the problem fixed.

thanks,

JP|||Originally posted by globeuser
Hey satya,

thanks for the heads up. I don't understand why it should be a SP issue because both of them are the same and it use to work before. Last week, I did the same... detach the DB, worked on it at home and rettached it back at work... no problem.

Oh well, such is life ... full of disappointments. I'll reintall the SP and see what happens. Maybe I'll do a windows update as well cuz we never know if it does affect anything else.

I'll report back should I get the problem fixed.

thanks,

JP

Nope.. same error result. did not work. I've reapplied SP3 and unfortunately I'm still getting this error.

One factor though... I ftp'd down the DB instead of making a copy on the CD but I've downloaded them a number of times and still the same error appears.

any other ideas anyone?

kinda desperate ;-) Guess I'll have to reinstall?

JP|||Originally posted by Satya
Check this SQLMAG forum link (http://www.sqlmag.com/Forums/messageview.cfm?catid=5&threadid=4702) for information.

HTH

Yup... looks like a reinstall is the only option because I am now getting another error message:

--------

Microsoft SQL-DMO (ODBC SQLState:42000)

X Error 1813: Cound not opne new database 'MyProject'. CREATE DATABASE is aborterd.
SQlDumpExceptionHandler: Process 51 genrated fatal exception c0000005 EXCEPTION_ACCESS_violation
Server is terminating this process.

--------

any other ideas?

JP|||Last resort to contact MS PSS for any fix on this EAV issues.|||Originally posted by Satya
Last resort to contact MS PSS for any fix on this EAV issues.

I had a hunch that it might just be as simple s this but I remembered that when I tried to detach it, it took some time because there were stilll some connections to it and I "forced it" to disconnect. It should not take that long because I have a very fast machine.

Lucky for me that the database was still there and I reattached it again first thing before any connections or apps have access to it.

The detachment is now clean and was able to attach it now to another machine.

The lesson learned is that the detach process was dirty or forced and all I had to do was to do it again before any other applications has a lock on it.

JP|||Thats fine, but before you detach make sure to run health checks and DBCC against the database to make sure its lively.|||Originally posted by Satya
Thats fine, but before you detach make sure to run health checks and DBCC against the database to make sure its lively.

Sorry, but how do you do that? I noticed that when I tried to run one of my stored procedures or view the properties in the DB, i get an error displayed...

And when I tried to delete the stored procedure to recreate it won't allow me to or can't delete it!

------
Microsoft SQL-DMO (ODBC SQLState:HY000)

X Error 605: Attempt to to fetch logical page (1:0) in database 'MyProject' belongs to object 'ALLOCATION', not to object 'syscomments'.
------

I am running ColdFusion 5 and this was what was displayed.

------
ODBC Error Code = S1000 (General error)

[Microsoft][ODBC SQL Server Driver][SQL Server]Attempt to fetch logical page (1:0) in database 'MyProject' belongs to object 'ALLOCATION', not to object 'syscomments'.
------

As I said, the SP works in my other machine and only when I move it over to this one that the error pops up.

ideas anyone?

JP|||This error occurs when SQL detcts any database corruption and to determine the full extent of the corruption, execute DBCC CHECKDB as soon as possible. Also check the error log for other errors, which often accompany a 605 error.

For more information on CHECKDB refer to books online.

My suggestion is to keep database in DBO Use only until you resolve the corruption on the database, do not allow any other process to deal with until this gets fixed.

HTH|||Originally posted by Satya
This error occurs when SQL detcts any database corruption and to determine the full extent of the corruption, execute DBCC CHECKDB as soon as possible. Also check the error log for other errors, which often accompany a 605 error.

For more information on CHECKDB refer to books online.

My suggestion is to keep database in DBO Use only until you resolve the corruption on the database, do not allow any other process to deal with until this gets fixed.

HTH

I tried correcting the db is single-user mode but when i tried to execute

dbcc checkdb ('MyProject', REPAIR_ALLOW_DATA_LOSS )

it notifies me that I should be in single-user mode which I already am and won't continue.!

perhaps I should just reinstall SQL... good thing I keep a script of table creation and sp procedures.

JP|||Make sure you haven't opened or accessed same DB in EM and QA.
Its not necessarily to have DB in singel-user mode when running DBCC CHECKDB, you can kill any other process which is involved to access during this execution.|||Originally posted by Satya
Make sure you haven't opened or accessed same DB in EM and QA.
Its not necessarily to have DB in singel-user mode when running DBCC CHECKDB, you can kill any other process which is involved to access during this execution.

I'm sure i haven't got any connections to it. I just finished installing it and still same errors... *sigh*|||I downloaded the same DB from another machine and to my surprise this was not corrupt at all. Perhaps its the target machine where I uploaded it to that caused the error. There are so many factors that I would not be surprised a simple thing such as these may be the cause in the first place.

thanks for the checkdb info.

JP|||If the problem persists from Database there is no use if SQL Server is reinstalled. Try to fix the corruption on the database level.

As you've scripts for the database, export important data using DTS to a text file and recreate the DB using scripts and import using DTS, it may work.

Meanwhile have a sneak thru SQL Error log for any information.

All the best.|||the good thing is that my main development machine is at my home office and the test box is here at work.

I'm not too concerned about the data for now because I'm still writing the program but I have tried to fix the corruption at the db level but quite honestly, the error log doesn't tell me anything more than what was displayed when I ran the cehckdb script anyway and even if I tried, i can't get it to fix it.

JP|||For the database corruption its suggested to use DBCC CHECKDB and check for event viewer also for any hardware issues or harddisk problems.|||I understand. I did mention it in my original post along with what the event viewer recorded.

anyway, when i have some time, I'll investigate further.

thanks,

JP

Error attaching database file

When I tried to attach a database it failed with:
Error 3624:
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 55
Process ID: 952
Some ideas to recover the file?
Marco,
Could be a corrupt file.
Did you perform a DBCC CHECKDB prior to detaching? Did you detach the
database before or...? What method are you using to attach i.e.,
sp_attach_db or sp_attach_single_file_db? Do you have admin permissions to
do this?
HTH
Jerry
"Marco Frontini" <iunknown@.libero.it> wrote in message
news:es5ZY5QyFHA.2652@.TK2MSFTNGP14.phx.gbl...
> When I tried to attach a database it failed with:
> Error 3624:
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 55
> Process ID: 952
> Some ideas to recover the file?
>

Error attaching database file

When I tried to attach a database it failed with:
Error 3624:
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 55
Process ID: 952
Some ideas to recover the file?Marco,
Could be a corrupt file.
Did you perform a DBCC CHECKDB prior to detaching? Did you detach the
database before or...? What method are you using to attach i.e.,
sp_attach_db or sp_attach_single_file_db? Do you have admin permissions to
do this?
HTH
Jerry
"Marco Frontini" <iunknown@.libero.it> wrote in message
news:es5ZY5QyFHA.2652@.TK2MSFTNGP14.phx.gbl...
> When I tried to attach a database it failed with:
> Error 3624:
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 55
> Process ID: 952
> Some ideas to recover the file?
>

Error attaching database file

When I tried to attach a database it failed with:
Error 3624:
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 55
Process ID: 952
Some ideas to recover the file?Marco,
Could be a corrupt file.
Did you perform a DBCC CHECKDB prior to detaching? Did you detach the
database before or...? What method are you using to attach i.e.,
sp_attach_db or sp_attach_single_file_db? Do you have admin permissions to
do this?
HTH
Jerry
"Marco Frontini" <iunknown@.libero.it> wrote in message
news:es5ZY5QyFHA.2652@.TK2MSFTNGP14.phx.gbl...
> When I tried to attach a database it failed with:
> Error 3624:
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 55
> Process ID: 952
> Some ideas to recover the file?
>

error attaching adventureworks database and SSIS training database

Hi there,

I got this error when i tried to attach the adventureworks database and SSIS training database.

an exception occurred while executing a transact-sql statement or batch
(microsoft.sqlserver.connectioninfo)

could not find row in sysindexes for database ID 17, object ID 1, index ID 1. Run DBCC
checktable on sysindexes.

I join join a network domain for my computer. Previously it doesnt have this problem.

Hello,

It appears that you are trying to attach a SQL Server 2005 database to an instance of SQL Server 2000 or earlier. Please try to attach the AV database to a SQL Server 2005 instance.

Derrick VanArnam - MSFT