-1

I need to take adhoc copies of a development database (on SQL Server 2005) to another SQL Server and be able to read/write to that cloned database. As the SQL Servers are in different data centres, copying the 20GB database I'm working with and then restoring is pretty slow. I have therefore opted to use log shipping to get an up to date copy of the data on the destination server. I now need a method to clone the log shipped read only database to a database I can read/write.

How can I copy a log shipped database to writeable database and not break the log shipping chain?

0

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:

  1. General overview: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/backup-and-copy-warm-standby-log-shipped/
  2. Path from file name: https://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path
  3. Wait for a job to complete: http://www.sqlservercentral.com/Forums/Topic99401-8-1.aspx#top
  4. Set DB offline immediately https://stackoverflow.com/questions/808232/extreme-wait-time-when-taking-a-sql-server-database-offline

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.