Solved

Moving SQL Database from SEManager to SQL2008 or 5 Express

Posted on 2013-10-23
7
387 Views
Last Modified: 2013-10-27
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 :-)
0
Comment
Question by:mercuriousu2
  • 3
  • 3
7 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39596009
If the DB size is less than 10GB you can move it to an Express instance.

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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39596028
Hi,

Instead of pushing the database across to the higher-level instance, pull it across from that instance.

Regards
  David
0
 

Author Comment

by:mercuriousu2
ID: 39596459
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.Express.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.Management.Smo.Restore.SqlRestore(Server srv)
   at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore()

===================================

System.Data.SqlClient.SqlError: Directory lookup for the file "d:\databases\Main1st_Data.MDF" failed with the operating system error 2(error not found). (Microsoft.SqlServer.Express.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.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages)
   at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries)
   at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
SQL-Error.jpg
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 38

Expert Comment

by:Jim P.
ID: 39597554
Does the server have a D:\Databases\ folder? You'll probably have to go in and mess with the paths to do the restore.
0
 

Author Comment

by:mercuriousu2
ID: 39599676
its not that the restore could not find the .BAK file, I have previously moved it to the default folder it was looking for.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39600372
The  "d:\databases\Main1st_Data.MDF" is the SQL DB Main Data File that it was trying to write to. So if the folder doesn't exist, or can't be accessed by the SQL Server service it can't do the restore.
0
 

Author Closing Comment

by:mercuriousu2
ID: 39604086
Many thanks indeed, this worked fine. Appreciate the response and also the previous DNS tip :-).
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now