Moving a DB from SQL 2005 to SQL 2000

Hello all,
This anoying me!!! I'm trying to copy one of our DB's to SQL 2000. In
SSMS, on the SQL 2005 server, I change the compatibility level for the DB to
SQL Server 2000(80). Then I detach the DB from SQL 2005, copy files over to
the SQL 2000 server and try to reatach the DB on that server. I keep
getting the following error:
TITLE: Microsoft SQL Server Management Studio
Attach database failed for Server 'MPS-DC01'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Attach+database+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Could not find row in sysindexes for database ID 8, object ID 1, index ID 1.
Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'xxxxxxxxxxxx'. CREATE DATABASE is aborted.
(Microsoft SQL Server, Error: 602)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=602&LinkId=20476[/url]
BUTTONS:
OK
Why will this not work? I look at the system tables in the DB on the 2005
server before detaching and the sysindexes table does not exist. There is
only one single table in the system tables and that is sysdiagrams.
What is the point of having a compatibility level if they are not
compatible?
Thanks for any help anyone can provide,
Conan Kelly
Conan,
The SQL2005 database physical format is not compatible with SQL 2000.
You will need to generate scripts to create the database and all the objects
in it. Right click on your 2005 database and:
Script Database as
Create to
Then again:
Tasks
Generate Scripts...
Once you have created the database and necessary objects
Tasks
Export Data ...
Be sure that you set scripting options to SQL Server 2000. In SP2 (at
least) you can set the scripting to be in SQL Server 2000 mode.
RLF
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:ms%7i.87779$p47.73245@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> This anoying me!!! I'm trying to copy one of our DB's to SQL 2000. In
> SSMS, on the SQL 2005 server, I change the compatibility level for the DB
> to SQL Server 2000(80). Then I detach the DB from SQL 2005, copy files
> over to the SQL 2000 server and try to reatach the DB on that server. I
> keep getting the following error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Attach database failed for Server 'MPS-DC01'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Attach+database+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Could not find row in sysindexes for database ID 8, object ID 1, index ID
> 1. Run DBCC CHECKTABLE on sysindexes.
> Could not open new database 'xxxxxxxxxxxx'. CREATE DATABASE is aborted.
> (Microsoft SQL Server, Error: 602)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLS erver&EvtID=602&LinkId=20476[/url]
> --
> BUTTONS:
> OK
> --
> Why will this not work? I look at the system tables in the DB on the 2005
> server before detaching and the sysindexes table does not exist. There is
> only one single table in the system tables and that is sysdiagrams.
> What is the point of having a compatibility level if they are not
> compatible?
> Thanks for any help anyone can provide,
> Conan Kelly
>
|||Russell,
Thank you for the feed back.
So even if you have the "Compatibility Level" in the DB's properties set to
SQL Server 2000 (80), one can't "detach-copy files-attach" from 2005 to
2000? What's the point of Compatibility Level then?
Thanks again for all of your help,
Conan
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OzEWJcJpHHA.1776@.TK2MSFTNGP05.phx.gbl...
> Conan,
> The SQL2005 database physical format is not compatible with SQL 2000.
> You will need to generate scripts to create the database and all the
> objects in it. Right click on your 2005 database and:
> Script Database as
> Create to
> Then again:
> Tasks
> Generate Scripts...
> Once you have created the database and necessary objects
> Tasks
> Export Data ...
> Be sure that you set scripting options to SQL Server 2000. In SP2 (at
> least) you can set the scripting to be in SQL Server 2000 mode.
> RLF
> "Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
> news:ms%7i.87779$p47.73245@.bgtnsc04-news.ops.worldnet.att.net...
>
sql

move log file to diff location

Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
Please don't multi-post. See my answer in .clustering.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23lg8C4luHHA.1768@.TK2MSFTNGP04.phx.gbl...
Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.

move log file to diff location

Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.Please don't multi-post. See my answer in .clustering.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23lg8C4luHHA.1768@.TK2MSFTNGP04.phx.gbl...
Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.

move log file to diff location

Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.Please don't multi-post. See my answer in .clustering.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23lg8C4luHHA.1768@.TK2MSFTNGP04.phx.gbl...
Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.