Solved

Relocating MS SQL Datatbase

Posted on 2011-03-16
16
369 Views
Last Modified: 2012-05-11
I have to relocate a MS SQL Database from it's current server to a new server, due to application conflict. MS has  this KB "http://support.microsoft.com/kb/314546" which does not give a tool name to use in order to backup the DB. The only tool on the server is "SQL Server Configuration Manager" and it it I do not see any viable way to backup or restore a database. I do not know what's missing. Can someone her please assist me with the information regarding back and restore of a DB. The server OS in both source and destination is Windows 2008 R2 Enterprise.

Thanks in Advance
0
Comment
Question by:cyberchrisrock
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 2

Expert Comment

by:gilem
ID: 35151912
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 35156067
You'll need "Microsoft SQL Server Management Studio" (SSMS) .

it should be on your installation CD, see attached screen shot for location and file name.

Then use the "Copy Database Wizard"
instructions here: http://msdn.microsoft.com/en-us/library/ms188664.aspx


There's other ways but that's the easiest GUI ever.


it's an easy process .
Please post your outcome, thoughts.

Thanks
Jason
 SSMS location
0
 

Author Comment

by:cyberchrisrock
ID: 35156830
Jason, I am running 2008 R2 OS but the SQL is 2005.. I downloaded and installed SQL Server Management Studio Express as suggested by "gilem:" and was able to get a backup from the original location, however on the destination server when I open Management Studio,I installed the program that requires the database, it in turns installed an instance of SQL 2005 with an empty database. I then copied the backup over to the new serve and attempted to restore, but I got an error message. I did not see an option to "copy"  the database. Is that because I am using the Express version OS Server Managemnent?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 35157014
You said in the question "The server OS in both source and destination is Windows 2008 R2 Enterprise.
"  what's the 2005 server then?

Also the copy database should be done at the source and then it'll give you option to specify the destination and it'll take care of the logins and everything.

Yep, it's limited, see here: http://msdn.microsoft.com/en-us/library/ms365247.aspx

that's why I advised you to install from the original CD.
0
 

Author Comment

by:cyberchrisrock
ID: 35157072

Hi huslayer:

Sorry if I was not clear, the SQL version  is 2005. I am trying to find the CD/DVD and install the real deal. I will update you.
0
 

Author Comment

by:cyberchrisrock
ID: 35157689
I found the "SqlRun_Tools.msi" on the installation DVD, but each time that I try to install it I get this error that says :A componenet that you have specified in the ADD_LOCAL property is already installed. To upgrade the existing componenet, refer to the template.ini and set the UPGRADE property to the componenet"

I would put a screen shot here but I cant paste anything into this window. I am almost there..I just need to get this installed.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 35157694
SSMS 2008 won't connect to a 2005 instance, you need SSMS 2005 from the CD.

are you doing 2005 to 2008 ? or 2005 to 2005 ?
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 35157752
download this and run it, just registry cleaning.

it's not avilable at microsoft's website anymore because it's discountinied.
http://www.geekstogo.com/forum/files/file/397-msicuu2exe/
0
 

Author Comment

by:cyberchrisrock
ID: 35158179
I downloaded the utility and used it. Afterward I was able to install the Server Management Studio. When I right click on the database, and go to Task, in th esubmenu I have 7 options, but none of them is "Copy"

Any clues? This is the full Server management studo, not the Express...
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 35158206
Cool, good news !

the one I sent you originaly was for 2008 responding to the question :)


use this article, it's for 2005

http://msdn.microsoft.com/en-us/library/ms188664(v=SQL.90).aspx

0
 

Author Comment

by:cyberchrisrock
ID: 35158248
Again, the instructions are the same, Go to object explorer, right click on the database to yo task and choose copy.. That is where I am at a dead end. The copy option/function, does not appear in the fly out sub menu at all..
0
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 500 total points
ID: 35158586
That's weired!! then try the backup, and restore to the new server and see what's going on.
0
 

Author Comment

by:cyberchrisrock
ID: 35159110
When I try the backup/restore option, upon restore attempt, I get an error messgae that say "The backup set holds a backup of a database other than the existing" !!

It's just not my day!!
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 35159405
no that's fine, use this query, with REPLACE is the keyword:

RESTORE DATABASE cyberchrisrock
FROM DISK = 'C:\Backupcyberchrisrock.bak'
WITH REPLACE
0
 

Author Comment

by:cyberchrisrock
ID: 35165400

huslayer: I took your advise and put the backup files on the destination server, then restored the database using the "over write" option which worked.. Thank you so very much for sticking with me.
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 35176619
You;re welcome, glad that it worked out for you.
But still can't figure out why that option was not available!!
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 62
Moving away from Access 2003 adp files 4 46
T-SQL: Number of Records is Greater Than One 7 50
Sql group by orderdate and ordertotal 3 6
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

737 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