Msg 601 AND Msg 1813 on Attach Database...

Hey All,

I'm trying to use OSQL to attach an MDF and LDF file to my MSDE database. The files were originally detached from an SQL Server 7.0 (also MSDE) service and I'm trying to attach them to a computer running MSDE (SQL Server 2k). When I run sp_attach_db I get the following errors:

msg 601, Level 12, State 3, server IQCRAFT04, Line 1
could not continue scan with NOLOCK due to data movement
msg 1813, Level 16, State 3, server IQCRAFT04, Line 1
could not open new database 'M3SQL'. CREATE DATABASE is aborted.
converting database 'M3SQL' from version 534 to the current version 539.
database 'M3SQL' running the upgrade step fropm version 534 to version 535.

Any idea what this means?
I cannot attach these database files to the SQL Server 7.0 database either.

Thanks!

--BenAddendum:

When I try to attach the database to the original SQL 7 computer, I get the following error messge:

Msg 906, Level 22, State 2, Server NIMITZ, Line 1
Could not locate row in sysobjects for system catalog 'Sysobjects' in database 'M3SQL'. This system catalog should exist in all databases. Run DBCC CHECKTABLE on sysobjects in this database.

Thanks,

-Ben|||Why don't you post the code you're executing...|||Sorry, good call.

Here:

sp_attach_db @.dbname = N'M3SQL', @.filename1 = N'C:\MyData\M3SQL.mdf', @.filename2 = N'C:\MyData\M3SQL.ldf'

Thanks,

Ben|||detached from an SQL Server 7.0 (also MSDE) service and I'm trying to attach them to a computer running MSDE (SQL Server 2k).

Sorry just got that...can you do that?

Also BOL say you can only attach a db if it was previously dettached..

why not try and do a restore...

Also where's the dmp or bak file located?

Can you do RESTORE FILELISTONLY on it to confirm the internall file info?|||Hey Brett,

I'm not terribly familiar with SQL Server, so I may be making mistakes I'm not allowed to make but.

1) You can attach a 7.0 database to 2000. In fact, I can do it with an much older version of this database that I detached and backed up.

2) I was under the impression that I restore could only be done if you had previously done an official 'backup' command, which I didn't.

3) I don't know what a dmp or bak file is or where I would find them.

4) I'll try the RESTORE FILELISTONLY command, but I was unaware of it until you mentioned it. Thanks for the tip!

--Ben|||You can attach a SQL 7.0 DB to a SQL 2K instance. I don't think that is the problem here. As a rule of thumb, whenever SQL Server asks for DBCC CHECKTABLE to be run on sysobjects, it is too late. I am afraid you will have to go back to the last good backup of this database. There are ways of getting a DB attached with no log, but sysobjects is firmly in the data portion of the database.|||MCrowley,

I was reaching that conclusion myself. Thankfully this is a dev environment which means no crucial data was lost. What I am upset about losing is my data structure. Any way I can extract that from the files?

Thanks,

Ben|||P.S. -

Hey Guys,

Besides backing up, what regular maintenance should I perform on my database to prevent this sort of thing in the future?

Please bear in mind I don't have Ent. Man. so every I do has to be from the OSQL command line. :)

Thanks,

Ben|||[blidfolded, loaded gun, very dark room]
Last shot...create a db on MSDE called M3SQL

Dettach it

Then try and attach with your code
[/blidfolded, loaded gun, very dark room]|||No luck, thanks anyways though. I've already started re-building from my old copy. Thanks guys for all of the suggestions!

--Ben|||As far as the pro-active maintenance, I generally try to run DBCC checkdb once a week for each DB. Generally on weekends, when there is less traffic.|||Originally posted by benfinkel
P.S. -

Hey Guys,

Besides backing up, what regular maintenance should I perform on my database to prevent this sort of thing in the future?

Please bear in mind I don't have Ent. Man. so every I do has to be from the OSQL command line. :)

Thanks,

Ben

How come yuo don't have the client tools?

I mean, I may be a command line junkie...but that's pushing it...|||Brett,

Believe me, I wish I did.

The Command Tools only come with SQL Server. We don't own SQL Server, instead we're using MSDE.

MSDE is a free version of the SQL Server engine that comes with MS Office and Visual Studio. Access can be used as a front-end command tool, but I'm just learning how to use it as such. In the meantime, when I search for directions on the internet they describe how to do it in either EM or command-line. Not too many examples of how to accomplish these things in Access. It seems that many of these things can't be accomplished in access.

--Ben|||Not yet...hold on...

OK Try this:

http://popularshareware.com/myLittleAdmin-for-MS-SQL-and-MSDE-download-7283.html

And then there's this:

http://www.databasejournal.com/features/mssql/article.php/10894_1494301_2|||Brett,

I love you. Will you marry me?

We'll have to go to Vermont, but it'll work.|||Hey...cut that out...

Besides, I'm spoken for 8-)

GOOD LUCK dude!