SQLServerWiki

“The Only Thing That Is Constant Is Change”

AlwaysON configuration

Posted by database-wiki on June 28, 2018

SELECT
AVGrp.name as group_name,
AVGRep.replica_server_name as replica_name,
AVGRep.endpoint_url,
AVGRep.availability_mode_desc,
AVGRep.failover_mode_desc,
AVGRep.seeding_mode_desc as seeding_mode
FROM sys.availability_replicas as AVGRep
JOIN sys.availability_groups as AVGrp
ON AVGRep.group_id = AVGrp.group_id;

Posted in Uncategorized | Leave a Comment »

AlwaysON failover history

Posted by database-wiki on June 17, 2018

Get-winEvent -ComputerName <alwaysON-Listener-Name> -filterHashTable @{logname =’Microsoft-Windows-FailoverClustering/Operational’; id=1641}| ft -AutoSize -Wrap

Note: Input only the -ComputerName.

Posted in Uncategorized | Leave a Comment »

Jobs and Schedules

Posted by database-wiki on June 12, 2018

SELECT   distinct [JobName] = [jobs].[name]

 

,[schedule].schedule_ID

 

,[schedule].[Name] as ScheduleName

 

,[Occurs] =

 

CASE [schedule].[freq_type]

 

WHEN   1 THEN ‘Once’

 

WHEN   4 THEN ‘Daily’

 

WHEN   8 THEN ‘Weekly’

 

WHEN  16 THEN ‘Monthly’

 

WHEN  32 THEN ‘Monthly relative’

 

WHEN  64 THEN ‘When SQL Server Agent starts’

 

WHEN 128 THEN ‘Start whenever the CPU(s) become idle’

 

ELSE ”

 

END

 

,[Frequency] =

 

CASE [schedule].[freq_subday_type]

 

WHEN 1 THEN ‘Occurs once at ‘ +

 

STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’)

 

WHEN 2 THEN ‘Occurs every ‘ +

 

CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ‘ Seconds(s) between ‘ +

 

STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ +

 

STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’)

 

WHEN 4 THEN ‘Occurs every ‘ +

 

CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ‘ Minute(s) between ‘ +

 

STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ +

 

STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’)

 

WHEN 8 THEN ‘Occurs every ‘ +

 

CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ‘ Hour(s) between ‘ +

 

STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’) + ‘ and ‘ +

 

STUFF(STUFF(RIGHT(‘000000’ + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ‘:’), 3, 0, ‘:’)

 

ELSE ”

 

END

 

FROM   [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK)

 

LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK)

 

ON [jobs].[job_id] = [jobschedule].[job_id]

 

LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK)

 

ON [jobschedule].[schedule_id] = [schedule].[schedule_id]

 

INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK)

 

ON [jobs].[category_id] = [categories].[category_id]

 

LEFT OUTER JOIN

 

(

 

SELECT        [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + (([run_duration] % 10000) / 100 * 60) + ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])

 

FROM    [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)

 

WHERE   [step_id] = 0

 

GROUP BY [job_id]

 

) AS [jobhistory]

 

ON [jobhistory].[job_id] = [jobs].[job_id]

 

Where [jobs].[enabled]=1 and [schedule].[enabled]=1

 

 

–and [jobs].[name] not in (‘syspolicy_purge_history’,’sp_purge_jobhistory’,’Cycle Error Log’,’sp_delete_backuphistory’)

 

— and [jobs].[name] like ‘NEW DATA%’

 

–order by [schedule].schedule_id asc

Posted in SQL SERVER, Uncategorized | Leave a Comment »

Cutom SQL Server Logshipping for migrating database to AWS EC2 Instance.

Posted by database-wiki on April 2, 2018

Scenario:
========

=> We have planned to migrate our MSSQL Databases from local datacentre to Amazon AWS.
=> The existing instance contained 800 databases and overall size of the databases were 10TB.
=> We are moving from MS SQL Server 2008 R2 to MS SQL Server 2014 without fallback plan which is a huge risk and hence the huge visibility.

Challenges:
==========

We were not inclined to use the SQL Server logshipping because:

1. We want all the databases to be sync at the same point in time.
2. We did not want to manage 800*3=2400 jobs created in primary while setting up the logshipping (backup,copy, Alert jobs).
3. Any new databases added should automatically be part of the logshipping and any databases removed should be automatically removed from logshipping as well.

Solution:
========

Prerequisites:

a. Enable SP_CMDSHELL on both the instance.
sp_configure ‘show advanced options’,1
go
reconfigure with override
go

sp_configure ‘xp_cmdshell’,1
go
reconfigure with override
go

b. Create operation ‘DBA’.
c. Creating required files and folder.
d. Create folder: D:\MSSQL\LogShipping.
e. Create Files: lastrun.txt, total.txt inside folder D:\MSSQL\LogShipping.

We decide to create a custom logshipping using scripts.

1. We created two databases called utility in primary and secondary database to host our Stored Procedures.

USE [master]

GO

IF EXISTS(select * from sys.databases where name=’Utility’)

DROP DATABASE [Utility]

GO

USE [master]

GO

CREATE DATABASE [Utility]

ON PRIMARY

( NAME = N’Utility’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\DATA\Utility.mdf’ , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N’Utility_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2\MSSQL\DATA\Utility_log.ldf’ , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

2. We created following stored procedures in primary Utility database.

a. Filecopy Store Procedure.

USE [Utility]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbsp_FileCopyInsert]’) AND type in (N’P’, N’PC’))

DROP PROCEDURE [dbo].[dbsp_FileCopyInsert]

GO

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

Create PROCEDURE [dbo].[dbsp_FileCopyInsert]

@DBName SYSNAME,

@PathFrom VARCHAR(255),

@FileName VARCHAR(255),

@FileSubPath VARCHAR(255) = NULL

AS

—————————————————————————————————

— Name: dbsp_FileCopyInsert

— exec Utility.dbo.dbsp_FileCopyInsert ‘Utility’, ‘J:\MSSQL\Backup\UserDatabases\’, ‘Utility_backup_200709130100.BKP’, ‘Utility’

— Parameters:

— @DBName = The name of the database that the file belongs to. Used for

— FileCopyExclude.

— @PathFrom = The base path of the file to copy, this corresponds to the

— dbo.FileCopyPath.PathFrom record to determine the PathTo

— @FileName = The filename of the file to copy

— @FileSubPath = The subdirectory located in @PathFrom from which to copy the

— @FileName to. This is also appended to dbo.FileCopyPath.PathTo

— If NULL then just @PathFrom / PathTo is used.

— Returns: None.

— Description: Schedules a File to be copied. The @PathFrom is used to match up the record to a PathTo

— location in dbo.FileCopyPath. If no PathFrom currently exists a new record will be added,

— and then dbsp_FileCopy will notify of the issue when it is ran (result code of 1 will be

— returned in this case).

—————————————————————————————————

SET NOCOUNT ON

DECLARE @res INT

DECLARE @fileCopyPathID INT

DECLARE @errorStr VARCHAR(512)

SET @res = 0

— check db is not to be excluded

IF EXISTS(SELECT 1 FROM dbo.FileCopyExclude WITH (NOLOCK) WHERE DBName = @DBName) BEGIN

— db is in exclude list

RETURN 2

END

— get FileCopyPath to use

SELECT

@fileCopyPathID = FileCopyPathID

FROM

dbo.FileCopyPath

WHERE

PathFrom = @PathFrom

IF (@@ROWCOUNT = 0) BEGIN

— failed to find a record, add a new one (NOTE: it will be missing the PathTo record)

SET @res = 1

INSERT INTO dbo.FileCopyPath (PathFrom, PathTo, RowCreatedDate, RowModifiedDate)

VALUES (@PathFrom, NULL, getdate(), getdate())

IF (@@ERROR <> 0) BEGIN

— failed to insert record

SET @errorStr = ‘dbsp_FileCopyInsert ERROR: Failed to add record to FileCopyPath, @PathFrom = [‘ + ISNULL(@PathFrom, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

RETURN -1

END

— get inserted id

SET @fileCopyPathID = SCOPE_IDENTITY()

END

— add record to FileCopy table to schedule copy for later

INSERT INTO dbo.FileCopy (FileCopyPathID, FileName, FileSubPath, ProcessGUID, ProcessDate, CopiedDate, RowCreatedDate)

VALUES (@fileCopyPathID, @FileName, @FileSubPath, NULL, NULL, NULL, getdate())

IF (@@ERROR <> 0) BEGIN

— failed to insert record

SET @errorStr = ‘dbsp_FileCopyInsert ERROR: Failed to add record to FileCopy, @fileCopyPathID = [‘ + ISNULL(CAST(@fileCopyPathID AS VARCHAR), ‘NULL’) + ‘], @FileName = [‘ + ISNULL(@FileName, ‘NULL’) + ‘], @FileSubPath = [‘ +ISNULL(@FileSubPath, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

RETURN -1

END

RETURN @res

GO

b. Backup stored procedure.

USE [Utility]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbsp_Backup]’) AND type in (N’P’, N’PC’))

DROP PROCEDURE [dbo].[dbsp_Backup]

GO

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE PROCEDURE [dbo].[dbsp_Backup]

@Path VARCHAR(255),

@DBTypes VARCHAR(50) = ‘user’,

@Extension CHAR(3) = ‘BAK’,

@CreateSubDir BIT = 1,

@Verify BIT = 0,

@SingleDB SYSNAME = NULL,

@FileCopy BIT = 1,

@CommonBackupDate DATETIME = NULL,

@Compress BIT = 1,

@SingleDBFileName VARCHAR(255) = NULL OUTPUT

AS

—————————————————————————————————

— Name: dbsp_Backup

— exec Utility.dbo.dbsp_Backup ‘J:\MSSQL\Backup\UserDatabases\’, ‘user’, ‘BAK’, 1, 0, NULL, 1, NULL, 1

— exec Utility.dbo.dbsp_Backup ‘J:\MSSQL\Backup\SystemDatabases\’, ‘system’, ‘BAK’, 1, 0, NULL, 1, NULL, 1

— exec Utility.dbo.dbsp_Backup ‘J:\MSSQL\Backup\UserDatabases\’, ‘single’, ‘BAK’, 1, 0, ‘Utility’, 1, NULL, 1

— Parameters:

— @Path = The path to backup databases to

— @DBTypes = The types of databases to backup, either, ‘all’, ‘user’ (default),

— ‘system’, or ‘single’

— @Extension = The extension to add to all backup files (default=BAK)

— @CreateSubDir = If set (1) then put each db backup in it’s own directory, otherwise

— put all backups in @Path

— @Verify = Verify the integrity of the backups

— @SingleDB = The single database to backup, only used for @DBTypes = ‘single’

— @FileCopy = Schedule the log backups for copying (see dbsp_FileCopy)

— @CommonBackupDate = Use to override the default behaviour (when NULL) of using a

— different date (starting time of each backup) as part of the

— filename (in the form of yyyymmddhhmm).

— NOTE: Usefull if you want all the backup files to have the same

— date in their file names.

— @Compress = If set (1) then compress the backups (unless a database is encrypted)

— @SingleDBFileName = OUTPUT, The file name of the database backup, only used for

— @DBTypes = ‘single’

— Returns: None.

—————————————————————————————————

SET NOCOUNT ON

DECLARE @res INT

DECLARE @database TABLE (

id INT IDENTITY(1,1),

dbname SYSNAME,

encrypted BIT

)

DECLARE @on INT

DECLARE @max INT

DECLARE @dbname SYSNAME

DECLARE @encrypted BIT

DECLARE @now CHAR(12)

DECLARE @bkupPath VARCHAR(255)

DECLARE @file VARCHAR(255)

DECLARE @filename VARCHAR(255)

DECLARE @desc VARCHAR(200)

DECLARE @fileSubPath SYSNAME

DECLARE @cmd VARCHAR(500)

DECLARE @result INT

DECLARE @errorStr VARCHAR(512)

SET @res = 0

— get databases to backup

INSERT INTO @database (dbname, encrypted)

SELECT

name,

is_encrypted

FROM

master.sys.databases

WHERE

— all databases

(@DBTypes = ‘all’ AND name <> ‘tempdb’)

— system databases

OR (@DBTypes = ‘system’ AND name IN (‘master’, ‘model’, ‘msdb’))

— single database

OR (@DBTypes = ‘single’ AND name = @SingleDB)

— user databases (default)

OR (ISNULL(@DBTypes,”) NOT IN (‘all’, ‘system’, ‘single’) AND name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’,’Utility’,’ASPState’))

ORDER BY

name

— loop on each database to back up

SELECT @max = COUNT(*) FROM @database

SET @on = 1

WHILE (@on <= @max) BEGIN

— get database on

SELECT @dbname = dbname, @encrypted = encrypted FROM @database WHERE id = @on

— get the current date (or the common date) in the form of yyyymmddhhmm

SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), ISNULL(@CommonBackupDate, GETDATE()), 120), ‘-‘, ”), ‘ ‘, ”), ‘:’, ”)

SET @bkupPath = CASE WHEN @CreateSubDir = 1 THEN @Path + @dbname + ‘\’ ELSE @Path END

SET @file = @dbname + ‘_backup_’ + @now + ‘.’ + @Extension

SET @filename = @bkupPath + @file

SET @desc = ‘Full backup of ‘ + @dbname + ‘ – ‘ + CONVERT(VARCHAR(50), GETDATE())

— check dir existence if different directories for each db or first iteration on

— same backup path for all

IF (@CreateSubDir = 1 OR @on = 1) BEGIN

— check to see if the backup directory exists

SET @cmd = ‘dir ‘ + @bkupPath

EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

IF (@result <> 0) BEGIN

— directory does not exist, create it

SET @cmd = ‘mkdir ‘ + @bkupPath

— create the directory

EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

IF (@result <> 0) BEGIN

— failed to create the directory (report and continue)

SET @errorStr = ‘Failed to create directory = [‘ + ISNULL(@bkupPath, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -1

— loop

SET @on = @on + 1

CONTINUE

END

END

END

— backup the database

IF (@encrypted = 1 OR @Compress = 0) BEGIN

— no compression

BACKUP DATABASE @dbname

TO DISK = @filename

WITH

INIT,

NAME = @dbname,

DESCRIPTION = @desc,

NOFORMAT

END ELSE BEGIN

— with compression

BACKUP DATABASE @dbname

TO DISK = @filename

WITH

INIT,

COMPRESSION,

NAME = @dbname,

DESCRIPTION = @desc,

NOFORMAT

END

IF (@@ERROR > 0) BEGIN

— failed to backup database (report and continue)

SET @errorStr = ‘Failed to backup database = [‘ + ISNULL(@dbname, ‘NULL’) + ‘], filename = [‘ + ISNULL(@filename, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -2

END ELSE BEGIN

— database backed up

IF (@Verify = 1) BEGIN

— verify backup

RESTORE VERIFYONLY FROM DISK = @filename

IF (@@ERROR > 0) BEGIN

— failed to verify backup (report and continue)

SET @errorStr = ‘Failed to verify backup for database = [‘ + ISNULL(@dbname, ‘NULL’) + ‘], filename = [‘ + ISNULL(@filename, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -3

END

END

IF (@FileCopy = 1) BEGIN

— schedule file for copy

SET @fileSubPath = CASE WHEN @CreateSubDir = 1 THEN @dbname ELSE NULL END

EXEC @result = Utility.dbo.dbsp_FileCopyInsert @dbname, @Path, @file, @fileSubPath

IF (@result <> 0 AND @result <> 2) BEGIN

— failed to schedule file for copy (report and continue)

SET @errorStr = ‘Failed to schedule database backup file copy for database = [‘ + ISNULL(@dbname, ‘NULL’) + ‘], result = [‘ + ISNULL(CAST(@result AS VARCHAR), ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -4

END

END

END

— take a breather for 2 seconds before starting on the next backup

IF (@on < @max) BEGIN

WAITFOR DELAY ’00:00:02′

END

— loop

SET @on = @on + 1

END

— single database backup

IF (@DBTypes = ‘single’) BEGIN

— set @SingleDBFileName param to file name (if successfully backed up)

IF (@res = 0) BEGIN

SET @SingleDBFileName = @file

END ELSE BEGIN

SET @SingleDBFileName = NULL

END

END

RETURN @res

GO

c. bacup log

USE [Utility]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbsp_BackupLog]’) AND type in (N’P’, N’PC’))

DROP PROCEDURE [dbo].[dbsp_BackupLog]

GO

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

Create PROCEDURE [dbo].[dbsp_BackupLog]

@Path VARCHAR(255),

@DBTypes VARCHAR(50) = ‘user’,

@Extension CHAR(3) = ‘TRN’,

@CreateSubDir BIT = 1,

@Verify BIT = 0,

@SingleDB SYSNAME = NULL,

@FileCopy BIT = 1,

@Compress BIT = 1

AS

—————————————————————————————————

— Name: dbsp_BackupLog

— exec Utility.dbo.dbsp_BackupLog ‘J:\MSSQL\Backup\UserDatabases\’, ‘user’, ‘TRN’, 1, 0, NULL, 1, 1

— exec Utility.dbo.dbsp_BackupLog ‘J:\MSSQL\Backup\SystemDatabases\’, ‘system’, ‘TRN’, 1, 0, NULL, 1, 1

— exec Utility.dbo.dbsp_BackupLog ‘J:\MSSQL\Backup\UserDatabases\’, ‘single’, ‘TRN’, 1, 0, ‘Utility’, 1, 1

— Parameters:

— @Path = The path to backup logs to

— @DBTypes = The types of databases to backup logs for, either, ‘all’,

— ‘user’ (default), ‘system’, or ‘single’

— @Extension = The extension to add to all log backup files (default=TRN)

— @CreateSubDir = If set (1) then put each log backup in it’s own directory, otherwise

— put all backups in @Path

— @Verify = Verify the integrity of the log backups

— @SingleDB = The single database to backup, only used for @DBTypes = ‘single’

— @FileCopy = Schedule the log backups for copying (see dbsp_FileCopy)

— @Compress = If set (1) then compress the log backups (unless a database is encrypted)

— Returns: None.

— Description: Backups all the specified type of databases logs using LiteSpeed. Excludes SIMPLE

— recovery model databases.

—————————————————————————————————

SET NOCOUNT ON

DECLARE @res INT

DECLARE @database TABLE (

id INT IDENTITY(1,1),

dbname SYSNAME,

encrypted BIT

)

DECLARE @on INT

DECLARE @max INT

DECLARE @dbname SYSNAME

DECLARE @encrypted BIT

DECLARE @now CHAR(12)

DECLARE @bkupPath VARCHAR(255)

DECLARE @file VARCHAR(255)

DECLARE @filename VARCHAR(255)

DECLARE @desc VARCHAR(200)

DECLARE @fileSubPath SYSNAME

DECLARE @cmd VARCHAR(500)

DECLARE @result INT

DECLARE @errorStr VARCHAR(512)

SET @res = 0

— get databases to backup logs for

INSERT INTO @database (dbname, encrypted)

SELECT

name,

is_encrypted

FROM

master.sys.databases

WHERE

— all databases

((@DBTypes = ‘all’ AND name <> ‘tempdb’)

— system databases

OR (@DBTypes = ‘system’ AND name IN (‘master’, ‘model’, ‘msdb’))

— single database

OR (@DBTypes = ‘single’ AND name = @SingleDB)

— user databases (default)

OR (ISNULL(@DBTypes,”) NOT IN (‘all’, ‘system’, ‘single’) AND name NOT IN (‘master’, ‘model’, ‘msdb’, ‘tempdb’,’Utility’)))

— exclude SIMPLE recovery mode

AND recovery_model <> 3

ORDER BY

name

— loop on each database to back up log for

SELECT @max = COUNT(*) FROM @database

SET @on = 1

WHILE (@on <= @max) BEGIN

— get database on

SELECT @dbname = dbname, @encrypted = encrypted FROM @database WHERE id = @on

— verify database has a full backup (otherwise backup log will fail) i.e. new database,

— or just switched from SIMPLE to FULL recovery model

IF EXISTS (SELECT last_log_backup_lsn

FROM sys.database_recovery_status

WHERE database_id = db_id(@dbname) AND last_log_backup_lsn IS NULL) BEGIN

— no database backup (report and continue)

SET @errorStr = ‘Failed since BACKUP LOG cannot be performed because there is no current database backup for database = [‘ + ISNULL(@dbname, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -4

— loop

SET @on = @on + 1

CONTINUE

END

— get the current date in the form of yyyymmddhhmm

SET @now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), ‘-‘, ”), ‘ ‘, ”), ‘:’, ”)

SET @bkupPath = CASE WHEN @CreateSubDir = 1 THEN @Path + @dbname + ‘\’ ELSE @Path END

SET @file = @dbname + ‘_backup_’ + @now + ‘.’ + @Extension

SET @filename = @bkupPath + @file

SET @desc = ‘Transaction log backup of ‘ + @dbname + ‘ – ‘ + CONVERT(VARCHAR(50), GETDATE())

— check dir existence if different directories for each db or first iteration on

— same backup path for all

IF (@CreateSubDir = 1 OR @on = 1) BEGIN

— check to see if the backup directory exists

SET @cmd = ‘dir ‘ + @bkupPath

EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

IF (@result <> 0) BEGIN

— directory does not exist, create it

SET @cmd = ‘mkdir ‘ + @bkupPath

— create the directory

EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

IF (@result <> 0) BEGIN

— failed to create the directory (report and continue)

SET @errorStr = ‘Failed to create directory = [‘ + ISNULL(@bkupPath, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -1

— loop

SET @on = @on + 1

CONTINUE

END

END

END

— backup the database log

IF (@encrypted = 1 OR @Compress = 0) BEGIN

— no compression

BACKUP LOG @dbname

TO DISK = @filename

WITH

NAME = @dbname,

DESCRIPTION = @desc

END ELSE BEGIN

— with compression

BACKUP LOG @dbname

TO DISK = @filename

WITH

COMPRESSION,

NAME = @dbname,

DESCRIPTION = @desc

END

IF (@@ERROR > 0) BEGIN

— failed to backup database log (report and continue)

SET @errorStr = ‘Failed to backup log for database = [‘ + ISNULL(@dbname, ‘NULL’) + ‘], filename = [‘ + ISNULL(@filename, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -2

END ELSE BEGIN

— log backed up

IF (@Verify = 1) BEGIN

— verify log backup

RESTORE VERIFYONLY FROM DISK = @filename

IF (@@ERROR > 0) BEGIN

— failed to verify backup (report and continue)

SET @errorStr = ‘Failed to verify log backup for database = [‘ + ISNULL(@dbname, ‘NULL’) + ‘], filename = [‘ + ISNULL(@filename, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -3

END

END

IF (@FileCopy = 1) BEGIN

— schedule file for copy

SET @fileSubPath = CASE WHEN @CreateSubDir = 1 THEN @dbname ELSE NULL END

EXEC @result = Utility.dbo.dbsp_FileCopyInsert @dbname, @Path, @file, @fileSubPath

IF (@result <> 0 AND @result <> 2) BEGIN

— failed to schedule file for copy (report and continue)

SET @errorStr = ‘Failed to schedule log backup file copy for database = [‘ + ISNULL(@dbname, ‘NULL’) + ‘], result = [‘ + ISNULL(CAST(@result AS VARCHAR), ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -4

END

END

END

— loop

SET @on = @on + 1

END

RETURN @res

GO

d. Backup Delete Stored Procedure.

USE [Utility]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbsp_BackupDel]’) AND type in (N’P’, N’PC’))

DROP PROCEDURE [dbo].[dbsp_BackupDel]

GO

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE PROCEDURE [dbo].[dbsp_BackupDel]

@Path VARCHAR(255),

@Retention INT = 3,

@Extension CHAR(3) = ‘BKP’,

@Depth INT = 2

AS

—————————————————————————————————

— Name: dbsp_BackupDel

— exec Utility.dbo.dbsp_BackupDel ‘J:\MSSQL\Backup\UserDatabases\’, 3, ‘BKP’, 2

— exec Utility.dbo.dbsp_BackupDel ‘J:\MSSQL\Backup\SystemDatabases\’, 3, ‘BKP’, 2

— Parameters:

— @Path = The path to delete backup files from

— @Retention = The number of days to keep backup files (default=3)

— @Extension = The extension of backup files to delete (default=BKP)

— @Depth = The depth of subdirectories to recurse where 1 is only @Path dir,

— 2 is @Path dir and its immediate subdirectories, etc.. (default=2)

— Returns: None.

— Description: Delete all the files of previous backups in @Path (and possibly subdirectores)

— that end with the @Extension and contain yyyymmddhhmm in the name that is not

— more recent then @Rentention days.

—————————————————————————————————

SET NOCOUNT ON

DECLARE @res INT

DECLARE @cmd VARCHAR(500)

DECLARE @result INT

DECLARE @filesSearch TABLE (

filename VARCHAR(512)

)

DECLARE @retentionDate CHAR(12)

DECLARE @files TABLE (

id INT IDENTITY(1,1),

filename VARCHAR(512)

)

DECLARE @on INT

DECLARE @max INT

DECLARE @filename VARCHAR(255)

DECLARE @errorStr VARCHAR(512)

SET @res = 0

— get all files/folders in given path (recurse subdirectories)

SET @cmd = ‘dir ‘ + @Path + ‘ /B /S’

INSERT INTO @filesSearch (filename)

EXEC @result = master.dbo.xp_cmdshell @cmd

IF (@result <> 0) BEGIN

— dir command failed, exit

PRINT ‘Dir failed for the given path = [‘ + ISNULL(@Path, ‘NULL’) + ‘], exiting!’

RETURN -1

END

— get the retention date in the form of yyyymmddhhmm

SET @retentionDate = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE() – @Retention, 120), ‘-‘, ”), ‘ ‘, ”), ‘:’, ”)

— get the files to be deleted

INSERT INTO @files (filename)

SELECT

filename

FROM

@filesSearch

WHERE

— ends with specified extension

filename LIKE ‘%.’ + @Extension

— contains yyyymmddhhmm before extension

AND filename LIKE ‘%20[0-9][0-9][0-1][0-9][0-3][0-9][0-2][0-9][0-5][0-9].___’

— older then retention days

AND LEFT(RIGHT(filename, 16), 12) < @retentionDate

— only go to depth subdirectories

AND filename NOT LIKE @Path + ‘%’ + REPLICATE(‘\%’, @Depth)

— loop on each backup file to delete

SELECT @max = COUNT(*) FROM @files

SET @on = 1

WHILE (@on <= @max) BEGIN

— get file on

SELECT @filename = filename FROM @files WHERE id = @on

— delete the backup file on

PRINT ‘Deleting backup file = [‘ + ISNULL(@filename, ‘NULL’) + ‘].’

SET @cmd = ‘del ‘ + @filename + ‘ /Q /F’

EXEC @result = master.dbo.xp_cmdshell @cmd, NO_OUTPUT

IF (@result <> 0) BEGIN

— failed to delete a file (report and continue)

SET @errorStr = ‘Failed to delete backup file = [‘ + ISNULL(@filename, ‘NULL’) + ‘]!’

RAISERROR (@errorStr, 9, 1)

SET @res = -2

END

— loop

SET @on = @on + 1

END

RETURN @res

GO

e. Create the below SQL Agent job in Primary.

USE [msdb]
GO

/****** Object: Job [MJ User Databases Backup Transaction Log] Script Date: 4/2/2018 4:33:17 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 4/2/2018 4:33:17 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’MJ User Databases Backup Transaction Log’,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’Back up all user databases logs’,
@category_name=N’Database Maintenance’,
@owner_login_name=N’sa’,
@notify_email_operator_name=N’DBA’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Back Up All User Databases Logs] Script Date: 4/2/2018 4:33:17 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Back Up All User Databases Logs’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’DECLARE @result INT

DECLARE @errorStr VARCHAR(512)

exec @result = Utility.dbo.dbsp_BackupLog

@Path = ”H:\MSSQL\Backup\UserDatabases\”,

@DBTypes = ”user”,

@Extension = ”TRN”,

@CreateSubDir = 1,

@Verify = 1,

@SingleDB = NULL,

— @FileCopy = 1,

@FileCopy = 0,

@Compress = 1

IF (@result <> 0) BEGIN

SET @errorStr = ”Backup Log Failed with result = [” + ISNULL(CAST(@result AS VARCHAR), ”NULL”) + ”]!”

RAISERROR (@errorStr, 16, 1)

END ELSE BEGIN

PRINT ”Backup Log completed.”

END

‘,
@database_name=N’master’,
@output_file_name=N’H:\MSSQL\Logs\MJ User Databases Backup Transaction Log.log’,
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Delete User db log Backups older than 9 days] Script Date: 4/2/2018 4:33:17 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Delete User db log Backups older than 9 days’,
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’DECLARE @result INT

DECLARE @errorStr VARCHAR(512)

exec @result = Utility.dbo.dbsp_BackupDel ”H:\MSSQL\Backup\UserDatabases\”, 9, ”TRN”, 2

IF (@result <> 0) BEGIN

SET @errorStr = ”Backup Delete Failed with result = [” + ISNULL(CAST(@result AS VARCHAR), ”NULL”) + ”]!”

RAISERROR (@errorStr, 16, 1)

END ELSE BEGIN

PRINT ”Backup Delete completed.”

END’,
@database_name=N’master’,
@output_file_name=N’H:\MSSQL\Logs\MJ User Databases Backup Transaction Log.log’,
@flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Daily – Every 15 minutes’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=15,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20100920,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N’dbce2f6b-c69d-4a91-8f95-10c95877955f’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

2. On Secondary server in Amazon AWS EC2.

a. Create RestoreBackupsNoRecovery store procedure in on secondary.

USE [Utility]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbsp_RestoreBackupsNoRecovery]’) AND type in (N’P’, N’PC’))

DROP PROCEDURE [dbo].[dbsp_RestoreBackupsNoRecovery]

GO

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE PROCEDURE [dbo].[dbsp_RestoreBackupsNoRecovery]

@BackupPath VARCHAR(255),

@Extension CHAR(3) = ‘BAK’,

@ServerDataPath VARCHAR(255) = ‘D:\MSSQL12.MSSQLSERVER\MSSQL\DATA’,

@ServerLogPath VARCHAR(255) = ‘F:\MSSQL12.MSSQLSERVER\MSSQL\Log’

AS

—————————————————————————————————

— Name: dbsp_RestoreBackupsNoRecovery

— exec Utility.dbo.dbsp_RestoreBackupsNoRecovery ‘I:\SkyLogShipping’

— Created By: Balaji Mani

— Created On: August 2017

— Parameters:

— @BackupPath The path to search and restore the backups from

— @Extension The extension of DB backup files (default=BAK)

— @ServerDataPath The path where DB data files should go

— @ServerLogPath The path where DB log files should go

— Returns: None.

— Description: Searches for all files with the specified extension in the specified folder and subfolders.

— Restores DBs with MOVE to the server path. Leaves the DB in RESTORING state.

—————————————————————————————————

SET NOCOUNT ON

SET NOCOUNT ON

DECLARE @res INT

DECLARE @result INT

DECLARE @cmd VARCHAR(4096)

DECLARE @filesSearch TABLE (

filename VARCHAR(512)

)

DECLARE @BackupHeader TABLE (

BackupName nvarchar(128) NULL,

BackupDescription nvarchar(255) NULL,

BackupType smallint NULL,

ExpirationDate datetime NULL,

Compressed tinyint NULL,

Position smallint NULL,

DeviceType tinyint NULL,

UserName nvarchar(128) NULL,

ServerName nvarchar(128) NULL,

DatabaseName nvarchar(128) NULL,

DatabaseVersion int NULL,

DatabaseCreationDate datetime NULL,

BackupSize numeric(20,0) NULL,

FirstLSN numeric(25,0) NULL,

LastLSN numeric(25,0) NULL,

CheckpointLSN numeric(25,0) NULL,

DatabaseBackupLSN numeric(25,0) NULL,

BackupStartDate datetime NULL,

BackupFinishDate datetime NULL,

SortOrder smallint NULL,

CodePage smallint NULL,

UnicodeLocaleId int NULL,

UnicodeComparisonStyle int NULL,

CompatibilityLevel tinyint NULL,

SoftwareVendorId int NULL,

SoftwareVersionMajor int NULL,

SoftwareVersionMinor int NULL,

SoftwareVersionBuild int NULL,

MachineName nvarchar(128) NULL,

Flags int NULL,

BindingID uniqueidentifier NULL,

RecoveryForkID uniqueidentifier NULL,

Collation nvarchar(128) NULL,

FamilyGUID uniqueidentifier NULL,

HasBulkLoggedData bit NULL,

IsSnapshot bit NULL,

IsReadOnly bit NULL,

IsSingleUser bit NULL,

HasBackupChecksums bit NULL,

IsDamaged bit NULL,

BeginsLogChain bit NULL,

HasIncompleteMetaData bit NULL,

IsForceOffline bit NULL,

IsCopyOnly bit NULL,

FirstRecoveryForkID uniqueidentifier NULL,

ForkPointLSN decimal(25, 0) NULL,

RecoveryModel nvarchar(60) NULL,

DifferentialBaseLSN decimal(25, 0) NULL,

DifferentialBaseGUID uniqueidentifier NULL,

BackupTypeDescription nvarchar(60) NULL,

BackupSetGUID uniqueidentifier NULL,

CompressedBackupSize binary(8) NULL,

Containment tinyint not NULL,

KeyAlgorithm nvarchar(32),

EncryptorThumbprint varbinary(20),

EncryptorType nvarchar(32)

)

DECLARE @BackupFileList TABLE (

LogicalName nvarchar(128),

PhysicalName nvarchar(260),

[Type] char(1),

FileGroupName nvarchar(128),

Size numeric(20,0),

MaxSize numeric(20,0),

FileID bigint,

CreateLSN numeric(25,0),

DropLSN numeric(25,0) NULL,

UniqueID uniqueidentifier,

ReadOnlyLSN numeric(25,0) NULL,

ReadWriteLSN numeric(25,0) NULL,

BackupSizeInBytes bigint,

SourceBlockSize int,

FileGroupID int,

LogGroupGUID uniqueidentifier NULL,

DifferentialBaseLSN numeric(25,0) NULL,

DifferentialBaseGUID uniqueidentifier NULL,

IsReadOnly bit,

IsPresent bit,

TDEThumbprint varbinary(32)

)

SET @cmd = ‘dir ‘ + @BackupPath + ‘\*.BAK /B /S’

INSERT INTO @filesSearch (filename)

EXEC @result = master.dbo.xp_cmdshell @cmd

IF (@result=1) BEGIN

— no .BAK files found; exit

PRINT ‘No .BAK files found at the given path = [‘ + ISNULL(@BackupPath, ‘NULL’) + ‘], exiting!’

RETURN 0

END

IF (@result<>0) BEGIN

— dir command failed, exit

PRINT ‘Dir failed for the given path = [‘ + ISNULL(@BackupPath, ‘NULL’) + ‘], exiting!’

RETURN -1

END

Declare @backupFileName varchar(255)

SELECT TOP 1 @backupFileName=filename FROM @filesSearch

WHILE @backupFileName IS NOT NULL BEGIN

SELECT @cmd=’RESTORE HEADERONLY FROM DISK=N”’+@backupFileName+”’ WITH FILE=1′

INSERT INTO @BackupHeader EXEC (@cmd)

SELECT @cmd=’RESTORE FILELISTONLY FROM DISK=N”’+@backupFileName+”’ WITH FILE=1′

INSERT INTO @BackupFileList EXEC (@cmd)

if exists(select 1 from sys.databases db inner join @BackupHeader bh on db.name=bh.DatabaseName where state_desc=’ONLINE’) begin

select @cmd=’alter database [‘+DatabaseName + ‘] set offline with rollback immediate’ from @BackupHeader

print @cmd

exec (@cmd)

end

— create RESTORE WITH MOVE command

select @cmd=’restore database [‘+DatabaseName + ‘] from disk=”’+@backupFileName+”’ with norecovery, replace,’ from @BackupHeader

select @cmd=@cmd+’ move ”’+LogicalName+”’ TO ”’+@ServerDataPath+RIGHT(PhysicalName, CHARINDEX(‘\’,REVERSE(PhysicalName)))+”’,’ from @BackupFileList where [Type]=’D’

select @cmd=@cmd+’ move ”’+LogicalName+”’ TO ”’+@ServerLogPath+RIGHT(PhysicalName, CHARINDEX(‘\’,REVERSE(PhysicalName)))+”’,’ from @BackupFileList where [Type]=’L’

set @cmd=left(@cmd, len(@cmd)-1) — to remove the ending comma

print @cmd

exec (@cmd)

select @cmd = ‘del ‘ + @backupFileName from @filesSearch

— print @cmd

— exec master.dbo.xp_cmdshell @cmd, no_output

delete @BackupHeader

delete @BackupFileList

delete from @filesSearch where filename=@backupFileName

SELECT TOP 1 @backupFileName=filename FROM @filesSearch

END

RETURN 0

GO

b. Create dbsp_RestoreLogs in secondary.

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

CREATE PROCEDURE [dbo].[dbsp_RestoreLogs]

@LogPath VARCHAR(255),

@Extension CHAR(3) = ‘TRN’

AS

—————————————————————————————————

— Name: dbsp_RestoreLogs

— exec Utility.dbo.dbsp_RestoreLogs ‘\\LS\Y$\’, ‘TRN’

— Parameters:

— @LogPath = The path to restore transaction logs from

— @Extension = The extension of log backup files (default=TRN)

— Returns: None.

— Description: Searches for all files with the specified extension in the specified folder and subfolders that

— also match the names of DBs in RESTORING state on the server.

— Restores files which have LSNs more recent than the database. Leaves the DB in RESTORING state.

— History:

—————————————————————————————————

SET NOCOUNT ON

DECLARE @res INT

DECLARE @result INT

DECLARE @cmd VARCHAR(500)

DECLARE @filesSearch TABLE (

filename VARCHAR(512)

)

DECLARE @files TABLE (

id INT IDENTITY(1,1),

dbname sysname,

filename VARCHAR(512),

firstLSN numeric(25,0),

lastLSN numeric(25,0),

backupDate datetime

)

DECLARE @BackupHeader TABLE (

BackupName nvarchar(128) NULL,

BackupDescription nvarchar(255) NULL,

BackupType smallint NULL,

ExpirationDate datetime NULL,

Compressed tinyint NULL,

Position smallint NULL,

DeviceType tinyint NULL,

UserName nvarchar(128) NULL,

ServerName nvarchar(128) NULL,

DatabaseName nvarchar(128) NULL,

DatabaseVersion int NULL,

DatabaseCreationDate datetime NULL,

BackupSize numeric(20,0) NULL,

FirstLSN numeric(25,0) NULL,

LastLSN numeric(25,0) NULL,

CheckpointLSN numeric(25,0) NULL,

DatabaseBackupLSN numeric(25,0) NULL,

BackupStartDate datetime NULL,

BackupFinishDate datetime NULL,

SortOrder smallint NULL,

CodePage smallint NULL,

UnicodeLocaleId int NULL,

UnicodeComparisonStyle int NULL,

CompatibilityLevel tinyint NULL,

SoftwareVendorId int NULL,

SoftwareVersionMajor int NULL,

SoftwareVersionMinor int NULL,

SoftwareVersionBuild int NULL,

MachineName nvarchar(128) NULL,

Flags int NULL,

BindingID uniqueidentifier NULL,

RecoveryForkID uniqueidentifier NULL,

Collation nvarchar(128) NULL,

FamilyGUID uniqueidentifier NULL,

HasBulkLoggedData bit NULL,

IsSnapshot bit NULL,

IsReadOnly bit NULL,

IsSingleUser bit NULL,

HasBackupChecksums bit NULL,

IsDamaged bit NULL,

BeginsLogChain bit NULL,

HasIncompleteMetaData bit NULL,

IsForceOffline bit NULL,

IsCopyOnly bit NULL,

FirstRecoveryForkID uniqueidentifier NULL,

ForkPointLSN decimal(25, 0) NULL,

RecoveryModel nvarchar(60) NULL,

DifferentialBaseLSN decimal(25, 0) NULL,

DifferentialBaseGUID uniqueidentifier NULL,

BackupTypeDescription nvarchar(60) NULL,

BackupSetGUID uniqueidentifier NULL,

CompressedBackupSize binary(8) NULL,

Containment tinyint not NULL,

KeyAlgorithm nvarchar(32),

EncryptorThumbprint varbinary(20),

EncryptorType nvarchar(32)

)

DECLARE @on INT

DECLARE @max INT

DECLARE @filename VARCHAR(255)

DECLARE @errorStr VARCHAR(512)

SET @res = 0

— get all files/folders in given path (recurse subdirectories)

SET @cmd = ‘dir ‘ + @LogPath + ‘ /B /S’

INSERT INTO @filesSearch (filename)

EXEC @result = master.dbo.xp_cmdshell @cmd

IF (@result <> 0) BEGIN

— dir command failed, exit

PRINT ‘Dir failed for the given path = [‘ + ISNULL(@LogPath, ‘NULL’) + ‘], exiting!’

RETURN -1

END

— get the files to be restored

INSERT INTO @files (dbname, filename)

SELECT

db.name, fs.filename

FROM

@filesSearch fs inner join sys.databases db on fs.filename like ‘%\’+db.name+’_backup%.’+@Extension

WHERE

db.database_id>4 and db.state_desc=’RESTORING’

ORDER BY fs.filename

— loop on each transaction log file to find backup details

SELECT @max = COUNT(*) FROM @files

SET @on = 1

WHILE (@on <= @max) BEGIN

— get file on

SELECT @cmd=’RESTORE HEADERONLY FROM DISK=N”’+filename+”’ WITH FILE=1′

FROM @files WHERE id = @on

SELECT @cmd

INSERT INTO @BackupHeader EXEC (@cmd)

IF @@ERROR<>0 RETURN -1

UPDATE @files SET firstLSN=bh.FirstLSN , lastLSN=bh.LastLSN, backupDate=bh.BackupStartDate from @files cross join @BackupHeader bh WHERE id=@on

DELETE @BackupHeader

— loop

SET @on = @on + 1

END

–select dbname, MAX(backupDate) from @files group by dbname having MAX(backupDate)<DATEADD(dd,-3,getdate())

–select db.name from sys.databases db left join @files f on db.name=f.dbname where db.database_id>6 and db.state_desc=’RESTORING’ and f.id is null

— Check that recent log backups exist for all databases in Restoring state

if exists(select dbname, MAX(backupDate) from @files group by dbname having MAX(backupDate)<DATEADD(dd,-3,getdate()))

or exists(select db.name from sys.databases db left join @files f on db.name=f.dbname where db.database_id>6 and db.state_desc=’RESTORING’ and f.id is null)

BEGIN

PRINT ‘No recent log backups found. Please investigate!’

select dbname from @files group by dbname having MAX(backupDate)<DATEADD(dd,-3,getdate())

union

select db.name from sys.databases db left join @files f on db.name=f.dbname where db.database_id>6 and db.state_desc=’RESTORING’ and f.id is null

— RETURN -1

END

— no need to process empty logs

–delete @files where firstLSN=lastLSN

— no need to process older logs

–delete @files from @files f where lastLSN<=(select top 1 redo_start_lsn from sys.master_files where database_id= db_id(f.dbname) and type=0)

declare filesdelete cursor fast_forward for

select id from @files where firstLSN=lastLSN

union

select id from @files f where lastLSN<=(select top 1 redo_start_lsn from sys.master_files where database_id= db_id(f.dbname) and type=0)

open filesdelete

fetch next from filesdelete into @on

while @@fetch_status=0

BEGIN

select @cmd = ‘del ‘ + filename from @files where id=@on

exec @result=master.dbo.xp_cmdshell @cmd, no_output

delete @files where id=@on

fetch next from filesdelete into @on

END

close filesdelete

deallocate filesdelete

— Check Log Chains integrity. The Min FirstLSN should be less than the DB’s redo_start_LSN and there should be a continuous chain of LSNs afterwards.

if exists (select dbf.database_id from sys.master_files dbf inner join (select dbname, min(firstLSN) firstLSN from @files group by dbname) as f on dbf.database_id=db_id(f.dbname)

where dbf.type=0 and f.firstLSN>dbf.redo_start_LSN)

or exists (select fcurrent.dbname, COUNT(*) from @files fcurrent left join @files fnext on fcurrent.dbname=fnext.dbname and fcurrent.lastLSN=fnext.firstLSN

where fnext.id is null group by fcurrent.dbname having COUNT(*)>1)

BEGIN

PRINT ‘Some log backups are missing. Please investigate!’

select db_name(dbf.database_id) from sys.master_files dbf inner join (select dbname, min(firstLSN) firstLSN from @files group by dbname) as f on dbf.database_id=db_id(f.dbname)

where dbf.type=0 and f.firstLSN>dbf.redo_start_LSN

union all

select fcurrent.dbname from @files fcurrent left join @files fnext on fcurrent.dbname=fnext.dbname and fcurrent.lastLSN=fnext.firstLSN

where fnext.id is null group by fcurrent.dbname having COUNT(*)>1

RETURN -1

END

declare filelist cursor fast_forward for

SELECT id FROM @files ORDER BY dbname, firstLSN

open filelist

fetch next from filelist into @on

while @@fetch_status=0

BEGIN

— get file on

SELECT @cmd=’RESTORE LOG [‘+dbname+’] FROM DISK=”’+filename+”’ WITH NORECOVERY’

FROM @files WHERE id = @on

PRINT @cmd

EXEC (@cmd)

select @cmd = ‘del ‘ + filename from @files where id=@on

exec @result=master.dbo.xp_cmdshell @cmd, no_output

— loop

fetch next from filelist into @on

END

close filelist

deallocate filelist

RETURN @res

GO

c. Create the below SQL Agent Job in Secondary AWS EC2 instance.

USE [msdb]

GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N’MJ LogShipping Database and Log Restore’)

EXEC msdb.dbo.sp_delete_job @job_name=N’MJ LogShipping Database and Log Restore’, @delete_unused_schedule=1

GO

USE [msdb]

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Log Shipping’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Log Shipping’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’MJ LogShipping Database and Log Restore’,

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=2,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’Custom Log Shipping – restores all backups and log backups from a specified folder’,

@category_name=N’Log Shipping’,

@owner_login_name=N’sa’,

@notify_email_operator_name=N’DBA’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Restore all new backups with no recovery] Script Date: 04-07-2017 19:05:20 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Restore all new backups with no recovery’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’DECLARE @result INT

DECLARE @errorStr VARCHAR(512)

exec @result = [Utility].[dbo].[dbsp_RestoreBackupsNoRecovery] ”\\LS\Logshipping”

IF (@result <> 0) BEGIN

SET @errorStr = ”Backup Restore Failed with result = [” + ISNULL(CAST(@result AS VARCHAR), ”NULL”) + ”]!”

RAISERROR (@errorStr, 16, 1)

END ELSE BEGIN

PRINT ”Backup Restore completed.”

END’,

@database_name=N’Utility’,

@output_file_name=N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2K4DLS\MSSQL\Log\MJ LogShipping Log Restore.txt’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Restore all available log backups] Script Date: 04-07-2017 19:05:20 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Restore all available log backups’,

@step_id=2,

@cmdexec_success_code=0,

@on_success_action=3,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’DECLARE @result INT

DECLARE @errorStr VARCHAR(512)

exec @result = [Utility].[dbo].[dbsp_RestoreLogs] @LogPath=”\\BMANI-IN-LE01\Logshipping”

IF (@result <> 0) BEGIN

SET @errorStr = ”Log Restore Failed with result = [” + ISNULL(CAST(@result AS VARCHAR), ”NULL”) + ”]!”

RAISERROR (@errorStr, 16, 1)

END ELSE BEGIN

PRINT ”Log Restore completed.”

END’,

@database_name=N’Utility’,

@output_file_name=N’C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2K4DLS\MSSQL\Log\MJ LogShipping Log Restore.txt’,

@flags=2

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Delete Folders] Script Date: 04-07-2017 19:05:20 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Delete Folders’,

@step_id=3,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N’TSQL’,

@command=N’DECLARE @cmd nvarchar(4000), @path nvarchar(4000)

SET @cmd = ”for /d %i in (\\LS\Logshipping\*.*) do @rmdir /s /q “%i””

exec master.dbo.xp_cmdshell @cmd’,

@database_name=N’master’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Every 1 hours’,

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=8,

@freq_subday_interval=1,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20101220,

@active_end_date=99991231,

@active_start_time=3000,

@active_end_time=235959,

@schedule_uid=N’4ce6c745-a114-4b82-8954-1d248e737247′

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

4. Restoring the database for initial sync in secondary.

a. Make sure all the database are in full recovery before taking full backup on the primary server.

–Changing the recovery model to full:

Select ‘Kill ‘+ CAST(p.spid AS VARCHAR)KillCommand into #temp

from master.dbo.sysprocesses p (nolock)

join master..sysdatabases d (nolock) on p.dbid = d.dbid

Declare @query nvarchar(max)

–Select * from #temp

Select @query =STUFF((

select ‘ ‘ + KillCommand from #temp

FOR XML PATH(”)),1,1,”)

Execute sp_executesql @query

Drop table #temp

declare @name sysname,

@recovery_model_desc nvarchar(120),

@script nvarchar(500),

@full_backup_count int

select @full_backup_count = count(*) from sys.databases where recovery_model_desc in ( ‘SIMPLE’,’BULK_LOGGED’) and name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’utility’)

print ‘There are ‘ + cast(@full_backup_count as varchar) + ‘ databases with simple and bulklogged recovery model’

declare db cursor

for select name, recovery_model_desc from sys.databases where recovery_model_desc in ( ‘SIMPLE’,’BULK_LOGGED’) and name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’Utility’)

for read only

open db

fetch next from db into @name, @recovery_model_desc

while @@fetch_status = 0

begin

set @script = ‘alter database [‘ + @name + ‘] set recovery full’

exec sp_executesql @script

print ‘Done with ‘ + @name

fetch next from db into @name, @recovery_model_desc

end

close db

deallocate db

select @full_backup_count = count(*) from sys.databases where recovery_model_desc = ‘FULL’ and name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’utility’)

print ‘There are ‘ + cast(@full_backup_count as varchar) + ‘ databases with FULL recovery mode’

b. Taking full backup

DECLARE @name VARCHAR(100) — database name

DECLARE @path VARCHAR(256) — path for backup files

DECLARE @fileName VARCHAR(256) — filename for backup

DECLARE @fileDate VARCHAR(20) — used for file name

— specify database backup directory

SET @path = ‘C:\Backup\’

— specify filename format

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,”)

DECLARE db_cursor CURSOR READ_ONLY FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’utility’) — exclude these databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

c. Generate full backup script to be used in the secondary.

begin tran t1

DECLARE @BackupPath NVARCHAR(400);set @BackupPath=’C:\backup\’;with backupset_cte as

(

SELECT database_name,rownum =

row_number() over

(

partition by database_name, type

order by backup_finish_date desc

),MAX(CASE WHEN bs.type = ‘D’ THEN bs.backup_finish_date ELSE NULL END) AS LastFullBackup,

‘RESTORE DATABASE [‘ + a.name + ‘] FROM DISK = ”’ + @BackupPath + RIGHT(bmf.physical_device_name, CHARINDEX(‘\’, REVERSE(bmf.physical_device_name)) -1) + ”” + ‘ WITH NORECOVERY,REPLACE, STATS=10’ +

(SELECT

‘,MOVE ”’ + b.name + ”’ TO ”’ + b.physical_name + ””

FROM master.sys.master_files b

WHERE a.database_id = b.database_id

ORDER BY b.type_desc DESC, b.data_space_id

FOR XML PATH(”)) + ‘;’ as restore_command

FROM master.sys.databases a

inner join msdb.dbo.backupset bs with(nolock) on a.name=bs.database_name

inner join msdb.dbo.backupmediafamily bmf with(nolock) on bs.media_set_id = bmf.media_set_id

WHERE a.name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

AND bs.type = ‘D’

GROUP BY bs.database_name,database_id,a.name,bmf.physical_device_name,bs.type,bs.backup_finish_date

–ORDER BY bs.database_name,database_id DESC

)

select

restore_command

from backupset_cte

where rownum = 1

order by database_name

rollback tran t1

Use full queries for monitoring.
——————————-

–last Tlog taken.

WITH LastTLogBackUp AS

(

SELECT bs.database_name,

bs.backup_start_date,

RIGHT(bmf.physical_device_name, CHARINDEX(‘\’, REVERSE(bmf.physical_device_name)) -1) [file_name],

Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )

FROM msdb.dbo.backupmediafamily bmf

JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id

JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id

Join master.sys.sysdatabases s on bs.database_name = s.name

WHERE bs.[type] = ‘L’

–AND bs.is_copy_only = 0

)

SELECT

database_name AS [Database],

backup_start_date AS [Last Tlog Backup Date],

[file_name] AS [Backup File Location]

FROM LastTLogBackUp

WHERE Position = 1

ORDER BY [Database] asc;

— Last tlog restored

WITH LastRestores AS

(

SELECT

DatabaseName = [d].[name] ,

r.restore_date,

RIGHT(m.physical_device_name , CHARINDEX(‘\’, REVERSE(m.physical_device_name )) -1) [file_name],

RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)

FROM master.sys.databases d

LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name

inner join msdb.dbo.backupfile b on r.backup_set_id = b.backup_set_id

inner join msdb.dbo.backupset bs on b.backup_set_id = bs.backup_set_id

inner join msdb.dbo.backupmediafamily m on bs.media_set_id = m.media_set_id

)

SELECT *

FROM [LastRestores]

WHERE [RowNum] = 1

ORDER BY [DatabaseName] asc;

d. Use the output of C to restore the database in secondary on a share location before starting the logshipping job in the primary and secondary.

5. during cut over

1. Stop all the jobs.

2. Backup and Restoring the tail log backup:

convert the output to text format

USE MASTER

go

SET NOCOUNT ON

DECLARE @name VARCHAR(100) — database name

DECLARE @path VARCHAR(256) — path for backup files

DECLARE @fileName VARCHAR(256) — filename for backup

DECLARE @fileDate VARCHAR(20) — used for file name

— specify database backup directory

SET @path = ‘C:\Backups\’

— specify filename format

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),’:’,”)

DECLARE db_cursor CURSOR READ_ONLY FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’utility’) — exclude these databases

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + ‘_’ + @fileDate + ‘_NORECOVERY.trn’

BACKUP LOG @name TO DISK = @fileName WITH NORECOVERY

select ‘RESTORE DATABASE ‘ + @name + ‘ FROM DISK=”’ + @fileName + ”’ WITH RECOVERY’

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

—–

begin tran t1

DECLARE @BackupPath NVARCHAR(400);set @BackupPath=’C:\backups\’;with backupset_cte as

(

SELECT database_name,rownum =

row_number() over

(

partition by database_name, type

order by backup_finish_date desc

),MAX(CASE WHEN bs.type = ‘D’ THEN bs.backup_finish_date ELSE NULL END) AS LastFullBackup,

‘RESTORE DATABASE [‘ + a.name + ‘] FROM DISK = ”’ + @BackupPath + RIGHT(bmf.physical_device_name, CHARINDEX(‘\’, REVERSE(bmf.physical_device_name)) -1) + ”” + ‘ WITH RECOVERY’ + ‘;’ as restore_command

FROM master.sys.databases a

inner join msdb.dbo.backupset bs with(nolock) on a.name=bs.database_name

inner join msdb.dbo.backupmediafamily bmf with(nolock) on bs.media_set_id = bmf.media_set_id

WHERE a.name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’Utility’)

AND bs.type = ‘L’

GROUP BY bs.database_name,database_id,a.name,bmf.physical_device_name,bs.type,bs.backup_finish_date

–ORDER BY bs.database_name,database_id DESC

)

select

restore_command

from backupset_cte

where rownum = 1

order by database_name

rollback tran t1

—–

changing the DB version:

————————

DECLARE GET_DATABASES CURSOR

READ_ONLY

FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != ‘120’

DECLARE @DATABASENAME NVARCHAR(255)

DECLARE @COUNTER INT

SET @COUNTER = 1

OPEN GET_DATABASES

FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

— CHANGE DATABASE COMPATIBILITY

EXECUTE sp_dbcmptlevel @DATABASENAME , ‘120’

PRINT @DATABASENAME + ‘ changed’

SET @COUNTER = @COUNTER + 1

END

FETCH NEXT FROM GET_DATABASES INTO @DATABASENAME

END

CLOSE GET_DATABASES

DEALLOCATE GET_DATABASES

GO

3. Move your logins from primary to secondary using https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

4. Script out all the SQL Agent jobs from the activity monitor and apply it on Secondary. Make sure the jobs are running before handing of the system to QA team.

Note: we have added few more steps to automatics create folder for new database, remove schedule and created linkedserver so that primary can call the secondary job but i removed those
details to keep it simple.

Posted in LOG SHIPPING | Leave a Comment »

Restore command generator for particular database in MSSQL.

Posted by database-wiki on November 30, 2017

begin tran t1

DECLARE @Database NVARCHAR(100)
DECLARE @MovePath NVARCHAR(400)

–If you don’t move datafile,Set @MovePath = ”
SET @Database = ‘Test’
SET @MovePath = ‘D:\database\’

DECLARE @FullMediaSetID NVARCHAR(20)
DECLARE @BackupSetID NVARCHAR(20)
DECLARE @FullPath NVARCHAR(400)
DECLARE @FullPosition NVARCHAR(20)

DECLARE @LogMediaSetID NVARCHAR(20)
DECLARE @LogPath NVARCHAR(400)
DECLARE @LogPosition NVARCHAR(20)

DECLARE @DiffMediaSetID NVARCHAR(20)
DECLARE @DiffPath NVARCHAR(400)
DECLARE @DiffPosition NVARCHAR(20)

DECLARE @SQLMove NVARCHAR(MAX)
SET @SQLMove =”

IF @MovePath <> ”
BEGIN
DECLARE @LogicalName NVARCHAR(200)
DECLARE @FileID NVARCHAR(10)
DECLARE @FileType NVARCHAR(10)
DECLARE @ExtName NVARCHAR(20)

DECLARE MoveCur CURSOR FOR
SELECT m.name
,m.file_id
,m.type
FROM sys.master_files m
INNER JOIN sys.databases d
ON m.database_id = d.database_id
WHERE d.name = @Database

OPEN MoveCur

FETCH NEXT FROM MoveCur INTO @LogicalName,@FileID,@FileType

WHILE @@FETCH_STATUS = 0
BEGIN
SET @ExtName = CASE
WHEN @FileID = 1 THEN ‘.mdf’
WHEN @FileID > 1 THEN CASE @FileType
WHEN 0 THEN ‘.ndf’ ELSE ‘.ldf’ END
END
SET @SQLMove = @SQLMove + CHAR(10) + ‘ , MOVE ‘ + ”” + @LogicalName + ””
+ ‘ TO ‘ + ”” + @MovePath + @LogicalName + @ExtName + ””
FETCH NEXT FROM MoveCur INTO @LogicalName,@FileID,@FileType
END
CLOSE MoveCur
DEALLOCATE MoveCur
END

SELECT
@BackupSetID = backup_set_id,
@FullMediaSetID = media_set_id,
@FullPosition = position
FROM msdb.dbo.backupset
WHERE backup_set_id =
(
SELECT MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name = @Database
AND type=’D’
GROUP BY database_name
)

SELECT @FullPath = physical_device_name
FROM msdb.dbo.backupmediafamily
WHERE media_set_id = @FullMediaSetID

IF NOT EXISTS(SELECT 1 FROM msdb.dbo.backupset
WHERE database_name = @Database AND type IN(‘L’,’I’) AND backup_set_id > @BackupSetID)
BEGIN
PRINT REPLICATE(‘-‘,200)
PRINT ‘———–//Full backup restore only’
PRINT REPLICATE(‘-‘,200)
PRINT ‘RESTORE DATABASE ‘ + @Database + ‘ FROM DISK = ”’ + @FullPath + ”” + CHAR(10)
+ ‘ WITH FILE = ‘ + @FullPosition + ‘, REPLACE’ + @SQLMove
END

IF EXISTS(SELECT 1 FROM msdb.dbo.backupset
WHERE database_name = @Database AND type=’L’ AND backup_set_id > @BackupSetID)
BEGIN
PRINT REPLICATE(‘-‘,200)
PRINT ‘———–//Full backup and log backup restore ‘
PRINT REPLICATE(‘-‘,200)
PRINT ‘RESTORE DATABASE ‘ + @Database + ‘ FROM DISK = ”’ + @FullPath + ”” + CHAR(10)
+ ‘ WITH FILE = ‘ + @FullPosition + ‘, NORECOVERY, REPLACE ‘ + @SQLMove

DECLARE log_cursor CURSOR FOR
SELECT media_set_id
,position
FROM msdb.dbo.backupset
WHERE database_name = @Database
AND type=’L’
AND backup_set_id > @BackupSetID
ORDER BY backup_set_id

OPEN log_cursor

FETCH NEXT FROM log_cursor INTO @LogMediaSetID,@LogPosition

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @LogPath = physical_device_name
FROM msdb.dbo.backupmediafamily
WHERE media_set_id = @LogMediaSetID

PRINT ‘RESTORE Log ‘ + @Database + ‘ FROM DISK = ”’ + @LogPath
+ ”’ WITH FILE = ‘ + @LogPosition + ‘, NORECOVERY’
FETCH NEXT FROM log_cursor INTO @LogMediaSetID,@LogPosition
END
CLOSE log_cursor
DEALLOCATE log_cursor

PRINT ‘RESTORE DATABASE ‘ + @Database + ‘ WITH RECOVERY ‘
END

IF EXISTS(SELECT 1 FROM msdb.dbo.backupset
WHERE database_name = @Database AND type=’I’ AND backup_set_id > @BackupSetID)
BEGIN
PRINT REPLICATE(‘-‘,200)
PRINT ‘———–//Full backup and differential backup restore ‘
PRINT REPLICATE(‘-‘,200)
PRINT ‘RESTORE DATABASE ‘ + @Database + ‘ FROM DISK = ”’ + @FullPath + ”” + CHAR(10)
+ ‘ WITH FILE = ‘ + @FullPosition + ‘, NORECOVERY, REPLACE ‘ + @SQLMove

DECLARE diff_cursor CURSOR FOR
SELECT media_set_id
,position
FROM msdb.dbo.backupset
WHERE database_name = @Database
AND type=’I’
AND backup_set_id > @BackupSetID
ORDER BY backup_set_id

OPEN diff_cursor

FETCH NEXT FROM diff_cursor INTO @DiffMediaSetID,@DiffPosition

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @DiffPath = physical_device_name
FROM msdb.dbo.backupmediafamily
WHERE media_set_id = @DiffMediaSetID

PRINT ‘RESTORE DATABASE ‘ + @Database + ‘ FROM DISK = ”’ + @DiffPath
+ ”’ WITH FILE = ‘ + @DiffPosition + ‘, NORECOVERY’

FETCH NEXT FROM diff_cursor INTO @DiffMediaSetID,@DiffPosition
END
CLOSE diff_cursor
DEALLOCATE diff_cursor

PRINT ‘RESTORE DATABASE ‘ + @Database + ‘ WITH RECOVERY ‘
END
GO

rollback tran t1

Posted in Uncategorized | Leave a Comment »

Powershell script to create admin/normal user for all the databases in SQL Azure instance.

Posted by database-wiki on March 20, 2017

=> Use sqlps
=> provide correct parameters for $newSqlUser, $serverName, $sqlAdminLogin, $sqlAdminPassword

PS code:
=======
Import-Module SQLPS -DisableNameChecking
$newSqlUser = ‘test3’;
$serverName = ‘sqlazure.database.windows.net’
$createAdminUser = $TRUE;

# generate a nice long random password
Add-Type -Assembly System.Web
$newSqlPassword = [Web.Security.Membership]::GeneratePassword(25,3) -Replace ‘[%&+=;:/]’, “!”;

# prompt for your server admin password.

#resets the variable
$results = @()

# login using SQL authentication, which means we supply the username
# and password

$credential = Get-Credential
#the backslash is regular expression to remove \ (prompt will give it as default for domain)
$userName = $credential.UserName -replace(“\\”,””)

#getnetworkcredential gives the passport unencrypted
$pass = $credential.GetNetworkCredential().password

# Create login and user in master db

$sql = “create login [$newSqlUser] with password = ‘$newSqlPassword’;”
invoke-sqlcmd -Query $sql -ServerInstance $serverName -Database ‘master’ -U $userName -P $pass

“new login: $newSqlUser”
“password: $newSqlPassword”

# Create login and user in master db

if ( $createAdminUser ) { `
$sql = “create login [$newSqlUser] with password = ‘$newSqlPassword’; create user [$newSqlUser] from login [$newSqlUser];exec sp_addRoleMember ‘dbmanager’, ‘$newSqlUser’; exec sp_addRoleMember ‘loginmanager’, ‘$newSqlUser’;”;`
} else { `
$sql = “create login [$newSqlUser] with password = ‘$newSqlPassword’; create user [$newSqlUser] from login [$newSqlUser];”;`
}

# sql to create user in each db
if ( $createAdminUser ) { `
$createUserSql = “create user [$newSqlUser] from login [$newSqlUser]; exec sp_addRoleMember ‘db_owner’, ‘$newSqlUser’; “; `
} else { `
$createUserSql = “create user [$newSqlUser] from login [$newSqlUser]; exec sp_addRoleMember ‘db_datareader’, ‘$newSqlUser’; exec sp_addRoleMember ‘db_denydatawriter’, ‘$newSqlUser’;”; `
}

# can’t have multiple Invoke-SQLCmd in a pipeline so get the dbnames first, then iterate
$sql = “select name from sys.databases where name <> ‘master’;”
$dbNames = @()
invoke-sqlcmd -Query $sql -ServerInstance $serverName -Database ‘master’ -U $userName -P $pass | `
foreach { $dbNames += $_.name }
# iterate over the dbs and add user to each one
foreach ($db in $dbNames ) { `
invoke-sqlcmd -Query $createUserSql -ServerInstance $serverName -Database $db -U $userName -P $pass; `
“created user in $db database”; `
}

Posted in SQL Azure | Leave a Comment »

Last Restored Backup for all the User databases.

Posted by database-wiki on February 7, 2017

WITH LastRestores AS
(
SELECT
DatabaseName = [d].[name] ,
r.restore_date,
RIGHT(m.physical_device_name , CHARINDEX(‘\’, REVERSE(m.physical_device_name )) -1) [file_name],
RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
inner join msdb.dbo.backupfile b on r.backup_set_id = b.backup_set_id
inner join msdb.dbo.backupset bs on b.backup_set_id = bs.backup_set_id
inner join msdb.dbo.backupmediafamily m on bs.media_set_id = m.media_set_id
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

Posted in LOG SHIPPING, Uncategorized | Leave a Comment »

Query to Monitoring Log Shipping on secondary instance.

Posted by database-wiki on February 7, 2017

WITH LastRestores AS

(

SELECT

DatabaseName = [d].[name] ,

r.restore_date,

bs.server_name, m.physical_device_name as backup_device, b.physical_name,

RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)

FROM master.sys.databases d

LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name

inner join msdb.dbo.backupfile b on r.backup_set_id = b.backup_set_id

inner join msdb.dbo.backupset bs on b.backup_set_id = bs.backup_set_id

inner join msdb.dbo.backupmediafamily m on bs.media_set_id = m.media_set_id

)

SELECT *

FROM [LastRestores]

WHERE [RowNum] = 1

Posted in LOG SHIPPING | Leave a Comment »

powershell command to check disk space on mount points.

Posted by database-wiki on February 7, 2017

Get-WmiObject win32_volume | Select-Object SystemName,Name, BlockSize, Capacity, FreeSpace, DriveLetter , @{Name=”CapacityGB”;Expression={[math]::round($_.Capacity/1GB,2)}}, @{Name=”FreeSpaceGB”;Expression={[math]::round($_.FreeSpace/1GB,2)}} , @{Name=”FreeSpacePercent”;Expression={[math]::round(($_.FreeSpace/($_.Capacity*1.00))*100.00,2)}} , @{Name=”Date”;Expression={$(Get-Date -f s)}}| Sort-Object Name

Output:

SystemName       :
Name             : D:\SQLServer\data\
BlockSize        : 65536
Capacity         : 214611984384
FreeSpace        : 10682368
DriveLetter      :
CapacityGB       : 199.87
FreeSpaceGB      : 0.01
FreeSpacePercent : 0
Date             : 2017-02-06T20:42:16

Posted in WINDOWS | Leave a Comment »

Changing the location of LOG Shipping folder and share in SQL Server 2014.

Posted by database-wiki on September 22, 2016

1. Disabling the Log Shipping Jobs in primary check if copy and restore files are up to date and then disable the Log Shipping Jobs in secondary.

 Script to generate SP_UPDATE_JOB to disable backup and alert jobs in primary.

begin tran t1

USE MSDB

go

SET NOCOUNT ON;

DECLARE @jobname NVARCHAR(256)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name from dbo.sysjobs WHERE category_id = 6 and name like ‘LSBackup%’ or name like ‘LSAlert%’

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @jobname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘EXEC msdb.dbo.Sp_update_job @job_name = ”’ + @jobname +”’,@enabled = 0′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @jobname

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSBackup_DATABASE’,@enabled = 0

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSAlert_SERVER\INSTANCE’,@enabled = 0

Script to generate SP_UPDATE_JOB to disable copy, restore and alert jobs in secondary.

begin tran t1

USE MSDB

go

SET NOCOUNT ON;

DECLARE @jobname NVARCHAR(256)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name from dbo.sysjobs WHERE category_id = 6 and name like ‘LSRestore%’ or name like ‘LSCopy%’ or name like ‘LSAlert%’

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @jobname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘EXEC msdb.dbo.Sp_update_job @job_name = ”’ + @jobname +”’,@enabled = 0′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @jobname

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSCopy_PRIMARY_SERVER\PRIMARY_DATABASE’,@enabled = 0

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSRestore_PRIMARY_SERVER\PRIMARY_DATABASE’,@enabled = 0

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSAlert_SECONDARY_SERVER\SECONDARY_INSTANCE’,@enabled = 0

2. Changing the LS TLog backup setting in the primary server:

Script to generate the command in case we have multiple databases:

begin tran t1

SET NOCOUNT ON;

DECLARE @Database NVARCHAR(256)

, @bkpdir NVARCHAR(1000)

, @bkpshare NVARCHAR(1000)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases

WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’)   and name not like ‘%_old’

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

SET @bkpdir = N’I:\TLOG’ + @Database

SET @bkpshare = N’\\Share_primary\TLOG\’ + @Database

SET @cmd = ‘EXEC MASTER.dbo.sp_change_log_shipping_primary_database

@database =’ + @Database +

‘, @backup_directory =”’ + @bkpdir + ”’, @backup_share =”’ +  @bkpshare + ”’, @backup_compression = 2′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC MASTER.dbo.sp_change_log_shipping_primary_database

@database = DATABASE

, @backup_directory = ‘I:\TLOG\to_secondary\DATABASE_FOLDER’

, @backup_share = ‘\\Share_primary\TLOG\DATABASE_FOLDER’

, @backup_compression = 2

Confirm all the new locations.

select primary_database,backup_directory,backup_share,backup_compression from msdb.dbo.log_shipping_primary_databases

3. Changing the setting in the seconday server:

Changing the locations:

Script to generate the command in case we have multiple databases:

begin tran t1

SET NOCOUNT ON;

DECLARE @Database NVARCHAR(256)

, @bkpshare NVARCHAR(1000)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name FROM master.dbo.sysdatabases

WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’)   and name not like ‘%_old’

ORDER BY 1

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @Database

WHILE @@FETCH_STATUS = 0

BEGIN

SET @bkpshare = N’\\Share_primary\TLOG\’ + @Database

SET @cmd = ‘EXEC MASTER.dbo.sp_change_log_shipping_secondary_primary

@primary_server =  ”PRIMARY_SERVER\PRIMARY_INSTANCE”

, @primary_database =”’ + @Database +

”’, @backup_source_directory =”’ + @bkpshare +””

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @Database

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC MASTER.dbo.sp_change_log_shipping_secondary_primary

@primary_server =  ‘PRIMARY\INSTANCE

, @primary_database =  ‘DATABASE’

, @backup_source_directory =  ‘\\Share_primary\TLOG\DATABASE_FOLDER’

, @backup_destination_directory =’I:\TLOG\from_primary\DATABASE_FOLDER’

Checking the changed location.

select primary_server,primary_database,backup_source_directory,backup_destination_directory from msdb.dbo.log_shipping_secondary

Open Failover Cluster Manager à select the running SQL Server (INSTANCE) role à select SQL Server (INSTANCE) resource from the drop down panel à right click [Take Offline].

Move the Log Shipping Secondary database files to the new location I:\INSTANCE\MSSQL12.INSTANCE\MSSQL in Windows Explorer.

Move the Log Shipping TLOG backup files from K:\TLOG\to_secondary to the new location I:\TLOG\to_secondary in Windows Explorer. (step 8 and 9 should be in parallel)

Open Failover Cluster Manager à select the running SQL Server (INSTANCE) role à select SQL Server (INSTANCE) resource from the drop down panel à right click [Take Online].

4. Enabling the Log Shipping Jobs in primary check if now TLog backup are taken in new location and then enable the Log Shipping Jobs in secondary.

Script to generate SP_UPDATE_JOB to enable backup and alert jobs in primary.

begin tran t1

USE MSDB

go

SET NOCOUNT ON;

DECLARE @jobname NVARCHAR(256)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name from dbo.sysjobs WHERE category_id = 6 and name like ‘LSBackup%’ or name like ‘LSAlert%’

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @jobname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘EXEC msdb.dbo.Sp_update_job @job_name = ”’ + @jobname +”’,@enabled = 1′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @jobname

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSBackup_DATABASE’,@enabled = 1

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSAlert_SERVER\INSTANCE’,@enabled = 1

NOTE: wait for the backup fall under the new location.

Script to generate SP_UPDATE_JOB to disable copy, restore and alert jobs in secondary.

begin tran t1

USE MSDB

go

SET NOCOUNT ON;

DECLARE @jobname NVARCHAR(256)

, @cmd NVARCHAR(500)

DECLARE DatabaseCursor CURSOR FOR

SELECT name from dbo.sysjobs WHERE category_id = 6 and name like ‘LSRestore%’ or name like ‘LSCopy%’ or name like ‘LSAlert%’

OPEN DatabaseCursor

FETCH NEXT FROM DatabaseCursor INTO @jobname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = ‘EXEC msdb.dbo.Sp_update_job @job_name = ”’ + @jobname +”’,@enabled = 1′

select @cmd

–EXEC (@cmd)

FETCH NEXT FROM DatabaseCursor INTO @jobname

END

CLOSE DatabaseCursor

DEALLOCATE DatabaseCursor

rollback tran t1

OUTPUT:

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSCopy_PRIMARY_SERVER\PRIMARY_DATABASE’,@enabled = 1

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSRestore_PRIMARY_SERVER\PRIMARY_DATABASE’,@enabled = 1

EXEC msdb.dbo.Sp_update_job @job_name = ‘LSAlert_SECONDARY_SERVER\SECONDARY_INSTANCE’,@enabled = 1

 

Posted in Uncategorized | 3 Comments »