Using various sources (see list at the bottom) I have created two stored procedures that allow me to do what I need using something similar to:
EXEC sp_copy_ls_database
@restore_job='LSRestore_server1_LOG_SHIPPED_DB',
@database='LOG_SHIPPED_DB',
@dest_database='LOG_SHIPPED_DB_COPY'
Where:
@restore_job - the name of the SQL agent job that is responsible for applying shipped logs on the log shipped database (the stored procedures disables this job while the clone is made and then enables it again).
@database - the name of the log shipped database to clone.
@dest_database - the name to give the cloned database.
Note: I don't think this is 100% fool proof as I believe SQL Server starts processing an agent job before updating it's internal tables and therefore sp_wait_for_running_job may miss the start of the job. Depending on the amount of data change and log ship frequency, this should still be fine for non-production databases.
Here's the SQL to create the stored procedures:
USE master
--SP to determine if a named SQL Agent job is running and wait for it to complete if it is
CREATE PROCEDURE sp_wait_for_running_job
@JobName as varchar(100) = Null
AS
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
-- Am I sysadm and who am I
DECLARE @is_sysadmin AS INT
DECLARE @job_owner AS NVARCHAR
SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()
-- Loop until the <job> stops
WHILE 1=1
BEGIN
-- Clear out temporary table on each pass
TRUNCATE TABLE #xp_results
-- Populate the temporary table with job details from Extended SP xp_sqlagent_enum_jobs
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
-- If the <job> is running then break out of the loop, otherwise wait for 10 secs
IF (SELECT COUNT(*)
FROM #xp_results xpr
LEFT OUTER JOIN msdb.dbo.sysjobsteps sjs ON ((xpr.job_id = sjs.job_id) AND (xpr.current_step = sjs.step_id)),
msdb.dbo.sysjobs_view sjv
WHERE (sjv.job_id = xpr.job_id)
AND step_name = @JobName and job_state = 1) = 0
BREAK
ELSE
WAITFOR DELAY '00:00:10'
END
--SP to clone a log shipped database to a new read/write database without impacting log shipping
CREATE PROCEDURE sp_copy_ls_database
@restore_job AS NVARCHAR(128),
@database AS NVARCHAR(128),
@dest_database AS NVARCHAR(128)
AS
DECLARE @mdf_location AS NVARCHAR(max)
DECLARE @log_location AS NVARCHAR(max)
DECLARE @dest_mdf_location AS NVARCHAR(max)
DECLARE @dest_log_location AS NVARCHAR(max)
--Get log shipped database MDF file location
SELECT @mdf_location=physical_name FROM sys.master_files
WHERE database_id=(SELECT DB_ID(@database)) AND type=0
--Get log shipped database LOG file location
SELECT @log_location=physical_name FROM sys.master_files
WHERE database_id=(SELECT DB_ID(@database)) AND type=1
--Determine destination database MDF file
SET @dest_mdf_location=LEFT(@mdf_location,LEN(@mdf_location) - charindex('\',reverse(@mdf_location),1) + 1) + @dest_database + '.mdf'
--Determine destination database LOG file
SET @dest_log_location=LEFT(@log_location,LEN(@log_location) - charindex('\',reverse(@log_location),1) + 1) + @dest_database + '_log.ldf'
DECLARE @q AS NVARCHAR(max)
DECLARE @cmd AS VARCHAR(255)
--Disable the LS restore job
RAISERROR('Disabling log shipping restore job: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC msdb.dbo.sp_update_job @job_name=@restore_job, @enabled=0
--Wait for the job to finish running if is running
RAISERROR('Waiting for log shipping restore job to complete: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC sp_wait_for_running_job @restore_job
--Set log shipped DB offline
RAISERROR('Setting log shipped database %s to offline mode', 0, 1, @database ) WITH NOWAIT
SET @q = 'ALTER DATABASE [' + @database + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
EXEC(@q)
--Drop placeholder DB if it already exists
IF EXISTS(SELECT name FROM sys.databases WHERE name = @dest_database)
BEGIN
RAISERROR('Dropping existing database: %s', 0, 1, @dest_database) WITH NOWAIT
SET @q = 'DROP DATABASE ' + @dest_database
EXEC(@q)
END
--Create new placeholder DB
RAISERROR('Creating placeholder database: %s' , 0, 1, @dest_database) WITH NOWAIT
SET @q = 'CREATE DATABASE ' + @dest_database + ' ON (NAME = ' + @dest_database + ', FILENAME = ''' + @dest_mdf_location + ''') LOG ON (NAME = ' + @dest_database + '_log, FILENAME = ''' + @dest_log_location + ''')'
EXEC(@q)
--Set placeholder DB offline
RAISERROR('Setting placeholder database %s to offline mode', 0, 1, @database) WITH NOWAIT
SET @q = 'ALTER DATABASE [' + @dest_database + '] SET OFFLINE WITH ROLLBACK IMMEDIATE'
EXEC(@q)
--Delete placeholder database files
SET @cmd = 'DEL "' + @dest_mdf_location + '"'
RAISERROR('Deleting placeholder database MDF file: %s', 0, 1, @dest_mdf_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;
SET @cmd = 'DEL "' + @dest_log_location + '"';
RAISERROR('Deleting placeholder database LOG file: %s', 0, 1, @dest_log_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;
--Copy the log shipped DB's MDF file
SET @cmd = 'COPY "' + @mdf_location + '" "' + @dest_mdf_location + '"';
RAISERROR('Copying the log shipped database MDF file to: %s', 0, 1, @dest_mdf_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;
--Copy the log shipped DB's LOG file
SET @cmd = 'COPY "' + @log_location + '" "' + @dest_log_location + '"';
RAISERROR('Copying the log shipped database LOG file to: %s', 0, 1, @dest_log_location) WITH NOWAIT
EXEC master.dbo.xp_cmdshell @cmd;
--Put the copied database online
SET @q = 'ALTER DATABASE [' + @dest_database + '] SET ONLINE'
RAISERROR('Setting copied database %s to online mode', 0, 1, @dest_database) WITH NOWAIT
EXEC(@q)
--Set log shipped DB online
SET @q = 'ALTER DATABASE [' + @database + '] SET ONLINE'
RAISERROR('Setting log shipped database %s to online mode', 0, 1, @database) WITH NOWAIT
EXEC(@q)
--Enable the LS restore job
RAISERROR('Enabling log shipping restore job: %s', 0, 1, @restore_job) WITH NOWAIT
EXEC msdb.dbo.sp_update_job @job_name=@restore_job, @enabled=1
Sources:
- General overview: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/backup-and-copy-warm-standby-log-shipped/
- Path from file name:
https://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path
- Wait for a job to complete:
http://www.sqlservercentral.com/Forums/Topic99401-8-1.aspx#top
- Set DB offline immediately
https://stackoverflow.com/questions/808232/extreme-wait-time-when-taking-a-sql-server-database-offline
