sql server database upgrade procedure

hi,
I have a program that connects to a SQL server database.
The server is currently server2003 - sql server standard 2003 v8.00.760 (SP3)  32bit

I need to upgrade the software and the software want the backend database upgraded to 64bit.

I have a second server running srv 2008 and sql svrr 2008, and I have a license for sql svr 2012 ready to go.

What would be the best procedure to move the database over to the new server and upgrade it to 64bit?
LVL 20
wolfcamelAsked:
Who is Participating?
 
Lionel MMConnect With a Mentor Small Business IT ConsultantCommented:
I usually use the procedure to make a backup (.bak) of the sql database from SQL 2000; then in the later version of SQL you create a "new" database using the "restore database" function in Enterprise Manager. This usually works but depending on the application you are using you may have to adjust tables or add fixes to account for the changes in the SQL 2000 and later versions structure.
0
 
HuaMin ChenConnect With a Mentor System AnalystCommented:
Try to do these
set up SS 2012 on new server
detach the databases which are running one old versions
copy .mdf and .ldf files from old server to new one
attach the databases to SS 2012
0
 
wolfcamelAuthor Commented:
are you sure that is all I need to do?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
wolfcamelAuthor Commented:
I have seen suggestions that I need to get it onto sql2008 first and then to 2012 - but I would rather not if I can avoid it!
But I don't want to try and go straight to 2012 only to find I need to go back to 2008 as I never like downgrading any app as something is always prone not to work!
0
 
wolfcamelAuthor Commented:
and sorry - that is sql server 2000 that is running on server 2003!
0
 
HuaMin ChenSystem AnalystCommented:
You can try to set up SS 2012 and then attach those .mdf/.ldf file from your previous SS 2000, to that.
0
 
Anthony PerkinsCommented:
You cannot upgrade directly from SQL Server 2000 to 2012 and you certainly cannot downgrade.  So I would test carefully first.
0
 
David ToddSenior DBACommented:
Hi,

As per Anthony's comment above, I find it much safer to restore databases than to detach/attach.

You are looking at needing to restore/backup from an intermediate version - either SQL 2005 or SQL 2008.

Do check the compatibility level.

Do check the page validation.

HTH
  David
0
 
wolfcamelAuthor Commented:
thanks david - however I am no SQL expert ..so..
How do I check the compat level and page validation?

I have 2008 on the machine now and will upgrade to 2012 later if I can get it working on 2008!
0
 
David ToddSenior DBACommented:
Hi,

Right click on the database and select properties.

I can give you the queries, but far easier initially to do from the SSNS GUI.

HTH
  David
0
 
wolfcamelAuthor Commented:
it was easier than I thought - I connected to both servers and did a copy and everything worked well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.