Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE master
- GO
- -- <gn>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
- -- </gn>
- 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
- -- <gn> 1 - Create a snapshot
- CREATE DATABASE [Accounting_Snapshot] ON ( NAME = Accounting, FILENAME = 'c:\projects\Accounting_Snapshot_dat.ss') AS SNAPSHOT OF Accounting
- go
- -- </gn>
- ALTER DATABASE [Accounting] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- go
- -- <gn> 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
- -- </gn>
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement