sp_attach_single_file_db causes error 1813

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

sp_attach_single_file_db causes error 1813

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-04 : 09:29:16

Wiktor Torq writes "of course if I would like to be sure to connect database file correctly, I should properly dettach it first with sp_dettach_db.
but I have an unexpected situation, a strange sqlserver crash (it's our customer's MSSQLserver to be precise).

now when I try to attach MDF file directly:

sp_attach_single_file_db @dbname='dbSQL',
@physname='C:\Program Files\Microsoft SQL Server\MSSQL\Data\db.mdf'

server claims:

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'dbSQL'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'C:\MSSQL7\data\dbSQL_log.LDF' may be incorrect.

I know - customer SHOULD have made a backup copy but ... heh ... you know
your customers ....

some tech info: customer machine used MSDE 1.0. I've tried it on MSDE 1.0
and SQL 2000 already and still cannot solve this problem.

my question is:
is it possible to somehow attach this MDF file to any working SQL Server (then I could be able to make a backup copy and restore it whereever I would
like to)? the LDF file is unavaliable.

any help would be appreciated."

izaltsman
A custom title

1139 Posts

Posted - 2002-01-04 : 10:00:30

Well, it isn't gonna be easy...

Create a new database using original filenames, stop the server, overwrite the new MDF file with the original MDF, and re-start the server. The database will appear as corrupt and you will not be able to access the data. Then you'll have to place the database into emergency mode:



Sp_configure 'allow updates', 1 

Reconfigure with override 

GO 

Update sysdatabases set status = -32768 where name = 'yourdb' 

Sp_configure 'allow updates', 0 

Reconfigure with override 

GO

Once it is in emergency mode, you will be able to BCP or DTS the data out into a new database. And tell them to start doing backups on a regular basis from now on!!!

Edited by - izaltsman on 01/04/2002 10:00:49

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources