Link to home
Start Free TrialLog in
Avatar of Peterson50
Peterson50Flag for United States of America

asked on

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.
Avatar of Jonathan Kelly
Jonathan Kelly
Flag of Ireland image

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
Avatar of Peterson50

ASKER

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

User generated image
btw, after making your backups, you should also run DBCC CHECKDB on your user databases to test the data integrity on the old server.
Will be in the office this week and try it.  Thanks
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?
SQL 2003? That doesn't exist.
Should be MSSQL 2000 or 2005? Can you confirm the SQL version?
It is MSSQL 2000 Service Pack 3 as the version says 8.0.760
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Peterson, any update on this one?
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
PaulUser generated imageUser generated image
You are restoring a 2000 db database on a 2005 server ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 serverUser generated imageUser generated image
Is D: a local device?

make sure the sql server service account has permission to use it.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

PaulUser generated imageUser generated imageUser generated imageUser generated image
You only repeated the images. What's really the issue now? Can't you get the instance name in the ODBC?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
All of the protocols are enabled, see belowUser generated image
Also here is the surface configuration tool pics.  The SQL Browser is disabled, should that be enabled on the serverUser generated image
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
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.
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?
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?
Cheers ;)