Transfer MS SQL database from one server to another

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
LVL 33
jppintoAsked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
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
 
RiteshShahCommented:
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
 
tillgeffkenCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
jppintoAuthor Commented:
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
 
RiteshShahCommented:
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
 
jppintoAuthor Commented:
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
 
RiteshShahCommented:
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
 
jppintoAuthor Commented:
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
 
jppintoAuthor Commented:
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
 
RiteshShahCommented:
open your SSMS and execute following script than show me its results.


USE yourDataBaseName;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
0
 
jppintoAuthor Commented:
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
 
RiteshShahCommented:
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
 
jppintoAuthor Commented:
I've runned a Query in VS and I get this error!
error.jpg
0
 
RiteshShahCommented:
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
 
jppintoAuthor Commented:
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
 
RiteshShahCommented:
that's ok, glad to help you. don't hesitate to ping here again.
0
 
jppintoAuthor Commented:
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
 
RiteshShahCommented:
well, in that case, you should do everything from scratch. :(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.