asked on

Move a MSSQL Database to another server

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 ??

AustinSeven

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

vidnan123

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?

arbert

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

MadDog986

ASKER

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.

arbert

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....

ASKER CERTIFIED SOLUTION

AustinSeven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

arbert

"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...