BI cooking

with a touch of SQL
posts - 5 , comments - 1 , trackbacks - 0

Wednesday, January 16, 2013

Grouped Failover, a 2008R2 version of Availability groups

One of the new cool features in SQL 2012 is the SQL Server Availability groups. In other words being able to failover a group of databases which are logically connected. i.e. SharePoint databases. Well it is also possible to do that in SQL 2008 (R2). It’s called a Grouped Failover. Instead of configuring it in SQL mirroring, you have to administrate the connected databases yourself and with a single stored procedure you will be able to failover a group of databases. It also gives you the option to failover a group of mirrored databases when the principal is down.

How to implement this solution

First run the script and it will create a database, 3 tables and a stored procedure. Of the 3 tables 2 of them need to be filled with configuration you desire or need. In the table tblFailoverGroups you create 1 or more failover groups. In the table tblFailoverGroupDatabases you need to add the databases that are linked to the failover group you just created. You can also add databases that are not mirrored, but have a logical connection to the failover group. These database will not failover since they are not mirrored.

Script
----------------------------------------------------------------
-- Create grouped failover solution
----------------------------------------------------------------
-- Author:        Remko de Boer
-- Email:        remko@bittown.nl
-- Created:        16-01-2013
-- Description:    The grouped failover solution is an solution that
--                that empoweres the DBA to failover a number of databases
--                which are logically linked to eachother i.e. sharepoint
--                databases. The concept is based on 2 tables:
--                tblFailoverGroups and tblFailoverGroupDatabases
--                The tblFailoverGroups contains the group information en
--                description. It is possible to have multiple groups on a
--                single SQL Server Instance.
--                The tblFailoverGroupDatabases contains all databases linked
--                to a specific group. It is not possible for a database to be
--                in more then 1 group. 
--                It is possible to add databases to the group, even while these
--                databases are not being mirrored.
----------------------------------------------------------------
-- Version:        1.0 Initial
----------------------------------------------------------------
-- Installation
----------------------------------------------------------------
-- Run this script, and after a succesfull execution check if 
-- the following objects have been created:
-- database        SQL_Beheer
-- table        dbo.tblFailoverGroups
-- table        dbo.tblFailoverGroupDatabases
-- table        dbo.tblFailoverErrors
-- procedure    dbo.spFailoverGroup
-- If all these objects are installed correctly, 
-- the solution then needs to configured by adding groups 
-- and adding databases to these groups.
-- After configguring the groups and databases the grouped failover
-- can be executed by the use of spFailoverGroup.
-- Check the inline documentation of the spFailoverGroup procedure
-- to obtain the needed information on what values to use as parameters
----------------------------------------------------------------
 
 
-- Check if database already exsists ---------------------------
if (select count(1) from sys.databases where name = 'SQL_Beheer') > 0
    BEGIN
        print 'database already exists.'
    END
else
    begin
        Create database SQL_Beheer
 
        print 'database SQL_Beheer created.'
    end
----------------------------------------------------------------
GO
 
-- Switch to SQL_Beheer ----------------------------------------
USE SQL_Beheer
----------------------------------------------------------------
GO
 
-- check if tblFailoverGroups exists ---------------------------
if (select count(1) from sys.tables where name = 'tblFailoverGroups') > 0
    BEGIN
        print 'table tblFailoverGroups already exists.'
    END
ELSE
    BEGIN
        CREATE TABLE [dbo].[tblFailoverGroups]
        (
            [id] [int] IDENTITY(1,1) NOT NULL,
            [group_name] [varchar](250) NULL,
            [group_description] [varchar](max) NULL
        ) ON [PRIMARY]
 
        print 'table tblFailoverGroups created.'
    END
----------------------------------------------------------------
GO
 
-- check if tblFailoverGroupDatabases exists -------------------
if (select count(1) from sys.tables where name = 'tblFailoverGroupDatabases') > 0
    BEGIN
        print 'table tblFailoverGroupDatabases already exists.'
    END
ELSE
    BEGIN
    CREATE TABLE [dbo].[tblFailoverGroupDatabases]
        (
        [id] [int] IDENTITY(1,1) NOT NULL,
        [group_id] [int] NULL,
        [database_name] [varchar](250) NULL,
        [database_id] [int] NULL
        ) ON [PRIMARY]
 
        print 'table tblFailoverGroupDatabases created.'
    END
----------------------------------------------------------------
GO
 
-- check if tblFailoverErrors exists ---------------------------
if (select count(1) from sys.tables where name = 'tblFailoverErrors') > 0
    BEGIN
        print 'table tblFailoverErrors already exists.'
    END
ELSE
    BEGIN
        CREATE TABLE [dbo].[tblFailoverErrors]
            (
            [id] [int] IDENTITY(1,1) NOT NULL,
            [group_id] [int] NULL,
            [database_id] [int] NULL,
            [error_date] [date] NULL,
            [error_time] [time](7) NULL,
            [error_message] [varchar](1000) NULL
            ) ON [PRIMARY]
 
        print 'table tblFailoverErrors created.'
    END
----------------------------------------------------------------
GO
 
-- check if spFailoverGroup exists -----------------------------
if (select count(1) from sys.procedures where name = 'spFailoverGroup') > 0
    BEGIN
        print 'procedure spFailoverGroup already exists'
    END
ELSE
    BEGIN
        declare @string nvarchar(max)
 
        set @string = N''
 
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- dbo.spFailoverGroup <@groupid> <@actionid>' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Author:        Remko de Boer' + char(13) + char(10)
        set @string = @string + N'-- Created:        16-01-2013' + char(13) + char(10)
        set @string = @string + N'-- Description:    This procedure will failover mirrored databases as groups,' + char(13) + char(10)
        set @string = @string + N'--                based on the entries in the tblFailoverGroups table and the' + char(13) + char(10)
        set @string = @string + N'--                tblFailOverGroupDatabases table.' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Parameters:    @groupid,' + char(13) + char(10)
        set @string = @string + N'--                This is the id of the group which needs a failover.' + char(13) + char(10)
        set @string = @string + N'--                @action id,' + char(13) + char(10)
        set @string = @string + N'--                This switch gives the option to either do a normal' + char(13) + char(10)
        set @string = @string + N'--                controlled failover without dataloss or an emergency' + char(13) + char(10)
        set @string = @string + N'--                failover with possible dataloss. This emergency failover' + char(13) + char(10)
        set @string = @string + N'--                is only needed when the principal is no longer operating' + char(13) + char(10)
        set @string = @string + N'--                properly and there is no automated failover configgured.' + char(13) + char(10)
        set @string = @string + N'--                Swith Options:' + char(13) + char(10)
        set @string = @string + N'--                0 = Normal failover' + char(13) + char(10)
        set @string = @string + N'--                1 = forced failover on mirror, principal not available' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Version:        1.0 Initial Version' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'create proc spFailoverGroup' + char(13) + char(10)
        set @string = @string + N'@groupid int,' + char(13) + char(10)
        set @string = @string + N'@actionid int' + char(13) + char(10)
        set @string = @string + N'AS' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- DEBUG --------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'--declare @groupid int' + char(13) + char(10)
        set @string = @string + N'--declare @actionid int' + char(13) + char(10)
        set @string = @string + N'' + char(13) + char(10)
        set @string = @string + N'--set @groupid = 1' + char(13) + char(10)
        set @string = @string + N'--set @actionid = 0' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Database state:' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- 0 = ONLINE ' + char(13) + char(10)
        set @string = @string + N'-- 1 = RESTORING' + char(13) + char(10)
        set @string = @string + N'-- 2 = RECOVERING' + char(13) + char(10)
        set @string = @string + N'-- 3 = RECOVERY_PENDING' + char(13) + char(10)
        set @string = @string + N'-- 4 = SUSPECT' + char(13) + char(10)
        set @string = @string + N'-- 5 = EMERGENCY' + char(13) + char(10)
        set @string = @string + N'-- 6 = OFFLINE' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Mirroring state:' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- 0 = Suspended' + char(13) + char(10)
        set @string = @string + N'-- 1 = Disconnected from the other partner' + char(13) + char(10)
        set @string = @string + N'-- 2 = Synchronizing ' + char(13) + char(10)
        set @string = @string + N'-- 3 = Pending Failover' + char(13) + char(10)
        set @string = @string + N'-- 4 = Synchronized' + char(13) + char(10)
        set @string = @string + N'-- 5 = The partners are not synchronized. Failover is not possible now.' + char(13) + char(10)
        set @string = @string + N'-- 6 = The partners are synchronized. Failover is potentially possible.' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Mirroring Role:' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- 1 = Principal' + char(13) + char(10)
        set @string = @string + N'-- 2 = Mirror' + char(13) + char(10)
        set @string = @string + N'-- NULL = Database is inaccessible or is not mirrored. ' + char(13) + char(10)
        set @string = @string + N'----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Drop temp table(s) -------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'IF object_id(''tempdb..#worklist'') IS NOT NULL' + char(13) + char(10)
        set @string = @string + N'BEGIN' + char(13) + char(10)
        set @string = @string + N'   DROP TABLE #worklist' + char(13) + char(10)
        set @string = @string + N'END' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Create temp table(s) -----------------------------------------------' + char(13) + char(10)
        set @string = @string + N'create table #worklist' + char(13) + char(10)
        set @string = @string + N'(' + char(13) + char(10)
        set @string = @string + N'id int identity (1,1) not null' + char(13) + char(10)
        set @string = @string + N',database_id int null' + char(13) + char(10)
        set @string = @string + N',database_name varchar(250) null' + char(13) + char(10)
        set @string = @string + N')' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Create worklist ----------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'insert into #worklist' + char(13) + char(10)
        set @string = @string + N'(' + char(13) + char(10)
        set @string = @string + N'database_id,' + char(13) + char(10)
        set @string = @string + N'database_name' + char(13) + char(10)
        set @string = @string + N')' + char(13) + char(10)
        set @string = @string + N'select ' + char(13) + char(10)
        set @string = @string + N'    database_id,' + char(13) + char(10)
        set @string = @string + N'    database_name' + char(13) + char(10)
        set @string = @string + N'from' + char(13) + char(10)
        set @string = @string + N'    tblFailoverGroupDatabases' + char(13) + char(10)
        set @string = @string + N'where' + char(13) + char(10)
        set @string = @string + N'    group_id = @groupid' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Declare Variables --------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'declare @database_name varchar(250) ' + char(13) + char(10)
        set @string = @string + N'declare @database_id int' + char(13) + char(10)
        set @string = @string + N'declare @database_status int' + char(13) + char(10)
        set @string = @string + N'declare @database_role int' + char(13) + char(10)
        set @string = @string + N'declare @mirror_status int' + char(13) + char(10)
        set @string = @string + N'declare @error_message varchar(2000)' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Create cursor ------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'declare failover_worklist cursor for' + char(13) + char(10)
        set @string = @string + N'select database_id, database_name from #worklist order by database_name' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'open failover_worklist' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'FETCH NEXT FROM failover_worklist' + char(13) + char(10)
        set @string = @string + N'INTO @database_id, @database_name' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Check @@FETCH_STATUS to see if there are any more rows to fetch.' + char(13) + char(10)
        set @string = @string + N'WHILE @@FETCH_STATUS = 0' + char(13) + char(10)
        set @string = @string + N'BEGIN' + char(13) + char(10)
        set @string = @string + N'    -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'    -- check if normal failover needs to be ' + char(13) + char(10)
        set @string = @string + N'    -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'    -- @actionid' + char(13) + char(10)
        set @string = @string + N'    -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'    -- 0 = Normal Failover' + char(13) + char(10)
        set @string = @string + N'    -- 1 = Emergency Mode ### Potential Dataloss when used ! ###' + char(13) + char(10)
        set @string = @string + N'    -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'    if @actionid = 0 -- Normal Failover --' + char(13) + char(10)
        set @string = @string + N'    BEGIN' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'        -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        -- check if the database is mirrored' + char(13) + char(10)
        set @string = @string + N'        -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        -- 1    = Principal' + char(13) + char(10)
        set @string = @string + N'        -- 2    = Mirror' + char(13) + char(10)
        set @string = @string + N'        -- NULL = Database is inaccessible or is not mirrored.' + char(13) + char(10)
        set @string = @string + N'        -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        set @database_role = (select mirroring_role from sys.database_mirroring where database_id = @database_id)' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'        if @database_role is null' + char(13) + char(10)
        set @string = @string + N'        BEGIN' + char(13) + char(10)
        set @string = @string + N'                set @error_message = ''Database is not configured for mirroring''' + char(13) + char(10)
        set @string = @string + N'        END ' + char(13) + char(10)
        set @string = @string + N'        else' + char(13) + char(10)
        set @string = @string + N'        BEGIN' + char(13) + char(10)
        set @string = @string + N'                ------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'                -- check the mirroring state' + char(13) + char(10)
        set @string = @string + N'                ------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'                -- 0 = Suspended' + char(13) + char(10)
        set @string = @string + N'                -- 1 = Disconnected from the other partner' + char(13) + char(10)
        set @string = @string + N'                -- 2 = Synchronizing ' + char(13) + char(10)
        set @string = @string + N'                -- 3 = Pending Failover' + char(13) + char(10)
        set @string = @string + N'                -- 4 = Synchronized' + char(13) + char(10)
        set @string = @string + N'                -- 5 = The partners are not synchronized. Failover is not possible now.' + char(13) + char(10)
        set @string = @string + N'                -- 6 = The partners are synchronized. Failover is potentially possible.' + char(13) + char(10)
        set @string = @string + N'                -----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'                set @mirror_status = (select mirroring_state from sys.database_mirroring where database_id = @database_id)' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                if @mirror_status = 4 or @mirror_status = 6' + char(13) + char(10)
        set @string = @string + N'                    BEGIN' + char(13) + char(10)
        set @string = @string + N'                        declare @string nvarchar(4000)' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                        set @string = N'''' ' + char(13) + char(10)
        set @string = @string + N'                        set @string = N''ALTER DATABASE '' + CAST(@database_name as varchar) + '' SET PARTNER FAILOVER''' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                        exec sp_executesql @string' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                        set @error_message = ''Manual failover executed.''' + char(13) + char(10)
        set @string = @string + N'                    END' + char(13) + char(10)
        set @string = @string + N'                else' + char(13) + char(10)
        set @string = @string + N'                    BEGIN' + char(13) + char(10)
        set @string = @string + N'                        set @error_message = ''In current mirroring state a normal synchronised failover is not possible.''' + char(13) + char(10)
        set @string = @string + N'                    END' + char(13) + char(10)
        set @string = @string + N'            END' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'    END' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'if @actionid = 1 -- EMERGENCY MODE --' + char(13) + char(10)
        set @string = @string + N'    BEGIN' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'        -------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        -- check the mirroring state' + char(13) + char(10)
        set @string = @string + N'        -------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        set @mirror_status = (select mirroring_state from sys.database_mirroring where database_id = @database_id)' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'        if @mirror_status <> 4' + char(13) + char(10)
        set @string = @string + N'            BEGIN' + char(13) + char(10)
        set @string = @string + N'                set @string = N''''' + char(13) + char(10)
        set @string = @string + N'                set @string = N''ALTER DATABASE '' + CAST(@database_name as varchar) + '' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS''' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                exec sp_executesql @string' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                set @error_message = ''Forced failover with possible data loss. Principal was unavailable.''' + char(13) + char(10)
        set @string = @string + N'            END' + char(13) + char(10)
        set @string = @string + N'        else' + char(13) + char(10)
        set @string = @string + N'            BEGIN' + char(13) + char(10)
        set @string = @string + N'                set @error_message = ''In current mirroring state a normal synchronised failover is not possible.''' + char(13) + char(10)
        set @string = @string + N'            END    ' + char(13) + char(10)
        set @string = @string + N'    END' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'---------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Log error message to logging table' + char(13) + char(10)
        set @string = @string + N'---------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'    INSERT INTO [dbo].[tblFailoverErrors]' + char(13) + char(10)
        set @string = @string + N'    (' + char(13) + char(10)
        set @string = @string + N'    [group_id]' + char(13) + char(10)
        set @string = @string + N'    ,[database_id]' + char(13) + char(10)
        set @string = @string + N'    ,[error_date]' + char(13) + char(10)
        set @string = @string + N'    ,[error_time]' + char(13) + char(10)
        set @string = @string + N'    ,[error_message]' + char(13) + char(10)
        set @string = @string + N'    )' + char(13) + char(10)
        set @string = @string + N'    VALUES' + char(13) + char(10)
        set @string = @string + N'    (' + char(13) + char(10)
        set @string = @string + N'@groupid' + char(13) + char(10)
        set @string = @string + N'    ,@database_id' + char(13) + char(10)
        set @string = @string + N'    ,convert(date,GETDATE(),120)' + char(13) + char(10)
        set @string = @string + N'    ,convert(time,getdate(),0)' + char(13) + char(10)
        set @string = @string + N'    ,@error_message' + char(13) + char(10)
        set @string = @string + N'    )' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'       FETCH NEXT FROM failover_worklist' + char(13) + char(10)
        set @string = @string + N'       INTO @database_id, @database_name' + char(13) + char(10)
        set @string = @string + N'    END' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'CLOSE failover_worklist' + char(13) + char(10)
        set @string = @string + N'    DEALLOCATE failover_worklist' + char(13) + char(10)
 
        exec sp_executesql @string        
 
    END
----------------------------------------------------------------
GO
 
----------------------------------------------------------------
-- End of Script
----------------------------------------------------------------

Posted On Wednesday, January 16, 2013 11:31 PM | Comments (0) |

Force shrink of transaction log after a full backup

After installing vCenter and connecting it to a SQL Server 2008R2 database server and making it high available with the help of database mirroring, somthing anoying started to take place. After a full backup the transaction log does NOT get 'truncated' and shrunk. So within a day or 5 my transaction log drive was full and vCenter refused to operate. After a bit of research it turns out that vCenter keeps an open transaction to the tailend of the transaction. Even during a full backup. So this open transaction stops any truncation and or shrinking of the transaction log. The VMWare white paper states that a succesfull backup of the database will only happen if you stop the vCenter service, make a backup and then start the vCenter service again. Due to several reasons, this is not a preffered way of ensuring a proper backup with a shrunk transaction log. The following code gives you the option to force a shrink of the transaction log.

Only use this solution if the possible dataloss is acceptable.

---------------------------------------------------------------------
-- Backup_Tran
---------------------------------------------------------------------
-- Author: Remko de Boer
-- Created: 2012-06-18
-- Description: Create a transaction log backup for all databases that are 
-- mirrored and in a synchronised state
---------------------------------------------------------------------
-- Version: 1.0 initial
---------------------------------------------------------------------
-- Database state:
-- 0 = ONLINE 
-- 1 = RESTORING
-- 2 = RECOVERING
-- 3 = RECOVERY_PENDING
-- 4 = SUSPECT
-- 5 = EMERGENCY
-- 6 = OFFLINE
--
-- Mirroring state:
-- 0 = Suspended
-- 1 = Disconnected from the other partner
-- 2 = Synchronizing 
-- 3 = Pending Failover
-- 4 = Synchronized
-- 5 = The partners are not synchronized. Failover is not possible now.
-- 6 = The partners are synchronized. Failover is potentially possible.
--
-- Shrink enabled
-- 0 = No
-- 1 = Yes
--
-- Overwrite backup
-- 0 = No, backup files will be appended
-- 1 = Yes, previous backups will be overwritten
---------------------------------------------------------------------
ALTER proc [dbo].[spBackup_tran]
AS
-- Declare Variables ------------------------------------------------
declare @name varchar(150)
declare @id int
declare @backup_root varchar(500)
declare @backup_path varchar(250)
declare @backup_file varchar(250)
declare @mirror_state int
declare @database_state int
declare @shrink_enabled int
declare @overwrite_backup int
declare @backup_date varchar(20)
declare @kill int
declare @killstring nvarchar(1000)
---------------------------------------------------------------------
 
-- Set Variables ----------------------------------------------------
set @backup_root = 'D:\Beheer\SQL_Backup\'
set @mirror_state = 4
set @database_state = 0
set @shrink_enabled = 1
set @overwrite_backup = 1
set @kill = 0
---------------------------------------------------------------------
 
---------------------------------------------------------------------
set @backup_path = @backup_root + CAST(@@SERVERNAME as varchar) + '\'
set @backup_date = replace(replace(replace(convert(varchar,CONVERT(datetime,getdate(),120),120),'-',''),' ',''),':','')
--select @backup_date
---------------------------------------------------------------------
 
declare transactie cursor for
select
db.name,
db.database_id
from 
sys.databases db
inner join sys.database_mirroring dbm on db.database_id = dbm.database_id
where 
db.[state] = @database_state
and db.recovery_model = 1
and dbm.mirroring_state = @mirror_state
open transactie
FETCH NEXT FROM transactie
INTO @name, @id
WHILE @@FETCH_STATUS = 0
BEGIN
-- set database to single user -------------------------------------------
if @kill = 1
BEGIN
set @killstring = N'ALTER DATABASE [' + @name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
 
exec sp_executesql @killstring
END
--------------------------------------------------------------------------
 
 
-- Backup transactielog --------------------------------------------------
set @backup_file = ''
 
-- DEBUG -------------
-- select @backup_file
----------------------
 
 
if @overwrite_backup = 0
BEGIN
set @backup_file = @backup_path + @name + '_' + @backup_date + '.trn'
 
BACKUP LOG @name TO DISK = @backup_file WITH NOINIT
END
 
if @overwrite_backup = 1
BEGIN
set @backup_file = @backup_path + @name + '.trn'
 
BACKUP LOG @name TO DISK = @backup_file WITH INIT
END
--------------------------------------------------------------------------
 
 
-- shrink transactielog --------------------------------------------------
if @shrink_enabled = 1
BEGIN
declare @string nvarchar(500)
declare @log_name varchar(250)
DECLARE @ParmDefinition nvarchar(500)
declare @string1 nvarchar(500)
set @string = N'select @log= name from ' + @name + '.sys.database_files where type = 1'
SET @ParmDefinition = N'@log varchar(250) OUTPUT';
execute sp_executesql @string, @ParmDefinition, @log = @log_name output
set @string1 = 'use ' + @name + ' DBCC SHRINKFILE ([' + @log_name + '],1, TRUNCATEONLY)'
-- DEBUG ----------
--select @log_name
--select @string1
-------------------
 
execute sp_executesql @string1
 
 
END
--------------------------------------------------------------------------
 
-- set database to multi user -------------------------------------------
if @kill = 1
BEGIN
set @killstring = 'ALTER DATABASE [' + @name + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE'
 
execute sp_Executesql @killstring 
END
--------------------------------------------------------------------------
 
 
FETCH NEXT FROM transactie
INTO @name, @id
END
CLOSE transactie
DEALLOCATE transactie

Posted On Wednesday, January 16, 2013 3:07 PM | Comments (0) |

Powered by: