March 26, 2008 at 12:05 pm
I am a newly acting DBA for my company and we are moving to a new building. With the move we are changing are processes so that developers will develop their applications on their local machines, then roll forward their changes to a "test" environment using a test server. I will need to develop a test SqlServer. I want the data in the test server to be accurate up to the previous night. I also want the database objects on the test server to be an exact copy of the production objects. The developers should be able to make their database objects and test their code. Any ideas on a best practice for doing something like this would be much appreciated. Thanks in advance.
April 5, 2008 at 10:34 pm
If your requirement would have been to refresh data on test server everynight then I would have suggested Snapshot replication. But as I understand you also want to make sure newly created object to be made available on test server everynight. Hence, backup database on source server and restore it on test server will be good for you to meet requirement.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
April 14, 2008 at 11:21 am
I agree replication is wrong and backup(PROD)-restore(TEST) is correct, but consider ...
1. the disk geometry on PROD (data=E:, tlog=F:, dumps=G:) may differ from TEST (data=tlog=D:)
2. the maintplan on PROD probably embeds datastamp in the .BAK files
3. you don't want to have linked-server (unless crippled) from TEST into PROD
so command might need to be
RESTORE mydb from disk='\\PROD\G$\mydb_db_200804140600.BAK'
WITH MOVE 'mydb_data' to 'D:\data\mydb_data.mdf',
WITH MOVE 'mydb_log' to 'D:\data\mydb_log.ldf'
so I recommend that you
1. use special-named linked-server (eg PROD_msdb) with credential very limited access rights
2. write SQLAgent job on TEST to query PROD to discover dump filename (see snippet below)
HTH
Dick
declare @physical_device_name nvarchar(260)
selecttop 1 @physical_device_name='\\PROD\'
+substring(physical_device_name,1,1) +'$'
+substring(physical_device_name,3,999)
fromPROD_msdb.msdb.dbo.backupset BS
joinPROD_msdb.msdb.dbo.backupmediafamily BMF on BMF.media_set_id=BS.media_set_id
wheredatabase_name=N'mydb'
andtype= 'D'
anddevice_type= 2
order by BS.backup_set_id desc
select @physical_device_name-- DEBUG
restore filelistonly from DISK=@physical_device_name-- obtain LogicalName, size
restore database mydb from disk=@physical_device_name
withmove 'mydb_Data' to 'D:\data\mydb_Data.mdf'
,move 'mydb_Log' to 'E:\data\mydb_Log.ldf'
,stats=10, replace
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply