Moving DB from one server to another? 
Author Message
 Moving DB from one server to another?
I need to move a database from a developmental server and
put it in production on a production machine.  I had
planned on using the sp_detach_db, transferring the file
and then using sp_attach_db on the new machine.

Is there any reason why I should NOT do it this way?
If the developmental db includes user rights (both domain
users and local SQL Server users) do the domain referenced
users transfer without modification?  If the local users
do not exist will this fail or will it just complain about
the users not existing?



Sat, 17 Jan 2004 23:38:46 GMT
 Moving DB from one server to another?

I prefer BACKUP and RESTORE.  This method is 100% reliable.

You might also want to read this article:
http://support.microsoft.com/support/kb/articles/Q246/1/33.ASP

And reading up on sp_change_users_login (within Books Online) would not be a
bad idea either.

--
Keith
==============
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I need to move a database from a developmental server and
> put it in production on a production machine.  I had
> planned on using the sp_detach_db, transferring the file
> and then using sp_attach_db on the new machine.

> Is there any reason why I should NOT do it this way?
> If the developmental db includes user rights (both domain
> users and local SQL Server users) do the domain referenced
> users transfer without modification?  If the local users
> do not exist will this fail or will it just complain about
> the users not existing?



Sat, 17 Jan 2004 23:53:17 GMT
 Moving DB from one server to another?
These articles should help you out with the move of the
database(s) and the related security issues.

Q224071 INF: Moving SQL Server 7.0 Databases to a New
Location,
http://support.microsoft.com/support/kb/articles/q224/0/71.
asp
Q221465 INF: Using the WITH MOVE Option with the RESTORE
Statement,
http://support.microsoft.com/support/kb/articles/q221/4/65.
asp
Q274188 PRB: "Troubleshooting Orphaned Users" Topic in BOL
Incomplete,
http://support.microsoft.com/support/kb/articles/q274/1/88.
asp
Q168001 PRB: User Logon and/or Permission Errors After
Restoring Dump,
http://support.microsoft.com/support/kb/articles/q168/0/01.
asp
Q246133 INF: Transfer Logins, Passwords Between SQL Server
7.0 and 2000,
http://support.microsoft.com/support/kb/articles/q246/1/33.
asp
Q298758 PRB: Using Auto_Fix w/sp_change_users_login Leaves
Vulnerability,
http://support.microsoft.com/support/kb/articles/q298/7/58.
asp
Q274463 INF: Understanding and Troubleshooting the Copy
Database Wizard,
http://support.microsoft.com/support/kb/articles/q274/4/63.
asp

Quote:
>-----Original Message-----
>I need to move a database from a developmental server and
>put it in production on a production machine.  I had
>planned on using the sp_detach_db, transferring the file
>and then using sp_attach_db on the new machine.

>Is there any reason why I should NOT do it this way?
>If the developmental db includes user rights (both domain
>users and local SQL Server users) do the domain
referenced
>users transfer without modification?  If the local users
>do not exist will this fail or will it just complain
about
>the users not existing?
>.



Sun, 18 Jan 2004 01:30:32 GMT
 Moving DB from one server to another?
Another site for you to check.
I've used this before and it works like a charm

Good Luck....watch out for any data that is server name
dependent.

http://www.swynk.com/friends/sharma/copydatabase.asp

Quote:
>-----Original Message-----
>I need to move a database from a developmental server and
>put it in production on a production machine.  I had
>planned on using the sp_detach_db, transferring the file
>and then using sp_attach_db on the new machine.

>Is there any reason why I should NOT do it this way?
>If the developmental db includes user rights (both domain
>users and local SQL Server users) do the domain
referenced
>users transfer without modification?  If the local users
>do not exist will this fail or will it just complain
about
>the users not existing?
>.



Sun, 18 Jan 2004 04:01:32 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. moving db from one server to another...

2. Problems Moving DB from one AS2K Server to Another

3. Moving entire DB from one server to another

4. Moving a DB From One Server To Another

5. How to move a DB from one server to another

6. Moving record from one DB to another DB

7. Moving alerts from one server to another server in sql server 6.5

8. Query from One DB Servers from Another DB Server

9. Moving SQL Server Agent Jobs from one server to another

10. MOVE everything on one SQL server to a new SQL server

11. Creating cubes on one MS olap server and moving to a different server

12. Moving Records from One DB to another


 
Powered by phpBB® Forum Software