SQL 2000 32-bit to SQL 2005 64-bit

[Former Member]

Hi,

Does someone know if it is supported to <b>attach</b> database from

SQL 2000 32-bit to SQL2005 64-bit ? if it is, are there any activities that should be

done after the attachment (or before it) ?

Dimitry Haritonov

[clas_hortien · Accepted solution]

Hi,

yes it is supported. The database format will be upgraded during the attach, so expect a longer runtime. Ensure that the log of the database is big enough or that it can grow, as some log space is needed during the conversion.

After the attach you shoud update the statistical and storage information on all table by running update statistics and update usage on all tables.

use <SID>

exec sp_updatestats

dbcc updateusage(0)

Best regards

Clas

[Former Member · Accepted solution]

Hi Clas,

Is there something to run after the attchment (I mean something like STM after

attaching the database from sql 2000 to 2005) ? or the attachment and STM is all

that I need to do ?

Dimitry

Message was edited by:

Dimitry Haritonov

[clas_hortien · Accepted solution]

Hi Dimitry,

the STM tools are supported for SQL 2005 as well and should be used. From the database side the STM tool is the only thing you need. But please keep in mind that for some R/3 releases you need special support package level to run on SQL 2005.

Best regards

Clas

[Former Member · Accepted solution]

Hi clas,

thanks for the information. can you link me to a document or kb article that

ensures this ?

Dimitry

[clas_hortien · Accepted solution]

Hi,

what should the kb article ensure ? That the attach is supported ?

http://msdn2.microsoft.com/en-us/library/ms189625.aspx

Best regards

Clas

[Former Member · Accepted solution]

hi,

I want to ensure that the attachment between sql 32-bit and sql 64-bit supported.

Dimitry

[clas_hortien · Accepted solution]

Hi,

maybe this article is the one you are looking for:

http://support.microsoft.com/kb/314546/en-us

I have already attached multiple databases from 32bit to 64bit and vice versa. The database format between 32bit and 64bit is the same for one database version (SQL 2000 or SQL 2005). The upgrade is only possible in one direction, from SQL 2000 to SQL 2005. Once you have attached the DB onto SQL 2005 you cannot attach it back to SQL 2000.

Best regards

Clas

[Former Member · Accepted solution]

thanks a lot.

[Former Member]

****WARNING****

DO NOT drop or just detach and attach the 32bit DB into SQL2005 64bit.

SQL2005 needs basis patches to be applied to SAP BEFORE you detach the DB. Check you have the correct patch levels for your versions of SAP.

You also need to do/use the SMT to complete the migration else you will get various errors like Wrong long datatypes. Perform SQL2005 after upgrade steps

see notes

Note 126973 - SICK messages with MS SQL Server

Note 799058 - Setting Up Microsoft SQL Server 2005

[clas_hortien]

Hello,

i do not agree.

First of all, you do not have to drop the database, as this will delete the database files. You have to detach it, otherwise you will not able to copy it. As here are also inexperienced user reading the threads, you have be check exactly what you are writing.

Furthermore needs SQL Server 2005 no additional patches to attach the database, the SAP system needs these patches to run on SQL Server 2005 successfully. But this is described in the installation or upgrade notes for every release which support packages has to be installed for the upgrade to SQL 2005. Normally these patches have to be installed in the old system before you upgrade to SQL 2005.

Best regards

Clas

[Former Member]

Hi kevin/clas,

I work with BI 7.0 on sql 2000 32-bit while my purpose is to copy it to

sql 2005 64-it. note 799058 says:

If you use SAP NetWeaver 2004s (7.0) or higher, there is an SAPinst option to perform migration steps. Only use this option, if the basis release of your source system is also 7.0.

Do you know what "SAPinst option " should I choose for the migration ?

Dimitry Haritonov

[clas_hortien]

Hi,

note 683447 states, that you have to use the copy option, when you attached a database from SQL 2000 to SQL 2005.

Regards

Clas

[Former Member]

Hi clas,

Copy option in STM tool, I knew that. Is this what the note mentioned ?

Dimitry

[clas_hortien]

Hi,

yes, this is what the note said.

Regards

Clas

[Former Member]

Clas,

I agree, you should read the comments exactly.

The Basis patches are required PRIOR to the move or detach and subsequent re-attachment of the SAP DB. They (The Basis Patches) are not required TO attach the DB - How would you apply Basis patches to a DB that doesn't yet exist?!!!!? (Mind boggles)

Things may get lost in translation - However these types of tasks (with a high level of risk) should only be carried out by competent or certified SAP Basis personnel for this exact reason.

Messup and you lose the system.

[Former Member]

Dimitry

You are on version 7, so copy.

[Former Member]

Dear All,

I am stucked while trying to attach our Production db (MSSQL2K-32bit

SP4) 20 datas and 2 logs to the Sandbox (SQL2K5-64bit SP2).

I received an error saying that the new database cannot be

opened… Can you please give me some suggestion on what could

be wrong?

SQL script:

USE [master]

GO

CREATE DATABASE [SNB] ON

( FILENAME = N’F:\SAPDATA\SNBDATA1\SNBDATA1.mdf’ ),

( FILENAME = N’G:\SAPLOG\SNBLOG1\SNBLOG1.ldf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA10\SNBDATA10.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA11\SNBDATA11.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA12\SNBDATA12.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA13\SNBDATA13.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA14\SNBDATA14.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA15\SNBDATA15.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA16\SNBDATA16.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA17\SNBDATA17.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA18\SNBDATA18.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA19\SNBDATA19.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA2\SNBDATA2.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA20\SNBDATA20.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA3\SNBDATA3.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA4\SNBDATA4.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA5\SNBDATA5.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA6\SNBDATA6.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA7\SNBDATA7.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA8\SNBDATA8.ndf’ ),

( FILENAME = N’F:\SAPDATA\SNBDATA9\SNBDATA9.ndf’ ),

( FILENAME = N’G:\SAPLOG\SNBLOG2\SNBLOG2.ldf’ )

FOR ATTACH

GO

if exists (select name from master.sys.databases sd where name N’SNB’ and SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) EXEC

[SNB].dbo.sp_changedbowner @loginame=N’FORTUNANET\snbadm’,

@map=false

GO

Error message:

Msg 1813, Level 16, State 2, Line 1

Could not open new database ‘SNB’. CREATE DATABASE is

aborted.

Msg 3456, Level 21, State 1, Line 1

Could not redo log record (73574:26706:79), for transaction ID

(0:766408492), on page (1:7487504), database ‘SNB’ (database

ID 5). Page: LSN = (73573:137222:224), type = 1. Log: OpCode 6, context 2, PrevPageLSN: (73574:23272:17). Restore from a

backup of the database, or repair the database.

Msg 3313, Level 21, State 2, Line 1

During redoing of a logged operation in database ‘SNB’, an

error occurred at log record ID (73574:26706:79). Typically, the

specific failure is previously logged as an error in the Windows

Event Log service. Restore the database from a full backup, or

repair the database.

I appreciate your help.

Regards

[Former Member]

Dear basis guy ,

First of all try this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1915440&SiteID=1

and then this:

http://www.kbalertz.com/kbNamed_926493/926493.aspx

I hope it will help you.

Best regards,

Dimitry Haritonov