Moving SQL Databases from one server to another..

[skurpyun]

Hello once again,
If anyone could help me with this i would be highly greatful.
In a few days I have to move data in an SQL Server database from one remote computer to another remote computer. To do this I intend on creating backup files of the entire database (of the original sql server) and then doing a restore on the second remote server. But then it occured to me, how am i going to transfer the tables names along with the corresponding indexes and relationships intact? Should I just generate a .sql file from the original sql server, run that on the new one, and then go through the restore process? If anyone could help me with this or suggest a better way of doing this, or just tell me i'm nuts, your help would be appreciated. Thank you.

 

[ecobb]

It's my understanding that if you use a backup, everything will already be there (tables, indexes, stored procedures, etc...) I would suggest trying it on a development server first, just to make sure.

 

[skurpyun]

i will.
I was just thinking it through beforehand, and realized that i might run into some problems. Thought I would check to see if anyone here had any suggestions. Thanks.

-sk

 

[ecobb]

Sorry my last post was short, my boss walked in...
I actually do this a lot (restoring from backups). I will take last night's backup of a database and put it on my Dev. machine each morning so I can work with it. I haven't noticed any problems with the database tables and indexes. Although I will point out that when you restore the database on a "new" server, if the users you have for that database are not already on the "new" SQL Server you will need to delete the users from the database, recreate them on the server, and add them back to the database.

Hope this helps

 

[tlbroadbent]

Check the following link for recommendations and potential problems when moving databases to a new server.

http://support.microsoft.com/default.aspx?kbid=224071

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[chenthorn]

One method that has worked for me in the past is to simply detach the database, copy it over to the destination server, then reattach. this works good if you can handle the downtime while the file copies.

 

[skurpyun]

Sweet!! Thanks ya'll, i think i have a game plan now. Thanks for responding both with your thoughts on transfering the database and for the links to sites about doing this. It has been very helpful.....now if only i knew enough to start returning the favour by posting my own solutions to other peoples questions....one day...one day....