Solved

Moving SQl Server SBS 2003 to SQL Server 2014

Posted on 2016-07-25
27
110 Views
Last Modified: 2016-10-13
We have a client who has refused until now to update his server and software.  The vertical application is no longer supported by the vendor and is a custom application.  So the option we have is to move the sql databases which are currently hosted on a SBS 2003 server.  We are installing a test server 2012 running SQL 2014 and will try to run the client app in a VM.
What is the easiest and best way to export the sql data from the old sql to the new.  All of the sql backup apps that run on sbs 2003 will not run on Server 2012, that we have tried.

I'm not a sql expert so need some walking through it.
0
Comment
Question by:Peterson50
  • 12
  • 7
  • 7
  • +1
27 Comments
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41727989
Hi,

For the database I would suggest using backup and restore.

1. Create a full backup of the database using the Create Backup command
2. Restore the backup to the new server using Restore command

3. You may have to re-create the sql logins if they are needed on the new server.
See this link on using a script to migrate the logins
https://support.microsoft.com/en-ie/kb/918992

4. Update the client software connection and config settings if the new server has different name/ip.

Rgds,
Jonathan
1
 

Author Comment

by:Peterson50
ID: 41727990
Thanks, where do I find the Create Backup command in SQL
0
 
LVL 32

Expert Comment

by:ste5an
ID: 41728053
hmm, there should no need to make extra backups, there should already happens ;) If not, you can sell your customer advanced server management..

Just right click on each database node in SSMS. Select Tasks and than Backup. See also
Create a Full Database Backup (SQL Server).

ssms.png
btw, after making your backups, you should also run DBCC CHECKDB on your user databases to test the data integrity on the old server.
1
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41729234
Take a look at this article
https://support.microsoft.com/en-us/kb/314546
0
 

Author Comment

by:Peterson50
ID: 41737615
Will be in the office this week and try it.  Thanks
0
 

Author Comment

by:Peterson50
ID: 41740028
Tried it, got an error message when attempting to restore, it recognized the .bak backup file but stated the backup was done on older version which is incompatible with this version which is SQL 2014.  Is there any tool I can use to do a restore from SQL 2003 to SQL 2014?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41742680
SQL 2003? That doesn't exist.
Should be MSSQL 2000 or 2005? Can you confirm the SQL version?
0
 

Author Comment

by:Peterson50
ID: 41744868
It is MSSQL 2000 Service Pack 3 as the version says 8.0.760
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41746751
You'll need to upgrade it to a middle version since SQL 2014 doesn't recognize MSSQL 2000 databases.
First thing to do is to update your MSSQL 2000 instance with SP4. Then get a VM with a SQL Server 2005 or 2008 installed so you can migrate your database to this middle version. After that you can migrate the database to a MSSQL 2014 instance.
0
 

Author Comment

by:Peterson50
ID: 41766307
I had to locate a sql 2008 disk which I am firing up on a Server 2008 VM to see what happens if I can get this to work, will post shortly

Thanks
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41785657
Peterson, any update on this one?
0
 

Author Comment

by:Peterson50
ID: 41818258
I have installed Server 2008 and then installed SQL 2005 which installed fine, tried to do a restore of the database and the error message I get is the following attached.  I'm running out of time of this one, did a vm conversion of the server which works but I don't want to run that as the domain controller.

Thanks
PaulSQLSQL
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41818276
You are restoring a 2000 db database on a 2005 server ?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 7

Assisted Solution

by:Jonathan Kelly
Jonathan Kelly earned 250 total points
ID: 41818285
select @@version will give you a version
0
 

Author Comment

by:Peterson50
ID: 41818323
I was using a .bkf file used by a backup program, I then did a backup of the sql files on the old server to default .bak format and tried to restore to 2005 SQL and the following are the error messages that I received.  It shows the proper sql file being restored and then I got an error message trying to restore in the second shot.  Should I give up on this, this was a vertical app which the vendor no longer produces or supports and we just want to move databases to newer serverRestore-of-Bak-file.JPGRestore-of-Bak-file-2.JPG
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41818641
Is D: a local device?

make sure the sql server service account has permission to use it.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 41819222
Are you trying to restore a SQL Server 2008 database backup into a SQL Server 2005 instance? If so, you can't downgrade database versions.
0
 

Author Comment

by:Peterson50
ID: 41827219
I was finally able to get the restore of the data using SQL Server 2005 installation.  I just have one quick question, I tried using ODBC create a connection to the database for the application.  But when it tries to pull up the instance of SQL running nothing appears.  I did this on the server.  In picture one it shows the three databases ARMSxxxx but even though services shows SQL running I do not see the instance of it.

PaulPic1.JPGpic2.JPGPic1.JPGpic2.JPG
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41827522
You only repeated the images. What's really the issue now? Can't you get the instance name in the ODBC?
0
 
LVL 7

Assisted Solution

by:Jonathan Kelly
Jonathan Kelly earned 250 total points
ID: 41827523
you may need to "allow remote connections" via the surface are config tool.
0
 

Author Comment

by:Peterson50
ID: 41828246
All of the protocols are enabled, see belowPic3.JPG
0
 

Author Comment

by:Peterson50
ID: 41828291
Also here is the surface configuration tool pics.  The SQL Browser is disabled, should that be enabled on the serverpic4.JPG
0
 

Author Comment

by:Peterson50
ID: 41828442
You guys were spot on, finally got everything recognized only to have the old vertical application (running in a Windows XP vm) give the error, "This release has expired please download newer release"
There is no newer release as we have been keeping this one alive on life support.  At this point we are probably going to use the sbs 2003 in a vm with windows xp in a vm to keep everything going and plan on moving all the data out of it
My brain hurts and I've run out of ideas
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41829319
The SQL Browser is disabled, should that be enabled on the server
Only if you're using named pipes connections.

This release has expired please download newer release"
Can't do nothing about that. You should upgrade to SBS 2008 (if not already expired) or SBS 2011.
0
 

Author Comment

by:Peterson50
ID: 41835387
Vitor,

Quick question will upgrading to SBS 2008 automatically update the SQL on the box also, should it be an inplace upgrade or replacement (install on new box then demote and promote new server to controller), what do you recommend?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41836457
Sorry, I'm only a SQL Server Expert. Never worked with SBS so can't really answer your last question.
Did you try to google for SBS migration?
0
 
LVL 7

Expert Comment

by:Jonathan Kelly
ID: 41841969
Cheers ;)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What to do when Windows Update is not working correctly? What tools can I use to detect the cause of the malfunction problem? What does this numeric error code mean? These and other questions that you have been asking in the past are answered here (…
Resolve DNS query failed errors for Exchange
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…

757 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