Fastest Way To Make Test Copy of Database

Some of our developers want to automate some system for bringing over a copy
of the production database to their workstations to use for test purposes.
I want to break this down into two separate questions:
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
Assuming the log file is backed up using the native Windows SQL backup
utility on the production database, what is the procedure to apply those
changes to the database on another machine?
2) How can this whole process be automated, so that the programmer could
just run a script and expect that 10 minutes later his database is up to the
current level
Are there applications that would help to automate this whole process?
Will
Internet: westes AT earthbroadcast.comIf the database is small you can write custom script to drop the developer
database and recreate it thru script given to developer.
step 1: Share the network location of backup on both machines
step 2: create linked server on the developer machine
step 3: write custom script on developer machine that go thru cursor like th
is
a) restore database with norecovery
b) declare c1 INSENSITIVE cursor for
select bmf.physical_device_name file_name
from sql2.msdb.dbo.backupset bs
inner join sql2.msdb.dbo.backupmediafamily bmf
on bmf.media_set_id = bs.media_set_id
where upper(bs.database_name) = upper(@.orig_db_name)
and backup_start_date > (getdate()-2)
and type ='L'
order by bs.backup_start_date
FOR READ ONLY
c) go thru cursor and apply all the Tlogs
EXEC ('RESTORE LOG ' + @.restore_db_name + ' from DISK =' + '''' + @.id +
'''' + ' with standby=' + '''' + 'c:\mssql\data\MSSQL\BACKUP\UNDO_' +
@.restore_db_name + '.DAT' + '''')
At the end restore database with recovery and you will have a DB as of last
tlog backup.
I do what you asked in question 2 but DB is open only as read only and
gets sync from production by custom scripts listed above.
If you have Enterprise edition you do not need to write the custom script as
it is in built in maintenance wizard
" wrote:

> Some of our developers want to automate some system for bringing over a co
py
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log file
s
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to t
he
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>|||Will
1) What is the most efficient way to bring the current database state over
to test machines? What I would like to find is a way to apply just the
change logs to the database currently on the developer's machine. There
just aren't that many changes being made to the database, and the log files
are small.
A. Detach\Attach database or more secure BACKUP /RESTORE Database
Also take a look at this article
http://www.sql-server-performance.c...og_shipping.asp
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:ex%232INmkFHA.576@.TK2MSFTNGP15.phx.gbl...
> Some of our developers want to automate some system for bringing over a
> copy
> of the production database to their workstations to use for test purposes.
> I want to break this down into two separate questions:
> 1) What is the most efficient way to bring the current database state over
> to test machines? What I would like to find is a way to apply just the
> change logs to the database currently on the developer's machine. There
> just aren't that many changes being made to the database, and the log
> files
> are small.
> Assuming the log file is backed up using the native Windows SQL backup
> utility on the production database, what is the procedure to apply those
> changes to the database on another machine?
> 2) How can this whole process be automated, so that the programmer could
> just run a script and expect that 10 minutes later his database is up to
> the
> current level
> Are there applications that would help to automate this whole process?
> --
> Will
> Internet: westes AT earthbroadcast.com
>
>