Solved

Transfer MS SQL database from one server to another

Posted on 2009-05-06
18
769 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now