copyonly testdb

gistfile1.txt

use master
go

if exists (select * from sys.databases where name='RestoreCopyOnly')
begin
	ALTER DATABASE RestoreCopyOnly SET single_USER with rollback immediate
	drop database RestoreCopyOnly
end
go


create database RestoreCopyOnly
go

alter database RestoreCopyOnly set recovery full
go

use RestoreCopyOnly
go

IF EXISTS (SELECT * FROM SYS.tables WHERE name='steps')
begin
	drop table steps
end
go

create table steps(
step integer,
dt datetime2
);
go

declare @i integer

set @i=0


backup database [RestoreCopyOnly] to disk='c:\dbatools\RestoreCopyOnly\RestoreCopyOnly.bak'

while (@i<10)
begin
insert into steps values (@i, getdate())
select @i=@i+1
waitfor delay '00:00:10'
end
backup log [RestoreCopyOnly] to disk='c:\dbatools\RestoreCopyOnly\RestoreCopyOnly_1.trn'


while (@i<20)
begin
insert into steps values (@i,getdate())
select @i=@i+1
waitfor delay '00:00:10'
end

backup log [RestoreCopyOnly] to disk='c:\dbatools\RestoreCopyOnly\RestoreCopyOnly_2.trn'

while (@i<30)
begin
insert into steps values (@i,getdate())
select @i=@i+1
waitfor delay '00:00:10'
end


--backup log [RestoreCopyOnly] to disk='c:\dbatools\RestoreCopyOnly\RestoreCopyOnly_3.trn'

backup database [RestoreCopyOnly] to disk='c:\dbatools\RestoreCopyOnly\RestoreCopyOnly2.bak' with copy_only

set @i=0
while (@i<10)
begin
insert into steps values (@i, getdate())
select @i=@i+1
waitfor delay '00:00:10'
end
backup log [RestoreCopyOnly] to disk='c:\dbatools\RestoreCopyOnly\RestoreCopyOnly_21.trn'

while (@i<20)
begin
insert into steps values (@i,getdate())
select @i=@i+1
waitfor delay '00:00:10'
end


backup log [RestoreCopyOnly] to disk='c:\dbatools\RestoreCopyOnly\RestoreCopyOnly_22.trn'

while (@i<30)
begin
insert into steps values (@i,getdate())
select @i=@i+1
waitfor delay '00:00:10'
end


backup log [RestoreCopyOnly] to disk='c:\dbatools\RestoreCopyOnly\RestoreCopyOnly_23.trn'