Solved

Move SQL 2005 Express to Server 2012R2

Posted on 2016-10-23
  • MS SQL Server 2005
  • MS SQL Server
  • Windows Server 2012
  • MS Server OS
  • Databases
  • +1
19
68 Views
Last Modified: 2016-11-22
Hi

My customer has SQL 2005 Express database on Windows 7 which contains xrays for Schick software and hardware in the surgeries. They used to use that Windows 7 as their "server". We now have Dell PowerEdge T320 dedicated server and we managed to move everything except the SQL database in question.
I assume that I cannot just install SQL 2005 Express on Server 2012 R2.
Could you please provide me as much information as you can including step-by-step instructions on how to move that database to SQL on our new server?
(I assume it must be Express or that kind of type version of SQL since we do not have full SQL licence)

Regards,
Tom
0
Comment
Question by:it10
  • 8
  • 5
  • 3
  • +2
19 Comments
 
LVL 68

Accepted Solution

by:
Qlemo earned 300 total points
Comment Utility
You'll need MSSQL 2008 or 2008R2 to migrate the database as an intermediate step. Since you need that anyway, I would install and stay with MSSQL 2008r2 Express (for starters). You can decide at any point in time to go with a higher release; even 2016 doesn't have issues with a 2008 DB.

You can either create a full backup of the database, and restore it to the other server, or detach the database on the old server, copy the datafiles over (there are at least two. the mdf and ldf with the database name) to the new server's file system, and then attach the DB there.
If you do not know the path of the DB files: go into the properties of the DB in Management Studio (SSMS) to find out. SSMS can be used for the detach and attach operation via context menu on Databases resp. the database itself.

If you are using SQL Login instead of Windows Authentication you'll also have to create that SQL user(s) on the new server, then revalidate the user info in the DB itself by running
  use YourDBName
   exec sp_change_users_login 'Update_One', 'YourUserName', 'YourUserName';

in a query window on the new location.
1
 
LVL 76

Assisted Solution

by:arnold
arnold earned 100 total points
Comment Utility
Double check, but there might be a direct path, from SQL 2005 to 2012 R2
Often, SQL supports the prior two derision, SQL 2005 has support for SQL 2000 and SQL 7

The R2s add half a version to each SQL 2008 and 2012
SQL 2005 90
SQL 2008 100
SQL 2008 r2 100.5
SQL 2012 110
SQL 2012 110.5

Qlemo's suggested for intermediary upgrade is valid, guess should have started with this statement.

Depending on your timeframe for transition, one option could be to virtualized the existing Windows 7 setup.
You can then test the application with the db on SQL 2008/2012....

Does the software vendor support SQL 2012/r2?
You should be able to install SQL

The data might be split, some data in the database, while the images being stored by reference in the database, while the files stored within the file system versus as a blob within the database.

Carefully, thoroughly reviewing the setup, interaction, and current backup setups........
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
You can go immediately from MSSQL 2005 to 2012. Beside MSSQL 2012 Express Edition allows databases up to 10GB (in 2005 limit is only 4GB).
Just take a backup of the SQL Server 2005 database and restore it in the new SQL Server 2012 instance. Then change the compatibility level to 110 and perform a reindex for the database.
EXEC sp_MSforeachtable @command1='DBCC DBREINDEX (''?'', '''', 0)'

Open in new window

0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
I stand corrected. https://msdn.microsoft.com/en-us/library/ms143393.aspx#SupportFor2005 confirms you can attach or restore a 2005 DB even in 2016, just not perform an in-place instance update - and you need to have 2016 on a different machine, as is the case here.
2012 could be installed as an upgrade. So your choice should be 2012, 2014 or 2016 Express.

And then you need to either detach/attach or backup/restore, as described.
0
 

Author Comment

by:it10
Comment Utility
Guys, thank you for all your suggestions.

I already have SQL 2008 and 2008 R2 installed on the server since Backup Exec and Florida Probe software installed it.
Am I okay to use that?
Also, I got used to Studio Management in 2005 but cannot see it in 2008, can this be added or is there another way of doing that?

(I do apologise for the delay).
0
 
LVL 76

Assisted Solution

by:arnold
arnold earned 100 total points
Comment Utility
Yes, it is a component that needs to be added, modify the intalled instance. But you have to get the SSMS package......
https://www.microsoft.com/en-us/download/details.aspx?id=7593
there is the same for R2 and 2012., When downloading the server installer, the sqlmanagement should be in the list of the selection to download.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Again, you can get up to SSMS 2016, they all connect to 2008 fine.
Since you have the choice, go with 2008r2 for the DB.
0
 

Author Comment

by:it10
Comment Utility
Thank you for all your help. And sorry for delay.

I downloaded Studio Express but as soon as I "Run as administrator" it give me the error message. I attached it here SQL-Server-2008-Management-Studio-Ex.txt. Could you please have a look at it?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Don't you have the SQL Server 2008R2 setup media? Just run the setup and choose the Management Studio in the Features selection screen.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:it10
Comment Utility
When I run the installation files which were used to install SQL, it doesn't give me an option for Studio Management.#
Here is the list of SQL instances on that server:

Microsoft SQL Server 2008 R2 SP2 Setup Discovery Report

Microsoft SQL Server 2008 R2 BKUPEXEC MSSQL10_50.BKUPEXEC Database Engine Services 1033 Express Edition 10.52.4042.0
Microsoft SQL Server 2008 R2 BKUPEXEC MSSQL10_50.BKUPEXEC SQL Server Replication 1033 Express Edition 10.52.4042.0
Microsoft SQL Server 2008 R2 FLPROBESQL MSSQL10_50.FLPROBESQL Database Engine Services 1033 Express Edition 10.50.1600.1
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
Comment Utility
When I run the installation files which were used to install SQL, it doesn't give me an option for Studio Management.
Unfortunally isn't so intuitive. You'll need to choose a new SQL Server standalone install and in the Features screen unselect the SQL Server engine and select the Management Tools option.
If you still have doubts follow this step-by-step installation.
0
 

Author Comment

by:it10
Comment Utility
Okay, I managed to install it after Repairing the installation.
Attempting to move the database.
0
 

Author Comment

by:it10
Comment Utility
Steps I followed:
1. SSMSE 2005 > Database1 > Tasks > Back up > database1.bak
2. On new server added new SQL instance called Database1 through the installer.
3. SSMSE 2008 > Database1 > Databases > Restore Database > pointed to the database1.bak file and then set new path in the Options of the Restore Database Wizard.
Got the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'SRVR\database1'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4042.0+((KJ_SP2_GDR).150326-2139+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The media set has 2 media families but only 1 are provided. All members must be provided. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4042.0+((KJ_SP2_GDR).150326-2139+)&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

What do you think?
0
 

Author Comment

by:it10
Comment Utility
Spoke too soon. I googled it and managed to restore by pointing to the second backup file.

The next question I have is this:
On the old server was the folder shared across the network called "databasexrays" which had the folders and files for each  patient for whom the xray was taken. By the way the Database1 was the database of xrays for the software called Schick /CDR Dicom.
So what is the likelihood that I need to re-create and copy that folder from old server to the new one?
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Most likely the DB contains (text) links to the xray images. Just copying over the folder wouldn't help then. Either there is a single "root folder" setting somewhere in the DB, and paths to images are stored based on that, or the full path is stored.
You need to find out the tables containing references, and look at the data. Only that way you will see yourself. Better: ask the software manufacturer Schick (https://www.schickbysirona.com ?).
0
 

Author Comment

by:it10
Comment Utility
Also, I have found in the installation folder of Schick /CDR Dicom the following command file "CreateCDRData64.bat":

"rem net start MSSQL$CDRDICOM
"c:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\osql.exe" -S (local)\CDRDicom -Usa -Pcdr -i createDB_SQL.sql
"c:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\osql.exe" -S (local)\CDRDicom -Usa -Pcdr -d CDRData -i configDB_SQL.sql

Pause ..."

and there are two other files in that folder:
configDB_SQL.sql
createDB_SQL.sql

Do you think that I could modify that I could modify that command file to reflect SQL 2008 and it would work?
0
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 300 total points
Comment Utility
I don't think there are release-related commands in those SQLs. You might see how settings are stored (and their default values) in the config script. But you will not have to use any of those scripts.
0
 

Author Closing Comment

by:it10
Comment Utility
Thank you very much, the question was related to moving the database which was accomplished. Although, I still need to move x-ray images but that is separate issue.
0
 
LVL 7

Expert Comment

by:Jason clark
Comment Utility
You can use the Copy Database wizard which makes it easy to move object from one SQL Server to another SQL Server. For complete details visit:
https://msdn.microsoft.com/en-us/library/ms188664.aspx
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

772 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

10 Experts available now in Live!

Get 1:1 Help Now