Best way to setup a test/developer server. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Best way to setup a test/developer server.

Here is my task:
Setup a server that allows developers to test their apps against a pretty realistic environment. I looked into log shipping but NO GOOD since the backup server does not allow updates. Tried setting up a DTS pkg to copy objects but it was less than 100% reliable (I found some data inconsistencies). So I was thinking of automating a restore of the live dbs onto the development server on a nightly schedule.
Before I get started looking into how to do it I wonder if anyone had a better idea/suggestions since it seems to be a pretty common task. Thanks in advance My live and test server environment:
Windows 2000 server
SQL 2000 8.00.760 (I believe SP3a)

Check: HTH Luis Martin
Moderator All postings are provided “AS IS” with no warranties for accuracy.
Thanks Luis for the link but in my case I need to have an exact copy of the data on the live servers. The test/developer and live servers will be on the same switch too so data transfer should be pretty fast. Size in this specific case is not a big concern. I was thinking about scheduling a job somewhat like this: RESTORE FILELISTONLY
FROM DISK = ‘c:Northwind.bak’
FROM DISK = ‘c:Northwind.bak’
WITH MOVE ‘Northwind’ TO ‘c: est estdb.mdf’,
MOVE ‘Northwind_log’ TO ‘c: est estdb.ldf’
Well I dont see why that wouldnt work, but remember development databases are used for development. Although you may want a copy of live data shipped to your dev server every night, remember that schema changes are often made on dev server before being deployed out to live. Restoring a backup would overwrite all changes to that database on the dev server, make sure your devs are aware of this !
What the developers really want is to play with the data that is on the live server but since this is not possible I’m just trying to provide them with a pretty close copy of it in an environment where they can "GO CRAZY."[:0] I probably should have mentioned that I wear many different hats and I’ll be a developer playing with data myself at times [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]
I don’t suppose you have a SAN do you? <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> I would schedule the restore if I were you.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Now that my restore is working does anyone have a suggestion on how to transfer logins? I try to have the process to be automatic but unfortunately as per ";EN-US;246133" the DTS pkg fails (DTS pkgs don’t seem to be reliable in many other situations) and the stored procedure approach (which I like) is MANUAL!!! Any other idea on how to keep the logins synchronized that does NOT require user intervention?