I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!
Vik!Hi,
Basically, you can't. Not in one fell swoop. What you can do, however, is to detach the database from one server, copy it--along with the log file--to the new server, and attach it. But you'll need to recreate the logins, because the internal SIDs--security IDs--on the new server won't match those on the old.
If permissions are assigned to database roles, those will be intact.
Don|||If you have Enterprise Manager, you can just create a sql script, and script that database, and it's users.|||What donkiely says in true. However, I'd say the best techniques are...
1. Backup database on server 1, restore it on server 2, re-wire logins
2. Use SQLs Transformation Wizards from Enterprise Server...ok for small databases but network intensive. Best to sort out the security issue first for this one, that way the transform will do the bulk (ahem) of the work for you.
3. As previous posters says, script all the meta data, then copy the data. Basically the same issues as (2) but at least you've got some nice scripts to reapply if your db gets into trouble.|||KraGiE,
Just keep in mind that scripting the database won't copy the data. You'd then have to use DTS or some other means to copy it.
Don
No comments:
Post a Comment