May 27, 2005 at 11:52 am
Simply stated, I want to have a copy of a production database for users to do development work with. I don't have Enterprise Edition, so log shipping is out of the question. In the past, I've copied a recent backup into the development database. The users are now stating that this is not good enough, as they need current up-to-date data in the development copy. I was thinking of using Transactional Replication for this task, but I need to be sure that changes to the delvelopment database do not get replicated back to the production database. My question is then, is this a possible method I could use, or is there something else I could try that I haven't thought of?
May 30, 2005 at 8:00 am
This was removed by the editor as SPAM
May 30, 2005 at 9:36 pm
By default, transactional replication does not send changes back to the publishing database.
However, allowing users to update the replica may introduce problems that you will need to deal with. Transaction replication checks each command it applies to the replica for errors and, by default, will cause an error (with the distribution agent) if a command cannot be completed. This can occur if a record is deleted by one of the development users and then replication tries to update it. Have a look at topic "Handling Agent Errors" in books online.
May 31, 2005 at 10:09 am
Very good point. I didn't even think of that, but it makes perfect sense. Thanks.
June 7, 2005 at 9:32 am
If having a read-only database serves your needs, you could implement a poor-man's log shipping by restoring transaction logs to the server using the STANDBY clause on the development server. Here is stored proc I use that searches the supplied directory for transaction logs and applys them in order. This only works if the names of the files can be sorted so it applys them in proper order for example:
DBNAME_tlog_200506070500.TRN
DBNAME_tlog_200506070530.TRN
--script below
use master;
go
if object_id ('dbo.sp_Restore_TLog_Standby') is not null
drop proc dbo.sp_Restore_TLog_Standby
GO
CREATE PROC dbo.sp_Restore_TLog_Standby
(@dbname sysname,
@filepath nvarchar(255),
@undofile nvarchar(128)
) AS
declare @filesig nvarchar(20), @text nvarchar(2000)
--Set filename search string
set @filesig = @dbname +'_tlog'
--read directory into a temp table
create table #dir ( dirtext nvarchar(255))
set @text = 'dir '+@filepath+'*.trn'
insert #dir
exec xp_cmdshell @text
delete #dir where dirtext not like '%'+@filesig+'%' or dirtext is null
--loop through all transaction logs.
DECLARE xcursor CURSOR LOCAL FOR
select
SUBSTRING(dirtext,
CHARINDEX(@filesig,dirtext),
LEN(dirtext)-PATINDEX(dirtext, @filesig))
from #dir
order by SUBSTRING(dirtext,
CHARINDEX(@filesig,dirtext),
LEN(dirtext)-PATINDEX(dirtext, @filesig))
OPEN xcursor
FETCH NEXT FROM xcursor into @text
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('RESTORE LOG '+@dbname+'
FROM DISK = '''+@filepath+@text+'''
WITH STANDBY = '''+@undofile+'''')
--delete on successful restore
if @@error = 0
begin
set @text = 'del '+@filepath+@text
exec xp_cmdshell @text
end
FETCH NEXT FROM xcursor into @text
END
CLOSE xcursor
DEALLOCATE xcursor
drop table #dir
GO
June 8, 2005 at 9:15 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply