Moving SQl Server SBS 2003 to SQL Server 2014

Peterson50
Peterson50 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
Thanks, where do I find the Create Backup command in SQL
ste5anSenior Developer

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Will be in the office this week and try it.  Thanks

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
SQL 2003? That doesn't exist.
Should be MSSQL 2000 or 2005? Can you confirm the SQL version?

Author

Commented:
It is MSSQL 2000 Service Pack 3 as the version says 8.0.760
IT Engineer
Distinguished Expert 2017
Commented:
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.

Author

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
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Peterson, any update on this one?

Author

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
You are restoring a 2000 db database on a 2005 server ?
select @@version will give you a version

Author

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
Is D: a local device?

make sure the sql server service account has permission to use it.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
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.

Author

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
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You only repeated the images. What's really the issue now? Can't you get the instance name in the ODBC?
you may need to "allow remote connections" via the surface are config tool.

Author

Commented:
All of the protocols are enabled, see belowPic3.JPG

Author

Commented:
Also here is the surface configuration tool pics.  The SQL Browser is disabled, should that be enabled on the serverpic4.JPG

Author

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ãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

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?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial