Improve company productivity with a Business Account.Sign Up

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

Moving SQl Server SBS 2003 to SQL Server 2014

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
Peterson50
Asked:
Peterson50
  • 12
  • 7
  • 7
  • +1
4 Solutions
 
Jonathan KellyCommented:
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
 
Peterson50Author Commented:
Thanks, where do I find the Create Backup command in SQL
0
 
ste5anSenior DeveloperCommented:
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
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.

 
Jonathan KellyCommented:
Take a look at this article
https://support.microsoft.com/en-us/kb/314546
0
 
Peterson50Author Commented:
Will be in the office this week and try it.  Thanks
0
 
Peterson50Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL 2003? That doesn't exist.
Should be MSSQL 2000 or 2005? Can you confirm the SQL version?
0
 
Peterson50Author Commented:
It is MSSQL 2000 Service Pack 3 as the version says 8.0.760
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Peterson50Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Peterson, any update on this one?
0
 
Peterson50Author Commented:
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
 
Jonathan KellyCommented:
You are restoring a 2000 db database on a 2005 server ?
0
 
Jonathan KellyCommented:
select @@version will give you a version
0
 
Peterson50Author Commented:
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
 
Jonathan KellyCommented:
Is D: a local device?

make sure the sql server service account has permission to use it.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Peterson50Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You only repeated the images. What's really the issue now? Can't you get the instance name in the ODBC?
0
 
Jonathan KellyCommented:
you may need to "allow remote connections" via the surface are config tool.
0
 
Peterson50Author Commented:
All of the protocols are enabled, see belowPic3.JPG
0
 
Peterson50Author Commented:
Also here is the surface configuration tool pics.  The SQL Browser is disabled, should that be enabled on the serverpic4.JPG
0
 
Peterson50Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Peterson50Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Jonathan KellyCommented:
Cheers ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 12
  • 7
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now