Solved

Transfer MS SQL database from one server to another

Posted on 2009-05-06
18
773 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

730 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