mercuriousu2
asked on
Moving SQL Database from SEManager to SQL2008 or 5 Express
Hi, I have an older 2003 server running a database on Enterprise Manager. I want to copy this to either an SQL Express instance on another 2003 server or ideally onto a SQL server running 2008R2.
When I ran a move/copy wizard on the server, but it could not find my other network servers. So I ran the Register SQL Server Wizard, it found both or the other instances but an error occurred stating: "to connect to this server you must use sql server management studio or sql server management objects".
Could someone please provide a blow by blow breakdown of how I can accomplish this, with any gotcha's on the database upgrade process so when attached on the new server my users do not have any connection issues.
Thanks in advance to all answers :-)
When I ran a move/copy wizard on the server, but it could not find my other network servers. So I ran the Register SQL Server Wizard, it found both or the other instances but an error occurred stating: "to connect to this server you must use sql server management studio or sql server management objects".
Could someone please provide a blow by blow breakdown of how I can accomplish this, with any gotcha's on the database upgrade process so when attached on the new server my users do not have any connection issues.
Thanks in advance to all answers :-)
Hi,
Instead of pushing the database across to the higher-level instance, pull it across from that instance.
Regards
David
Instead of pushing the database across to the higher-level instance, pull it across from that instance.
Regards
David
ASKER
Hi
I tried to restore the database backup to the Express version, I got the error, any ideas?
David: not sure how to achieve that, from the express version the only restore option was browsing locally.
========================== =========
Restore failed for Server 'ppm-srv-05\SQLEXPRESS'. (Microsoft.SqlServer.Expre ss.Smo)
-------------------------- ----
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
-------------------------- ----
Program Location:
at Microsoft.SqlServer.Manage ment.Smo.R estore.Sql Restore(Se rver srv)
at Microsoft.SqlServer.Manage ment.SqlMa nagerUI.Sq lRestoreDa tabaseOpti ons.RunRes tore()
========================== =========
System.Data.SqlClient.SqlE rror: Directory lookup for the file "d:\databases\Main1st_Data .MDF" failed with the operating system error 2(error not found). (Microsoft.SqlServer.Expre ss.Smo)
-------------------------- ----
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
-------------------------- ----
Program Location:
at Microsoft.SqlServer.Manage ment.Smo.E xecutionMa nager.Exec uteNonQuer yWithMessa ge(StringC ollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
at Microsoft.SqlServer.Manage ment.Smo.B ackupResto reBase.Exe cuteSql(Se rver server, StringCollection queries)
at Microsoft.SqlServer.Manage ment.Smo.R estore.Sql Restore(Se rver srv)
SQL-Error.jpg
I tried to restore the database backup to the Express version, I got the error, any ideas?
David: not sure how to achieve that, from the express version the only restore option was browsing locally.
==========================
Restore failed for Server 'ppm-srv-05\SQLEXPRESS'. (Microsoft.SqlServer.Expre
--------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
--------------------------
Program Location:
at Microsoft.SqlServer.Manage
at Microsoft.SqlServer.Manage
==========================
System.Data.SqlClient.SqlE
--------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
--------------------------
Program Location:
at Microsoft.SqlServer.Manage
at Microsoft.SqlServer.Manage
at Microsoft.SqlServer.Manage
SQL-Error.jpg
Does the server have a D:\Databases\ folder? You'll probably have to go in and mess with the paths to do the restore.
ASKER
its not that the restore could not find the .BAK file, I have previously moved it to the default folder it was looking for.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks indeed, this worked fine. Appreciate the response and also the previous DNS tip :-).
The best way is to do a backup and restore to the new instance. There are so many explanations of how to do that, I'll let you do the extended research but it is basically right-click the DB in Management Studio and select backup and follow the prompts. Restore is pretty much a similar idea.
Then use the sp_help_revlogin to transfer logins and passwords between instances of SQL Server.
Now a suggestion to make your life easier in the future. If you have a DB that is on a server, even a named or instance like ServerName1\InstanceName and you have access to your DNS make a CNAme record like MyAppServer that points at the ServerName1. Then in the future if you want to move the DB or instance, all you have to do is modify the CName record to point at ServerName2. You won't have to touch the clients at all.