Solved

Transfer MS SQL database from one server to another

Posted on 2009-05-06
18
771 Views
Last Modified: 2013-11-26
Hello Experts,

I've a SQL Server database on a web server. As I changed my hosting company I would like to transfer the database to the new server. What is the best way to do this? Can it be done using Visual Studio? Both connection are available on my VS Server Explorer. Is it possible to transfer the tables from one connection to the other?

Thanks for your support,

jppinto
0
Comment
Question by:jppinto
  • 9
  • 8
18 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24313221
well there are quite a few ways, either go for import and export or take a .BAK file from your current hosting company, give it to your new hosting company and tell them to restore it.
0
 
LVL 9

Expert Comment

by:tillgeffken
ID: 24313261
Perform a full backup on server A and import the backup on server B using SQL Server Management Studio.

You might need to fix the login to user mapping for the users/logins you created. Since you don't mention your SQL Server version either this or that article may help.

Good Luck.
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24313322
user mapping need to be fixed if take a backup from sql server 2000 and restore it in 2005. I have also written small article and script for the same at my blog.

http://www.sqlhub.com/search?q=syncronize+user

Aslong as full backup concern, can't be done by SSMS also as .BAK file will be generated at hosting server and have to be uploaded at other hosting company so the best idea is to ask first hosting company to take full backup and put it in website FTP, author can download it and give it to other hosting company to restore it.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 33

Author Comment

by:jppinto
ID: 24324410
I've used the Enterprise Manager of the hosting company and manage to restore the backup from the old hosting company server. In VS I can access the tables and data but the name of the tables appear something like this tblTableName (username) and I get errors connecting to the tables when I run my site. Is this related with the mapping of the users? How can I change the mapping if I can't run the SQL Server Managment Studio since I'm limited to the online options my hosting company gives me?

jppinto
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24324456
which error are you getting? moreover, which user name do you see in bracket beside the table name? which user name are you using to connect to database?

0
 
LVL 33

Author Comment

by:jppinto
ID: 24324808
In front of my table name in brackets i see the username of the "old" server. When I try to run my website I get an error of table name not found. Now I'm trying to connect to the database using the username of the new server database.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24324851
you have to create login "old" server with same password and you may be ok, this is the easiest solution or else you can transfer the schema and permission but that we will see later if first suggestion won't work.
0
 
LVL 33

Author Comment

by:jppinto
ID: 24328873
My problem is that both servers are from the same company and I had to create a new user name for the "new" server, I was not able to have the same user name on both servers because I get a message saying that the username (from the first server) already exists if I try to create the same username on the "new" server. So, I created a different username. The only solution to do what you propose is to delete the database and user from the first server as I have the backup their shouldn't be a problem.

I will try it tomorrow and let you know the results.

Thanks for your support.

jppinto
0
 
LVL 33

Author Comment

by:jppinto
ID: 24334149
OK, I've done this:

Backup of the "old" server. Deleted the database from the old server so that I could create a new database on the new server with the same name and username of the "old" one.
Created a database on the "new" server with the same name and username of the "old". Restored the backup that I've made from the "old" server.

The database connection appears in Visual Studio as webxxx.databasename.dbo. On the old server it used to appear as webxxx.databasename.username. No the tables appear like tbltablename (username) while on the old server they appeared like tbltablename only.

I still get the error when I run my website:

Invalid object name 'tbltablename'

I don't know what else I can do! I've changed the web.config connection string to the data of the new server/database, like this:

<add name="ConnString" connectionString="Data Source=mssqlxxxwin-servers.com;Persist Security Info=True;User ID=usernamehere;Password=passwordhere" providerName="System.Data.SqlClient"/>

Can you please help me?

Thanks again,

jppinto
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24334209
open your SSMS and execute following script than show me its results.


USE yourDataBaseName;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
0
 
LVL 33

Author Comment

by:jppinto
ID: 24334256
I work with the database in Visual Studio 2005. I don't have SSMS in my computer. Is there some place in Visual Studio that I can run the command provided?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24334262
well, in that case, you can do one thing, create one page in your application, run SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); in your application, print the results on grid, and show it to me.
0
 
LVL 33

Author Comment

by:jppinto
ID: 24334324
I've runned a Query in VS and I get this error!
error.jpg
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24334342
you don't have full access to your database and not a user name with full permission. I afraid, you could fix it, you are facing permission issue and you need to give permission to your user with login by powerful user which seems you don't have. I guess only your hosting company can help you to get permission on your own database.
0
 
LVL 33

Author Comment

by:jppinto
ID: 24334410
I've sent an email to the hosting company to see what wrong with my database. Let's wait for their answer. I'll get back to this as soon as they answer. Thanks for your help so far. I'll not forget this...

jppinto
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24334417
that's ok, glad to help you. don't hesitate to ping here again.
0
 
LVL 33

Author Comment

by:jppinto
ID: 24348441
The hosting company was not of much help! The solutions presented here should have worked. I ended up giving up and recreated the database tables and stored procedures from scratch on the new server with different usernames. They were not too much trouble.

Thanks for your support.

jppinto
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24348478
well, in that case, you should do everything from scratch. :(
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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.

772 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