A Script A Day - Day 22 - Database Backup

Today’s script follows on from Day 16’s script which was about database restores.  In a backwards way the script is a simple backup database script with two backup commands.  For all my production servers I have custom maintenance routines which take care of transaction log and full backups along with a whole host of other maintenance tasks.  But for a one off backup lets say before decomissioning an old server or before running some test data scripts in development then the syntax to backup a database doesn’t get much easier!  This is yet another example of T-SQL being better than using the GUI as it is far quicker ;)

Now as with my script on Day 16 not everyone knows T-SQL and thus wouldn’t be able to backup a database using it.

So I’ve included two backup database commands one will backup the SQLServer365 database to disk and one will backup the SQLServer365 database to disk with compression.  Backup compression was introduced in SQL Server 2008, details can be found on Technet.  Basically backup compression will generally save you disk space, in the example here on my instance the backup without compression was 146MB the backup with compression was 10MB!

/*

      -----------------------------------------------------------------

      Backup Database

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE master;

GO

-- Backup database

BACKUP DATABASE SQLServer365

TO DISK = 'D:\SQL\Backup\SQLServer365_20120224.bak';

GO

-- Backup database with compression

BACKUP DATABASE SQLServer365

TO DISK = 'D:\SQL\Backup\SQLServer365_20120224_Compressed.bak'

WITH COMPRESSION;

GO

Enjoy!

Chris

A Script A Day - Day 21 - The HAVING Clause

Today’s post is about the HAVING clause which specifies a search condition for a group or an aggregate. HAVING is typically used in a GROUP BY clause and the two biggest uses I have for HAVING are SUM() for reporting and COUNT() for hunting down invalid data.  In the script below we create a HavingBlog table and insert some test data.  We then return a count of server names by data centre where the COUNT(ServerName) > 1.

Now, yes I know in an ideal world this would never happen!  You wouldn’t have two servers with the same name in the same data centre, this is where constraints come into play.  In reality not all functional dependancies are as obvious as this and some are not catered for during the design phase, when this is the case we need to have a look into a problem which is where HAVING can help us out.

/*

      -----------------------------------------------------------------

      HAVING Clause

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE SQLServer365;

GO

--Create test table

IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID('[dbo].[HavingBlog]') AND [type] = 'U')

DROP TABLE [dbo].[HavingBlog];

GO

CREATE TABLE HavingBlog

      (

            HavingBlogID INT IDENTITY (1,1),

            DataCentre VARCHAR(30),

            ServerName VARCHAR(15)

      );

GO

-- Insert test data

INSERT INTO HavingBlog

VALUES ('Manchester','MAN-LIVE-SQL01');

INSERT INTO HavingBlog

VALUES ('Manchester','MAN-LIVE-SQL02');

INSERT INTO HavingBlog

VALUES ('Manchester','MAN-LIVE-SQL02');  

INSERT INTO HavingBlog

VALUES ('London','LON-LIVE-SQL01');

INSERT INTO HavingBlog

VALUES ('London','LON-LIVE-SQL02');

INSERT INTO HavingBlog

VALUES ('London','LON-LIVE-SQL03');

INSERT INTO HavingBlog

VALUES ('Nottingham','NOT-LIVE-SQL01');

GO

-- Return count of servernames by data centre

SELECT

      DataCentre,

      ServerName,

      COUNT(ServerName) AS [NumberOfServers]

FROM

      SQLServer365.dbo.HavingBlog

GROUP BY DataCentre, ServerName

HAVING COUNT(ServerName) > 1;

GO

Enjoy!

Chris

A Script A Day - Day 20 - User Database VLF Count

Today’s script will execute DBCC LOGINFO for all user databases.  This is a script I use to monitor the number of Virtual Log Files (VLF’s).  Kimberly Tripp has a great article on VLF’s here.

/*

      -----------------------------------------------------------------

      User Database VLF Count

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Set database context

USE master;

GO

-- Declare variables

DECLARE @DatabaseName VARCHAR(100)

DECLARE @MinDatabaseID INT

DECLARE @MaxDatabaseID INT

DECLARE @SQL VARCHAR(200)

           

-- Check for temporary table and drop it if it exists

IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL

    DROP TABLE [#Database] ;

-- Create temporary table

CREATE TABLE #Database

    (

      ID INT IDENTITY(1, 1),

      DatabaseName VARCHAR(100)

    )

       

-- Check for existing user databases

IF EXISTS ( SELECT  name

            FROM    sys.databases

            WHERE   database_id > 4

                    AND name NOT IN ( 'ReportServer', 'ReportServerTempDB',

                                      'distribution' ) )

    BEGIN

            -- Insert all database names into a temporary table

        INSERT  INTO #Database ( DatabaseName )

                SELECT  name

                FROM    sys.databases

                WHERE   database_id > 4

                        AND name NOT IN ( 'ReportServer', 'ReportServerTempDB',

                                          'distribution' )

                                                

            -- Set variables for the loop            

        SELECT  @MinDatabaseID = MIN(ID),

                @MaxDatabaseID = MAX(ID)

        FROM    #Database

            -- Begin loop

        WHILE @MinDatabaseID <= @MaxDatabaseID

            BEGIN

                        -- Get databaseName

                SELECT  @DatabaseName = DatabaseName

                FROM    #Database

                WHERE   ID = @MinDatabaseID

           

                        -- Build command

                SET @SQL = 'DBCC LOGINFO(' + '''' + @DatabaseName + ''''

                    + ');'

           

                        -- Try Catch block to execute SQL and handle errors              

                BEGIN TRY

                              -- Execute SQL

                    EXEC ( @SQL

                        )

                    PRINT 'DBCC LOGINFO RUN FOR ' + @DatabaseName

                END TRY

                BEGIN CATCH

                    SELECT  @DatabaseName,

                            message_id,

                            severity,

                            [text],

                            @SQL

                    FROM    sys.messages

                    WHERE   message_id = @@ERROR

                            AND language_id = 1033 -- British English

                END CATCH

           

                        --Get the next databasename ID

                SET @MinDatabaseID = @MinDatabaseID + 1    

                  -- End loop

            END

    END

GO

Enjoy!

Chris

A Script A Day - Day 19 - Remove Virtual Subscriptions

Today’s script is to help replication performance.  It was something I learned from my resident replication expert Paul Anderton.  Below is a description of virtual subscriptions.

If Immediate_Sync is set when a publication is created then virtual subscriptions can occur.  These can affect the "Distribution Clean Up: distribution" SQL job and the "msrepl_commands" table.  By Default the job runs every 10 mins and removes replicated commands from the "msrepl_commands" table dependant on the @min_distretention value (0 hrs is default).  If virtual subscriptions are present then the @min_distretention value is ignored and all replicated commands will only be removed after the @max_distretention is reached (72 hrs is default).

This script is one I have run on all my servers serving as a distributor to remove the virtual subscribers.  The performance gain in all instances is fantastic the distribution clean up job runs a lot faster and replication latency (number of undistributed commands) is dramatically reduced.

/*

      -----------------------------------------------------------------

      Remove Virtual Subscriptions

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Set database context

USE distribution;

GO

-- Get undelivered commands

SELECT

      *

FROM

      MSdistribution_status

ORDER BY

      UndelivCmdsInDistDB DESC

-- Check for virtual subscriptions

SELECT

      msp.publication,

      mss.publisher_db,

      mss.publication_id,

      mss.subscriber_id,

      mss.subscriber_db,

      mss.subscription_type,

      mss.[status]

FROM

      distribution.dbo.MSsubscriptions mss

      left join distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

GROUP BY

      msp.publication,

      mss.publisher_db,

      mss.publication_id,

      mss.subscriber_id,

      mss.subscriber_db,

      mss.subscription_type,

      mss.[status];

GO

-- Remove virtual subscriptions

DECLARE @minid INT

DECLARE     @maxid INT

DECLARE @pubname VARCHAR(100)

DECLARE     @pubdb VARCHAR(100)

SELECT

      @minid = MIN(mss.publication_id),

      @maxid =  MAX(mss.publication_id)

FROM

      distribution.dbo.MSsubscriptions mss

      INNER JOIN distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

WHERE

      mss.subscriber_db = 'virtual'

WHILE @minid <= @maxid

BEGIN

      SELECT

            @pubname = msp.publication,

            @pubdb = mss.publisher_db

      FROM

            distribution.dbo.MSsubscriptions mss

      INNER JOIN distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

      WHERE

            mss.subscriber_db = 'virtual'

            AND mss.publication_id = @minid

           

                        EXEC ('

                        EXEC ' + @pubdb + '.dbo.sp_changepublication

                        @publication = ' + @pubname + ',

                        @property = ''allow_anonymous'',

                        @value = ''false'' ;

                        ')

                        EXEC ('

                        EXEC ' + @pubdb + '.dbo.sp_changepublication

                        @publication = ' + @pubname + ',

                        @property = ''immediate_sync'',

                        @value = ''false'' ;

                        ')

SELECT

      @minid = MIN(mss.publication_id)

FROM

      distribution.dbo.MSsubscriptions mss

      inner join distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

WHERE

      mss.subscriber_db = 'virtual'

      AND mss.publication_id > @minid

END;

GO

-- Check for virtual subscriptions

SELECT

      msp.publication,

      mss.publisher_db,

      mss.publication_id,

      mss.subscriber_id,

      mss.subscriber_db,

      mss.subscription_type,

      mss.[status]

FROM

      distribution.dbo.MSsubscriptions mss

      left join distribution.dbo.MSpublications msp ON mss.publication_id = msp.publication_id

GROUP BY

      msp.publication,

      mss.publisher_db,

      mss.publication_id,

      mss.subscriber_id,

      mss.subscriber_db,

      mss.subscription_type,

      mss.[status];

GO

Enjoy!

Chris

A Script A Day - Day 18 - Viewing System Configuration

Today’s script is something I’ve learned today, it’s cool learning new stuff!  When viewing server configurations in the past I’ve used the below;

EXEC sp_configure 'show advanced options',1;

GO

RECONFIGURE WITH OVERRIDE;

GO

EXEC sp_configure;

GO

This is all fine when making changes to the system configuration and you need to run the RECONFIGURE, but the RECONFIGURE command flushes the buffer cache!

An alternative is to use sys.configurations as below.  Check it out, I like it anyway.

/*

      -----------------------------------------------------------------

      Viewing System Configuration

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change db context

USE master;

GO

-- System Configuration

SELECT

      name,

      value,

      value_in_use,

      [description]

FROM

      sys.configurations

ORDER BY

      name ASC;

GO

Enjoy!

Chris