February 26, 2003 at 10:49 pm
Just wondering what other people do out in the world with copying a database from one server to another - for development and testing purposes.
I currently know of three methods:
1) DTS - which constantly breaks for various reasons and takes a lifetime to complete
2) Snapshot replication - which we haven't tried as of yet
3) Restoring from backup - a method that I use for my own testing but trashes the logins
What other methods are there?
Cheers
M
February 27, 2003 at 1:42 am
Depending on the size of the database and if it's possible to take the DB offline I often use sp_detach_DB, then copy the files and sp_attach_db to connect the Db to the server(s) again.
M
[font="Verdana"]Markus Bohse[/font]
February 27, 2003 at 1:45 am
Yeah but I think that trashes all of the logins.
It's actually a reasonable database, around 15 gigabytes, although copying a file of that size is no problem in this day and age.
February 27, 2003 at 2:37 am
I use both 1) and 3). MarkusB is right it depends on size. If the db is minimal and no FK's then I use DTS otherwise it depends on whether I have time to create manual DTS.
What do you mean about about restores trashing logins. A DB restore will not trash a login but will have the users from source DB and access may not be allowed (and the user invisible to EM) if the logins have different sid. I always make sure that the login on the dest server has the same sid so that when the restore is made the user matches the login and all security is kept.
Far away is close at hand in the images of elsewhere.
Anon.
February 27, 2003 at 5:17 am
David,
how do you make sure that the sid is the same on the two machines?
I just adapt the user sids with sp_change_users_login
February 27, 2003 at 6:36 am
I normally do this before I create/restore the DB.
I use
select 'sp_addlogin '''+name+''',@sid=',sid from syslogins where name = 'loginname'
to get the sid from the host machine.
and then create the login on the dest machine by using the results of the query (minus the spaces between = and the sid
sp_addlogin 'loginname',@sid=0x...
Far away is close at hand in the images of elsewhere.
Anon.
February 27, 2003 at 8:27 am
The latter approach makes sense. Nice part is once you've got the sids matching, future restores dont require that step (unless you have new logins of course).
Andy
February 27, 2003 at 7:01 pm
Yeah that's what I meant, the SIDs get mismatched. I like that idea of generating a script to pick up the SIDs.
Although I feel that this idea only suits a fully interactive session with the DBA for pulling data from Production to Development. (I'll take it further and give it a try later today)
For something fully automated, would Snapshot replication (on a 15GB database) be a better way of doing things? We have a main database and a secondary server which we use for the "big" queries. The data only needs to be updated weekly, so would a snapshot on a weekly basis be the way to go or would [queued] transactional be the go? We want to fully minimise the impact on the production server as it's getting old and slow enough as it is!
(I have spent countless hours cleaning up ADO code and streamlining SP's and picking through traces... the poor ol' server just can't handle much more)
Thanks
Mark
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply