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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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).
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).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Since you have the choice, go with 2008r2 for the DB.
ASKER
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?
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?
Don't you have the SQL Server 2008R2 setup media? Just run the setup and choose the Management Studio in the Features selection screen.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, I managed to install it after Repairing the installation.
Attempting to move the database.
Attempting to move the database.
ASKER
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.SmoEx
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.SqlE
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?
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.SmoEx
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.SqlE
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?
ASKER
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?
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?
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 ?).
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 ?).
ASKER
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.
"c:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\osql.
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?
"rem net start MSSQL$CDRDICOM
"c:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\osql.
"c:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\osql.
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
https://msdn.microsoft.com/en-us/library/ms188664.aspx
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.
Open in new window