Saturday, February 25, 2012

copy a database from server to development

Dear all,
i want to copy a database from the sql enterprise server into my developer e
dition for development, what is the most suitable methods for this?Hi,
There are 2 options:-
1. Detach and Attach database
2. Backup and Restore the database
1. Detach and Attach
a. Use SP_DETACH_DB to detach the MDF and LDF files
b. Copy the files to Destination server
c. Use SP_ATTACH_DB to attach the databases
(Refer books online for information)
2. Backup and Restore
a. Backup the source databases using BACKUP DATABASE command
b. Copy the .BAK files to destination
c. Restore the database using RESTORE DATABASE command
If you have only 1 or 2 users to syncronize then use the procedure
sp_change_users_login (Refer books online)
Thanks
Hari
MCDBA
"ken" <anonymous@.discussions.microsoft.com> wrote in message
news:4D480E63-1DB3-43B0-814D-A1E4CF4DCD21@.microsoft.com...
> Dear all,
> i want to copy a database from the sql enterprise server into my developer
edition for development, what is the most suitable methods for this?
>|||Another one options : in EM, there is "Copy database wizard".
Bye
"Hari" <hari_prasad_k@.hotmail.com> ha scritto nel messaggio
news:OnzpUDJTEHA.504@.TK2MSFTNGP11.phx.gbl...
> Hi,
> There are 2 options:-
> 1. Detach and Attach database
> 2. Backup and Restore the database
> 1. Detach and Attach
> a. Use SP_DETACH_DB to detach the MDF and LDF files
> b. Copy the files to Destination server
> c. Use SP_ATTACH_DB to attach the databases
> (Refer books online for information)
> 2. Backup and Restore
> a. Backup the source databases using BACKUP DATABASE command
> b. Copy the .BAK files to destination
> c. Restore the database using RESTORE DATABASE command
>
> If you have only 1 or 2 users to syncronize then use the procedure
> sp_change_users_login (Refer books online)
> Thanks
> Hari
> MCDBA
>
>
> "ken" <anonymous@.discussions.microsoft.com> wrote in message
> news:4D480E63-1DB3-43B0-814D-A1E4CF4DCD21@.microsoft.com...
developer[vbcol=seagreen]
> edition for development, what is the most suitable methods for this?
>|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.databasejournal.com/feat...cle.php/2228611
Transferring Logins
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Andrew J. Kelly
SQL Server MVP
"ken" <anonymous@.discussions.microsoft.com> wrote in message
news:4D480E63-1DB3-43B0-814D-A1E4CF4DCD21@.microsoft.com...
> Dear all,
> i want to copy a database from the sql enterprise server into my developer
edition for development, what is the most suitable methods for this?
>|||This is really a subset of detach/attach, but with the
benefit of a graphical interface. Actually this is useful
to know because it means it is also an out-of-hours
procedure, unlike backup/restore.
Regards,
Paul Ibison

No comments:

Post a Comment