USE master
GO
-- Drop the snapshot if it exists
if db_id('Accounting_snapshot') is not null
begin
alter database Accounting set single_user with rollback immediate;
drop database Accounting_snapshot;
end
go
--
if db_id('Accounting') is not null
begin
alter database Accounting set single_user with rollback immediate;
drop database Accounting;
end
go
create database Accounting;
go
alter authorization on database::Accounting to sa;
use Accounting;
go
create queue q;
create service s on queue q ([DEFAULT]);
go
-- 1 - Create a snapshot
CREATE DATABASE [Accounting_Snapshot] ON ( NAME = Accounting, FILENAME = 'c:\projects\Accounting_Snapshot_dat.ss') AS SNAPSHOT OF Accounting
go
--
ALTER DATABASE [Accounting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
-- 2 - Restore with ENABLE_BROKER from the snapshot while in single user mode
USE MASTER;
RESTORE DATABASE Accounting FROM DATABASE_SNAPSHOT = 'Accounting_Snapshot' WITH ENABLE_BROKER;
USE Accounting;
go
--
declare @h uniqueidentifier;
begin dialog conversation @h
from service [s]
to service N's'
with encryption = off;
send on conversation @h ('Hello, World');
go
-- Give the queue a bit of time to process
WAITFOR DELAY '0:00:02';
GO
-- Take a look after sending a message
SELECT * FROM sys.transmission_queue; -- 1 row, transmission_status = "...single user ..."
GO
-- Retry same multiuser with ENABLE_BROKER
USE master
GO
ALTER DATABASE Accounting SET MULTI_USER, ENABLE_BROKER WITH NO_WAIT
GO
use Accounting;
go
-- wait for the message delivery
WAITFOR (receive cast(message_body as varchar(8000)), * from q), timeout 60;
GO
-- Look at xmit queue
USE Accounting;
SELECT * FROM sys.transmission_queue; -- 1 row, no error status.
GO
-- Look again at xmit queue, in 10 seconds
waitfor delay '00:00:10';
USE Accounting;
SELECT * FROM sys.transmission_queue; -- row is gone
GO