Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.sql

Migrate SQL 2000 to New SQL 2005 Installation.

Hi all, in my current environment, I have a single server SQL 2000 setup that's being replaced. I'm in the process of installing a new SQL 2005 cluster with the thought of taking advantage of 2005 mirroring and clustering, but have a few questions.

1. My thinking is that I can migrate my 2000 databases to 2005, but leave the databases in 8.0 (2000) mode. Are there any issues with this? I know they won't take advantage of the 2005 performance boost

2. Some of the apps don't support 2005 yet so I need to leave them in 2000 mode until they do. Is SQL 2005 fully backwards compatible with a SQL 2000 database?

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode?

Thanks for any assistance with this!

Start by running the SQL Server 2005 upgrade advisor against your SQL 2000 installation. This will give you good information on problems that you might need to address. Also, if your applications are purchased, check with the vendors for advice on compatibility. But the bottom line is that every application is unique and the only way you'll know for sure is by carefully testing.

Sorry, I don't know the answer to your question on mirroring.

Paul

|||Thanks for the response Paul, Doesn't the upgrade advisor only tell me info in regards to actually upgrading the database? I'm looking at keeping them at 8.0 compatibility on SQL 2005. Basically, I'm looking at keeping the databases at the SQL2000 level on a SQL2005 box. I guess my real question is: Are databases that are running in SQL2000 compatibility mode on a SQL2005 box fully backwards compatible?|||

Most applications will upgrade with no problems but there are a small number of breaking changes in 9.0, even if you stay in 8.0 compat mode. Look up "compatibility" and "sp_dbcmptlevel" in BOL for details.

|||

hi Tynman

Yes u can always keep SQL2000 database compatibility level i.e. 80 in sql 2005 by specifying compatibility level in system stored procedure "sp_dbcmptlevel" and give compatibility level as 80 with database name.

Ur command will be: sp_dbcmplevel dbname, 80

|||

BUMP!

3. Will mirroring work on SQL 2005 with a database that is still in 2000 mode (compatibility level 80)?

Do anyone have an answer to this question?

|||I am trying to restore a SQL 2005 DB into SQL 2000 and it errors out "too many objects on 64 allowed......blah..blah..blah.." something to that effect.

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

Microsoft.SqlServer.Management.Smo Backup and Restore classes

Hi,

Hopefully someone out there will be able to help me with this question. I'm trying to setup my C# program to use the Backup and Restore classes to perform backups and restores on an SQL Express 2005 database.

From the documentation I've read... it seems that when you go to restore the database, it's a good idea to backup the current transaction log. But when I do this using the Backup.SqlBackup method I get an error saying that no database backup is detected so a log backup can't occur. I'm not sure if this has something to do with the fact that I moved my backup files from the default SQL Express/Server Backup folder to a different location. Shouldn't it know I've already performed a backup though?

And does anyone know how to set a parameter to backup to a different file location than the default? I've been doing a backup then a file move. Works the same, but it would be nice to have everything together in the Backup object.

Thanks!

Without full backup transaction log backup is useless. After processing full backup database is marked, so server allows log backups. No any search of backup files, of course.

create database [dummy];
go

-- here will be an error because there is no full backup
backup log [dummy] to disk = 'delete.it.bak'
go

-- this works fine
backup database [dummy] to disk = 'delete.it.bak'
exec xp_cmdshell 'move d:\databases\mssql.1\mssql\backup\delete.it.bak d:\databases\mssql.1\mssql\backup\dont.delete.it.bak'
backup log [dummy] to disk = 'delete.it.bak'
go

drop database [dummy]

To backup file in nondefault directory you should simply specify full path in backup device name. But make sure that SQL Server service account have access to such dir.

WBR, Evergray
--
Words mean nothing...

|||I want to backup to nondefault directory, so I tried

USE master
EXEC sp_addumpdevice 'disk', 'testing_Backup',
'C:\testing.Bak'

BACKUP DATABASE testDB TO testing_Backup

and received the following error message:

Cannot open backup device 'testing_Backup(C:\testing.Bak)'. Operating system error 5(Access is denied.).

I suppose this is due to insufficient access right of SQL Server service account, but what shall I do with it?

|||Windows Vista doesn't allow you to write in root (c:\).
Try it on d:\

Microsoft SQL-DMO (ODBC SQLState: 42000) Error 22022

Hi,
We have recently setup a new cluster server. At the moment
the resources have swapped over so that 1 is running on
node 2 and vice-versa.
However this appears to be causing a problem with the SQL
scheduled tasks. All tasks in the list have (Date and
Time are not available) under 'Next Run Date.' All the
tasks will try and run at their scheduled time however if
we try and manually execute tasks we get the error message
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 22022:
SqlServerAgent Error: Job ... does not exist in the job
cache.
Has anyone seen this error before?
We do not have this problem on our other clustered servers.
Thanks in advance for your help.
Do you have SQL Server Agent starting with a domain account that is valid
on both nodes?
Are there any messages in the SQL Server or SQL Agent logs?
Are there any messages in the event logs?
Do the jobs refer to any files or other objects that only exist on one node?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Microsoft SQL Server Setup Support Files (English) -- Installation failed

I'm trying in vain to upgrade sql server 2000 to 2005 with great headache and pain.

I (finally) have removed all Beta, and other incompatible version of .NET, Visual Studio and any other offending components, and made it to the first page of the install shield.

The .NET 2.0 components installed fine - but I get a fatal error trying to install the SQL Server Set Support Files.

I do have a log file - and have tired searching on the various error codes in it, but am coming up blank. Please advise - this is taking forever!

sample of error msgs in log file:

MSI (s) (B0:E0) [12:33:06:613]: Note: 1: 1935 2: 3: 0x8002802F 4: 5: CreateAssemblyNameObject 6: Microsoft.NetEnterpriseServers.ExceptionMessageBox,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0"
MSI (s) (B0:E0) [12:33:06:613]: Product: Microsoft SQL Server Setup Support Files (English) -- Error 2908. The installer has encountered an unexpected error. The error code is 2908. Could not register component {1F3316BE-825B-4390-A9D2-AF3EECCAE9F6}.

MSI (s) (B0:E0) [12:33:06:613]: Executing op: ComponentRegister(ComponentId={52723CBD-3400-458C-988A-4D012E6CAEDB},KeyPath=<\Microsoft.SqlServer.WizardFrameworkLite,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0",State=3,,Disk=1,SharedDllRefCount=0,BinaryType=0)
Error 2908. The installer has encountered an unexpected error. The error code is 2908. Could not register component {1F3316BE-825B-4390-A9D2-AF3EECCAE9F6}.
MSI (s) (B0:E0) [12:33:06:629]: MSCOREE not loaded loading copy from system32
MSI (s) (B0:E0) [12:33:06:629]: Assembly Error:Function not defined in specified DLL.

MSI (s) (B0:E0) [12:33:06:629]: Note: 1: 1935 2: {52723CBD-3400-458C-988A-4D012E6CAEDB} 3: 0x8002802F 4: 5: CreateAssemblyNameObject 6: Microsoft.SqlServer.WizardFrameworkLite,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0"
MSI (s) (B0:E0) [12:33:06:629]: Product: Microsoft SQL Server Setup Support Files (English) -- Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.WizardFrameworkLite,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x8002802F. assembly interface: , function: CreateAssemblyNameObject, component: {52723CBD-3400-458C-988A-4D012E6CAEDB}

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.WizardFrameworkLite,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x8002802F. assembly interface: , function: CreateAssemblyNameObject, component: {52723CBD-3400-458C-988A-4D012E6CAEDB}
MSI (s) (B0:E0) [12:33:06:629]: User policy value 'DisableRollback' is 0
MSI (s) (B0:E0) [12:33:06:629]: Machine policy value 'DisableRollback' is 0
Action ended 12:33:06: InstallFinalize. Return value 3.

mandpenny

Please read this blog and see if it helps with the problem.

http://blogs/jdbaker/archive/2006/04/20/10684.aspx

|||

Thanks for the reply Raja, but I can't get to that URL - pg. cannot be displayed error...

Mandy

|||

I had a similar problem - removing the beta version of SQL Native Client (which came with the 2005 CTP) fixed it

Dave

Microsoft SQL Server Setup Support Files (English) -- Installation failed

I'm trying in vain to upgrade sql server 2000 to 2005 with great headache and pain.

I (finally) have removed all Beta, and other incompatible version of .NET, Visual Studio and any other offending components, and made it to the first page of the install shield.

The .NET 2.0 components installed fine - but I get a fatal error trying to install the SQL Server Set Support Files.

I do have a log file - and have tired searching on the various error codes in it, but am coming up blank. Please advise - this is taking forever!

sample of error msgs in log file:

MSI (s) (B0:E0) [12:33:06:613]: Note: 1: 1935 2: 3: 0x8002802F 4: 5: CreateAssemblyNameObject 6: Microsoft.NetEnterpriseServers.ExceptionMessageBox,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0"
MSI (s) (B0:E0) [12:33:06:613]: Product: Microsoft SQL Server Setup Support Files (English) -- Error 2908. The installer has encountered an unexpected error. The error code is 2908. Could not register component {1F3316BE-825B-4390-A9D2-AF3EECCAE9F6}.

MSI (s) (B0:E0) [12:33:06:613]: Executing op: ComponentRegister(ComponentId={52723CBD-3400-458C-988A-4D012E6CAEDB},KeyPath=<\Microsoft.SqlServer.WizardFrameworkLite,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0",State=3,,Disk=1,SharedDllRefCount=0,BinaryType=0)
Error 2908. The installer has encountered an unexpected error. The error code is 2908. Could not register component {1F3316BE-825B-4390-A9D2-AF3EECCAE9F6}.
MSI (s) (B0:E0) [12:33:06:629]: MSCOREE not loaded loading copy from system32
MSI (s) (B0:E0) [12:33:06:629]: Assembly Error:Function not defined in specified DLL.

MSI (s) (B0:E0) [12:33:06:629]: Note: 1: 1935 2: {52723CBD-3400-458C-988A-4D012E6CAEDB} 3: 0x8002802F 4: 5: CreateAssemblyNameObject 6: Microsoft.SqlServer.WizardFrameworkLite,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0"
MSI (s) (B0:E0) [12:33:06:629]: Product: Microsoft SQL Server Setup Support Files (English) -- Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.WizardFrameworkLite,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x8002802F. assembly interface: , function: CreateAssemblyNameObject, component: {52723CBD-3400-458C-988A-4D012E6CAEDB}

Error 1935. An error occurred during the installation of assembly 'Microsoft.SqlServer.WizardFrameworkLite,Version="9.0.242.0",processorArchitecture="MSIL",Culture="neutral",PublicKeyToken="89845dcd8080cc91",FileVersion="9.0.1399.0"'. Please refer to Help and Support for more information. HRESULT: 0x8002802F. assembly interface: , function: CreateAssemblyNameObject, component: {52723CBD-3400-458C-988A-4D012E6CAEDB}
MSI (s) (B0:E0) [12:33:06:629]: User policy value 'DisableRollback' is 0
MSI (s) (B0:E0) [12:33:06:629]: Machine policy value 'DisableRollback' is 0
Action ended 12:33:06: InstallFinalize. Return value 3.

mandpenny

Please read this blog and see if it helps with the problem.

http://blogs/jdbaker/archive/2006/04/20/10684.aspx

|||

Thanks for the reply Raja, but I can't get to that URL - pg. cannot be displayed error...

Mandy

|||

I had a similar problem - removing the beta version of SQL Native Client (which came with the 2005 CTP) fixed it

Dave