Wednesday 29 February 2012

A Script A Day - Day 27 - Missing Database Backups

Today’s script is one I have used many times in the past to check for missing backups.  The script will return the database name and the last full backup date of all databases that are in the FULL recovery model and have not had a FULL database backup in the last 24 Hours.

/*
      -----------------------------------------------------------------
      Missing Database Backups
      -----------------------------------------------------------------
     
      For more SQL resources, check out SQLServer365.blogspot.com

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

      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 msdb;
GO

-- Create temporary table for database names
IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL
      DROP TABLE #Database ;
CREATE TABLE #Database
(
      ID INT IDENTITY (1,1),
      DatabaseName VARCHAR(255)
);
GO
-- Declare variables
DECLARE @Date DATETIME

-- Set variables
SET @Date = GETDATE()-1

-- Get database in FULL recovery WITH a FULL backup in the last 24 hours
INSERT INTO #Database
SELECT DISTINCT
      database_name
FROM
      msdb.dbo.backupset
WHERE
      recovery_model = 'FULL'
      AND [type] = 'D'
      AND backup_finish_date > @Date;

-- Get databases in FULL recovery without a FULL backup in the last 24 hours
SELECT
      b.database_name AS DatabaseName,
      MAX(b.backup_finish_date) AS LastFullBackup
FROM
      msdb.dbo.backupset b
WHERE
      b.database_name NOT IN (SELECT DatabaseName FROM #Database)
      AND b.recovery_model = 'FULL'
      AND b.[type] = 'D'
      AND b.backup_finish_date < @Date
GROUP BY b.database_name
ORDER BY b.database_name ASC;
GO

Enjoy!

Chris

A Script A Day - Day 26 - RESTART a Database Restore

Today’s script is another one based on database restores.  There is a little known clause of the RESTORE DATABASE command called RESTART.  The name of this clause is deceptive, it does not RESTART anything and should actually be called RESUME or CONTINUE.  What it allows you to do is to resume a restore that has failed for a reason other than a SQL Server issue (backup consistancy, lack of space etc).

Take the following example which is simulated by the script.  You start a large backup lets say 500GB restoring before you leave the office one evening which you know from previous restores takes about 4 hours to run.  Now being a DBA you will of course log on ;) to check the restore is succesfull and finish any other tasks required before the database is ready to be used.  You notice the database is in a RESTORING state but your query window with the restore command has errored, after a bit of digging you find that 3 hours and 50 minutes into the restore the server lost power and went down (assume this is a development environment with no UPS or fail over) and the IT operations team brought the server back online.

If you where to restore the database again then it would take another 4 hours to run but by using the RESTART clause the database restore will only take about another 10 minutes!

Now I haven’t used this in anger but have tested it quite a few times on SQL Server 2008 and SQL Server 2008 R2 and have never had a problem.

/*
      -----------------------------------------------------------------
      RESTART a Database Restore
      -----------------------------------------------------------------
     
      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

-- Get a list of database files and locations for the restore
sp_helpfile;
GO

-- Change database context
USE master;
GO
-- Backup database
BACKUP DATABASE SQLServer365 TO DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'
GO

--Restore Database
RESTORE DATABASE SQLServer365 FROM DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'
WITH REPLACE, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf',
MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';
GO

-- STOP SQL SERVER SERVICE WHILE RESTORING

-- Error Message Received on client

-- START SQL SERVER SERVICE

-- There will be a message in SQL log like the below
-- The database 'SQLServer365' is marked RESTORING and is in a state that does not allow recovery to be run.

-- At this point he database is marked as RESTORING and is inaccesible.

-- Change database context
USE master;
GO
-- Restore the database with the RESTART option (this resumes the restore, honestly!)
RESTORE DATABASE SQLServer365 FROM DISK = 'D:\SQL\Backup\SQLServer365_RESTART_Test.bak'
WITH REPLACE, RESTART, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf',
MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';
GO

-- Results
/*
Processed 18192 pages for database 'SQLServer365', file 'SQLServer365_Data' on file 1.
Processed 1 pages for database 'SQLServer365', file 'SQLServer365_Log' on file 1.
RESTORE DATABASE successfully processed 18193 pages in 19.852 seconds (7.159 MB/sec).
*/

Enjoy!

Chris

A Script A Day - Day 25 - Performance Counters

Today’s script is based on performance counters and in particular the sys.dm_performance_counters DMV.  There are many ways in which to view and collect performance counters the most common of which is to use perfmon.  Powershell is another method, I am currently working on a project where I will be collecting a defined set of performance counters using Powershell, I have used Allen White’s example as a starting point.  The counters will be recorded locally on each server for a month and archived to a central repository for reporting purposes.

The script below however uses as I mentioned earlier the sys.dm_performance_counters DMV I have not been selective here as different counters wil be useful in different environments.

If the installation instance of SQL Server fails to display the performance counters of the Windows operating system, use the following Transact-SQL query to confirm that performance counters have been disabled.

SELECT
      COUNT(*)
FROM
      sys.dm_os_performance_counters;
GO

If the return value is 0 rows, this means that the performance counters have been disabled. You should then look at the setup log and search for error 3409, "Reinstall sqlctr.ini for this instance, and ensure that the instance login account has correct registry permissions." This denotes that performance counters were not enabled. The errors immediately before the 3409 error should indicate the root cause for the failure of performance counter enabling.

/*
      -----------------------------------------------------------------
      Performance Counters
      -----------------------------------------------------------------
     
      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

-- Get performance counters
SELECT
      [object_name],
      counter_name,
      instance_name,
      cntr_value,
      cntr_type
FROM
      sys.dm_os_performance_counters;
GO

Enjoy!

Chris

Tuesday 28 February 2012

A Script A Day - Day 24 - Object Qualification

Today’s script is one I use as an example to explain that there is method behind my standards that some people initially see as madness.  I’m sure some people think I am trying to make their job harder and that I get a kick out of telling them to “go away and do it again properly”.  The answer is yes I do get a kick out of it, but not because I am telling them they are wrong because it proves that the standards in place are providing value to the business by adding a layer of protection.

Let me explain the script below.  I create two schemas, two tables (one in each schema) and populate both with one record.  I then create two logins and two users, each user has a different default schema (one of each of the schemas created earlier).  These objects are to simply support the example.

Now lets assume that SchemaBlogUser1 is a developer and SchemaBlogUser2 is a DBA the process that I am trying to demonstrate is;

Developer creates and submits a script
DBA executes the script
Problems arrise with users reporting errors in the application
DBA investiagtes the issue

What the DBA ultimately finds is that because the script the developer submitted did not qualify the table with a schema in the check or the drop statement SQL Server used the default schema of the DBA which was different to that of the developer and ultimately dropped the wrong table!

It is important to remember that standards are there to protect everyone, yes they can make life more difficult in the now, but can save a lot of time and headaches in future, and that includes DBA’s!!!

/*
      -----------------------------------------------------------------
      Object Qualification
      -----------------------------------------------------------------
     
      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"
     
      -----------------------------------------------------------------
*/
USE SQLServer365
GO
-- Create test schemas
CREATE SCHEMA SchemaBlog1 AUTHORIZATION dbo;
GO
CREATE SCHEMA SchemaBlog2 AUTHORIZATION dbo;
GO

-- Create test tables
CREATE TABLE SchemaBlog1.Table1
(
      Table1ID INT IDENTITY (1,1),
      Column1 VARCHAR(50)
);
GO
CREATE TABLE SchemaBlog2.Table1
(
      Table1ID INT IDENTITY (1,1),
      Column1 VARCHAR(50)
);
GO

-- Insert test data
INSERT INTO SchemaBlog1.Table1
VALUES ('This table is in the SchemaBlog1 schema');
GO
INSERT INTO SchemaBlog2.Table1
VALUES ('This table is in the SchemaBlog2 schema');
GO

-- Create Logins
USE [master]
GO
CREATE LOGIN [SchemaBlogUser1] WITH PASSWORD=N'SchemaBlogUser1', DEFAULT_DATABASE=[SQLServer365], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE LOGIN [SchemaBlogUser2] WITH PASSWORD=N'SchemaBlogUser2', DEFAULT_DATABASE=[SQLServer365], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

-- Create users
USE [SQLServer365];
GO
CREATE USER [SchemaBlogUser1] FOR LOGIN [SchemaBlogUser1];
GO
ALTER USER [SchemaBlogUser1] WITH DEFAULT_SCHEMA=[SchemaBlog1];
GO
EXEC sp_addrolemember N'db_owner', N'SchemaBlogUser1';
GO
CREATE USER [SchemaBlogUser2] FOR LOGIN [SchemaBlogUser2];
GO
ALTER USER [SchemaBlogUser2] WITH DEFAULT_SCHEMA=[SchemaBlog2];
GO
EXEC sp_addrolemember N'db_owner', N'SchemaBlogUser2';
GO

-- SchemaBlogUser1 created the below script to drop table1 -- DO NOT RUN THIS PART!
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('Table1')
                    AND [type] = 'U' )
    DROP TABLE Table1;
GO

/*
      Connect to the instance of SQL Server as SchemaBlogUser2 to run the script
*/

-- Sets the execution context to SchemaBlogUser2 (to mimic the user executing the script)
EXECUTE AS USER = 'SchemaBlogUser2';
GO
-- Drop the table
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('Table1')
                    AND [type] = 'U' )
    DROP TABLE Table1;
GO

-- Revert the execution context (just to show the syntax)
REVERT;
GO

-- Set the execution context to SchemaBlogUser2 (to mimic the user executing the script)
EXECUTE AS USER = 'SchemaBlogUser2';
GO

-- Quick check of record count in SchemaBlog2.Table1
SELECT
      COUNT(*)
FROM  SchemaBlog2.Table1;
GO

/*
      Msg 208, Level 16, State 1, Line 1
      Invalid object name 'SchemaBlog2.Table1'.
*/

-- 0 Records
SELECT 
      *
FROM   
      sys.objects
WHERE  
      [object_id] = OBJECT_ID('Table1')
      AND [type] = 'U';
GO   
     
-- 1 Record
SELECT 
      *
FROM   
      sys.objects
WHERE  
      [object_id] = OBJECT_ID('SchemaBlog1.Table1')
      AND [type] = 'U';
GO
     
-- 0 Records - Wrong table has been dropped!   
SELECT 
      *
FROM   
      sys.objects
WHERE  
      [object_id] = OBJECT_ID('SchemaBlog2.Table1')
      AND [type] = 'U';
GO
     
-- The script should have read!
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   [object_id] = OBJECT_ID('SchemaBlog1.Table1')
                    AND [type] = 'U' )
    DROP TABLE Table1;
GO

Enjoy!

Chris

Monday 27 February 2012

A Script A Day - Day 23 - Find an Object In Job Steps

Today’s script is one which saves me a lot of time on occasion.  On large systems with lets say more than 100 jobs it is esential to manage them properly and the smallest thing like naming conventions help a lot.  So what if you want to check if an object lets say a stored procedure is executed in a job, you aren’t going to search through all 100+ jobs and potentially 1000’s of job steps are you?  Hell No, you use a script, because we don’t like GUI’s ;)

The script below is simple enough just the one join from sysjobs to sysjobsteps and the only thing you need to do is to update the ObjectHere.  It is easy to see that you could use this script to search for any text in a job step not just an object!

/*
      -----------------------------------------------------------------
      Find an Object in Job Steps
      -----------------------------------------------------------------
     
      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 msdb;
GO

-- Find object in a job step
SELECT
      sj.name,
      sj.[enabled],
      sjs.step_id,
      sjs.step_name,
      sjs.command
FROM    msdb.dbo.sysjobs sj
      INNER JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sjs.command LIKE '%ObjectHere%';
GO

Enjoy!

Chris