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: http://www.sql-server-performance.com/jc_transferring_statistics.asp HTH Luis Martin
Moderator
SQL-Server-Performance.com 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’
RESTORE DATABASE TestDB
FROM DISK = ‘c:Northwind.bak’
WITH MOVE ‘Northwind’ TO ‘c: est estdb.mdf’,
MOVE ‘Northwind_log’ TO ‘c: est estdb.ldf’
GO

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=’

‘ />]

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. MeanOldDBA [email protected] 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 "http://support.microsoft.com/default.aspx?scid=kb;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?

]]>