Solved

sql server database upgrade procedure

Posted on 2014-03-04
11
438 Views
Last Modified: 2014-05-03
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?
0
Comment
Question by:wolfcamel
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 250 total points
ID: 39905215
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
 
LVL 20

Author Comment

by:wolfcamel
ID: 39905229
are you sure that is all I need to do?
0
 
LVL 20

Author Comment

by:wolfcamel
ID: 39905258
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
 
LVL 20

Author Comment

by:wolfcamel
ID: 39905263
and sorry - that is sql server 2000 that is running on server 2003!
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39905311
You can try to set up SS 2012 and then attach those .mdf/.ldf file from your previous SS 2000, to that.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

Accepted Solution

by:
lionelmm earned 250 total points
ID: 39907027
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39907485
You cannot upgrade directly from SQL Server 2000 to 2012 and you certainly cannot downgrade.  So I would test carefully first.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39916737
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
 
LVL 20

Author Comment

by:wolfcamel
ID: 39916762
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39916862
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
 
LVL 20

Author Closing Comment

by:wolfcamel
ID: 40039367
it was easier than I thought - I connected to both servers and did a copy and everything worked well.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I get blanks instead of zero's to show up in my results? 2 30
sql query help 2 46
Square brackets 4 39
SQL Query 2 43
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
INTRODUCTION The purpose of this document is to demonstrate the Installation and configuration of the Data Protection Manager product. Note that this demonstration was prepared on the basis of Windows OS is 2008 R2 and DPM 2010. DATA PROTECTI…
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now