· 9 years ago · Jan 06, 2017, 11:04 AM
1EXEC sp_copy_ls_database
2 @restore_job='LSRestore_server1_LOG_SHIPPED_DB',
3 @database='LOG_SHIPPED_DB',
4 @dest_database='LOG_SHIPPED_DB_COPY'
5
6USE master
7--SP to determine if a named SQL Agent job is running and wait for it to complete if it is
8CREATE PROCEDURE sp_wait_for_running_job
9 @JobName as varchar(100) = Null
10AS
11CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
12 last_run_date INT NOT NULL,
13 last_run_time INT NOT NULL,
14 next_run_date INT NOT NULL,
15 next_run_time INT NOT NULL,
16 next_run_schedule_id INT NOT NULL,
17 requested_to_run INT NOT NULL, -- BOOL
18 request_source INT NOT NULL,
19 request_source_id sysname COLLATE database_default NULL,
20 running INT NOT NULL, -- BOOL
21 current_step INT NOT NULL,
22 current_retry_attempt INT NOT NULL,
23 job_state INT NOT NULL)
24
25-- Am I sysadm and who am I
26DECLARE @is_sysadmin AS INT
27DECLARE @job_owner AS NVARCHAR
28SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
29SELECT @job_owner = SUSER_SNAME()
30
31-- Loop until the <job> stops
32WHILE 1=1
33BEGIN
34 -- Clear out temporary table on each pass
35 TRUNCATE TABLE #xp_results
36
37 -- Populate the temporary table with job details from Extended SP xp_sqlagent_enum_jobs
38 INSERT INTO #xp_results
39 EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
40
41 -- If the <job> is running then break out of the loop, otherwise wait for 10 secs
42 IF (SELECT COUNT(*)
43 FROM #xp_results xpr
44 LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
45 msdb.dbo.sysjobs_view sjv
46 WHERE (sjv.job_id = xpr.job_id)
47 AND step_name = @JobName and job_state = 1) = 0
48 BREAK
49 ELSE
50 WAITFOR DELAY '00:00:10'
51END
52
53
54--SP to clone a log shipped database to a new read/write database without impacting log shipping
55CREATE PROCEDURE sp_copy_ls_database
56 @restore_job AS NVARCHAR(128),
57 @database AS NVARCHAR(128),
58 @dest_database AS NVARCHAR(128)
59AS
60DECLARE @mdf_location AS NVARCHAR(max)
61DECLARE @log_location AS NVARCHAR(max)
62DECLARE @dest_mdf_location AS NVARCHAR(max)
63DECLARE @dest_log_location AS NVARCHAR(max)
64
65--Get log shipped database MDF file location
66SELECT @mdf_location=physical_name FROM sys.master_files
67WHERE database_id=(SELECT DB_ID(@database)) AND type=0
68
69--Get log shipped database LOG file location
70SELECT @log_location=physical_name FROM sys.master_files
71WHERE database_id=(SELECT DB_ID(@database)) AND type=1
72
73--Determine destination database MDF file
74SET @dest_mdf_location=LEFT(@mdf_location,LEN(@mdf_location) - charindex('',reverse(@mdf_location),1) + 1) + @dest_database + '.mdf'
75
76--Determine destination database LOG file
77SET @dest_log_location=LEFT(@log_location,LEN(@log_location) - charindex('',reverse(@log_location),1) + 1) + @dest_database + '_log.ldf'
78
79DECLARE @q AS NVARCHAR(max)
80DECLARE @cmd AS VARCHAR(255)
81
82--Disable the LS restore job
83RAISERROR('Disabling log shipping restore job: %s', 0, 1, @restore_job) WITH NOWAIT
84EXEC msdb.dbo.sp_update_job @job_name=@restore_job, @enabled=0
85--Wait for the job to finish running if is running
86RAISERROR('Waiting for log shipping restore job to complete: %s', 0, 1, @restore_job) WITH NOWAIT
87EXEC sp_wait_for_running_job @restore_job
88--Set log shipped DB offline
89RAISERROR('Setting log shipped database %s to offline mode', 0, 1, @database ) WITH NOWAIT
90SET @q = 'ALTER DATABASE [' + @database + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
91EXEC(@q)
92
93--Drop placeholder DB if it already exists
94IF EXISTS(SELECT name FROM sys.databases WHERE name = @dest_database)
95BEGIN
96 RAISERROR('Dropping existing database: %s', 0, 1, @dest_database) WITH NOWAIT
97 SET @q = 'DROP DATABASE ' + @dest_database
98 EXEC(@q)
99END
100
101--Create new placeholder DB
102RAISERROR('Creating placeholder database: %s' , 0, 1, @dest_database) WITH NOWAIT
103SET @q = 'CREATE DATABASE ' + @dest_database + ' ON (NAME = ' + @dest_database + ', FILENAME = ''' + @dest_mdf_location + ''') LOG ON (NAME = ' + @dest_database + '_log, FILENAME = ''' + @dest_log_location + ''')'
104EXEC(@q)
105
106--Set placeholder DB offline
107RAISERROR('Setting placeholder database %s to offline mode', 0, 1, @database) WITH NOWAIT
108SET @q = 'ALTER DATABASE [' + @dest_database + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
109EXEC(@q)
110
111--Delete placeholder database files
112SET @cmd = 'DEL "' + @dest_mdf_location + '"'
113RAISERROR('Deleting placeholder database MDF file: %s', 0, 1, @dest_mdf_location) WITH NOWAIT
114EXEC master.dbo.xp_cmdshell @cmd;
115SET @cmd = 'DEL "' + @dest_log_location + '"';
116RAISERROR('Deleting placeholder database LOG file: %s', 0, 1, @dest_log_location) WITH NOWAIT
117EXEC master.dbo.xp_cmdshell @cmd;
118
119--Copy the log shipped DB's MDF file
120SET @cmd = 'COPY "' + @mdf_location + '" "' + @dest_mdf_location + '"';
121RAISERROR('Copying the log shipped database MDF file to: %s', 0, 1, @dest_mdf_location) WITH NOWAIT
122EXEC master.dbo.xp_cmdshell @cmd;
123
124--Copy the log shipped DB's LOG file
125SET @cmd = 'COPY "' + @log_location + '" "' + @dest_log_location + '"';
126RAISERROR('Copying the log shipped database LOG file to: %s', 0, 1, @dest_log_location) WITH NOWAIT
127EXEC master.dbo.xp_cmdshell @cmd;
128
129--Put the copied database online
130SET @q = 'ALTER DATABASE [' + @dest_database + '] SET ONLINE'
131RAISERROR('Setting copied database %s to online mode', 0, 1, @dest_database) WITH NOWAIT
132EXEC(@q)
133
134--Set log shipped DB online
135SET @q = 'ALTER DATABASE [' + @database + '] SET ONLINE'
136RAISERROR('Setting log shipped database %s to online mode', 0, 1, @database) WITH NOWAIT
137EXEC(@q)
138
139--Enable the LS restore job
140RAISERROR('Enabling log shipping restore job: %s', 0, 1, @restore_job) WITH NOWAIT
141EXEC msdb.dbo.sp_update_job @job_name=@restore_job, @enabled=1