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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jonathan KellyCommented:

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

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

Peterson50Author Commented:
Thanks, where do I find the Create Backup command in SQL
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).

btw, after making your backups, you should also run DBCC CHECKDB on your user databases to test the data integrity on the old server.
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Jonathan KellyCommented:
Take a look at this article
Peterson50Author Commented:
Will be in the office this week and try it.  Thanks
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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL 2003? That doesn't exist.
Should be MSSQL 2000 or 2005? Can you confirm the SQL version?
Peterson50Author Commented:
It is MSSQL 2000 Service Pack 3 as the version says 8.0.760
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

Vitor MontalvãoMSSQL Senior EngineerCommented:
Peterson, any update on this one?
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.

Jonathan KellyCommented:
You are restoring a 2000 db database on a 2005 server ?
Jonathan KellyCommented:
select @@version will give you a version
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
Jonathan KellyCommented:
Is D: a local device?

make sure the sql server service account has permission to use it.
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.
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.

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?
Jonathan KellyCommented:
you may need to "allow remote connections" via the surface are config tool.
Peterson50Author Commented:
All of the protocols are enabled, see belowPic3.JPG
Peterson50Author Commented:
Also here is the surface configuration tool pics.  The SQL Browser is disabled, should that be enabled on the serverpic4.JPG
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
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.
Peterson50Author Commented:

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?
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?
Jonathan KellyCommented:
Cheers ;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.