Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Move a MSSQL Database to another server

Posted on 2004-10-05
9
Medium Priority
?
204 Views
Last Modified: 2011-09-20
I need to be able to move a MSSQL database to another server. But the problem is both of the servers are shared so i do not have access to the actually servers.

Is there a way to transfer the databases keeping all information intact including default values, indexes, records, tables, etc, etc ??
0
Comment
Question by:MadDog986
[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
9 Comments
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12225017
Can you connect to the databases on both servers using SQL Server client tools like Query Analyzer?   If not, why not?  Is it because you don't have SQL Server Client Tools installed or is it because the hosting company doesn't give you the necessary level of access in order to connect to your databases?   It's worth clarifying this exactly because if you can connect via SQL Server client tools, it'll make a big difference.

If you can move a database using standard backup/restore, all database objects will go with it so no problem there.  However, your hosting company would need to assist because the server level Logins will need mapping to your database level users.  ie. you won't have system admin privs.  

One typical scenario with shared services is that you might have rights to run T-SQL so you could script the schema in one database and re-apply it to another.   However, this would not script the data so another step is required.  Before continuing, let's find out what level of access you have.

AustinSeven
0
 
LVL 2

Expert Comment

by:vidnan123
ID: 12225030
Could you please elaborate on what you mean by 'servers are shared' and you do not have access to the actual servers?

One good old way to move databases between servers is to do a BACKUP of the database on server 1 and RESTORE the same on server2. But for that, you must be able to access the .BAK/.DAT file on server 1 and transfer the same to a location on server2. And, then perform the RESTORE activity

Do you mean that you would not able to access the server1 and server2?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12234423
No matter what solution you choose (Backup/Restore or Attach/Detach), remember you will need to recreate any logins that don't exist on the second server and reattach the user's in the database to their logins using sp_change_users_login.

Brett
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:MadDog986
ID: 12256492
As i said, the servers are shared servers. Meaning i do not have access to them and orignal host will not do a backup of the database.

I also can not use Enterprise Manager because i do not have it. I was hoping to find a way to move the database to the new server. Maybe some kind of program that i can enter both connections into and it does the rest.

But when the database is transfered everything has to go with it including default values, indexs, relationships, etc, etc.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12258793
So if you don't have access to them, what kind of magical program do you expect to find?  You need to have some kind of access to the databases to copy them....
0
 
LVL 10

Accepted Solution

by:
AustinSeven earned 1500 total points
ID: 12258978
Maybe the point is that you don't have 'SQL Server Client Tools' installed even though the 'shared service' providers have already given you the necessary level of access?   Please clarify.   Ask your shared service providers if you can have connectivity from SQL Server client tools.    The issue of getting SQL Server client tools installed at your end is then another subject but certainly can be done (install SQL Server 2000 but select 'Client Tools Only').  

If the old service provider doesn't give you access for SQL Server Client Tools, get onto them and see if they will at least zip up a backup of your database and send it to you.   I know that a lot of 'cheaper' providers will not even answer your email but it's worth a try.   I suppose that the size of your DB is a factor but, unless it's very big, the chances are that it will fit on  a CD once it's zipped.   If they say they don't offer such a service, speak the the customer services dept. and offer to pay them the cost of a blank CD plus postal charges.   As for the new service provider you are trying to get this database restored to, if they don't offer you the ability to connect via SQL Server client tools, you shouldn't be dealing with them anyway (not too late to change your mind and look elsewhere).  

The point is that you need to have an adequate level of network connectivity & privilages to your database.   If your service providers don't offer you this, you're stuffed unless you get their help.  

AustinSeven
0
 
LVL 34

Expert Comment

by:arbert
ID: 12448802
"Meaning i do not have access to them and orignal host will not do a backup of the database."

I'm not sure how we were ever suppose to answer an opened ended question....It's like asking how to put air into a tire when you don't have access to your tire...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

604 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