[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Move SQL 2005 Express to Server 2012R2

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
Tom Skowyrski
Asked:
Tom Skowyrski
  • 8
  • 5
  • 3
  • +2
5 Solutions
 
QlemoDeveloperCommented:
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
 
arnoldCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
QlemoDeveloperCommented:
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
 
Tom SkowyrskiAuthor Commented:
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
 
arnoldCommented:
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
 
QlemoDeveloperCommented:
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
 
Tom SkowyrskiAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Tom SkowyrskiAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Tom SkowyrskiAuthor Commented:
Okay, I managed to install it after Repairing the installation.
Attempting to move the database.
0
 
Tom SkowyrskiAuthor Commented:
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
 
Tom SkowyrskiAuthor Commented:
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
 
QlemoDeveloperCommented:
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
 
Tom SkowyrskiAuthor Commented:
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
 
QlemoDeveloperCommented:
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
 
Tom SkowyrskiAuthor Commented:
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
 
Jason clarkDBA FreelancerCommented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now