A Script A Day Finished!

Well that's it, my "A Script A Day" series is finished.  I hope you found the scripts I provided useful, I've used all the scripts in real world DBA tasks.  I tried to provide a variety of scripts covering a number of different areas.  This series I feel will appeal more to new or inexperienced DBA's but I'm sure that some more seasoned DBA's will be able to take something away from it.  It's been a thoroughly enjoyable project that I have taken great pride in doing.  As promised here is the pdf with all the scripts in for easy access.


Thanks to everyone who commented on the posts and special thanks to David Riley, Chris Taylor and Paul Anderton with whom I continue to learn and share my experiences.

A Script A Day - Day 29 - The Importance of Being Idle

Today’s script is also one I used in my migration on Wednesday.  It again uses string manipulation to generate a script, this time the restore database script.  Now granted this quick script wouldn't work if there are any secondary data files and is reliant on the logical file names and file locations etc etc. 

The point of me using this script is because I am very anal when it comes to standards, I like to make sure drive letters and paths are consistent as well as naming conventions for all databases and objects.  Adhering to standards makes your life as a DBA much easier especially when it comes to tasks like migrations.

/*

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

      The Importance of Being Idle (Results To Text Ctrl+T)

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

     

      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

-- Create restore database script

SELECT

      'RESTORE DATABASE ' + [name] + ' FROM DISK = ''D:\Migration\Backup\' + [name] + '_migration_20120301.bak''' +

      ' WITH REPLACE, MOVE ''' + [name] + '_Data''' + ' TO ''D:\Data\' + [name] + '_Data.mdf'',' + ' MOVE ''' + [name] + '_Log''' + ' TO ''L:\Log\' + [name] + '_Log.ldf'';'

FROM

      sysdatabases;

GO

Enjoy!

Chris

A Script A Day - Day 28 - String Manipulation

Today’s script is one that I used earlier this week.  On Thursday I migrated a server from SQL Server 200 to SQL Server 2008 R2.  Now I love migrations, I don’t think I’m weird but I buzz off the addrenaline rush when working under pressure and I get a great deal of satisfaction when a migration is complete.  As part of the migration I backed up the databases ready to be restored to the new server.  As there where a number of databases to backup and of course I don’t use the GUI where I can help it the easiest way for me to generate the backup script prior to the migration was using the below script.  The Query results I then save to a script file ready for use.

/*

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

      String Manipulation (Results To Text Ctrl+T)

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

     

      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

-- Create backup database script

SELECT

      'BACKUP DATABASE ' + [name] + ' TO DISK = ''D:\Migration\Backup\' + [name] + '_migration_20120301.bak''' + ';'

FROM

      sysdatabases;

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

A Script A Day - Day 17 - Possible Poor Indexes

Today’s script is one I’ve taken from a job I use to collect possible poor indexes.  I’ve tweeked it slightly so you can choose the database to run it against. 

It goes without saying that you should never just remove an index without some considerable investigation doesn't it???!!!

/*

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

      Possible poor indexes

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

     

      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 DatabaseNameHere;

GO

-- Get Possible poor indexes

SELECT

      OBJECT_NAME(S.[object_id]) AS [Table Name],

      I.name AS [Index Name],

      I.index_id,

    user_updates AS [Total Writes],

    (user_seeks + user_scans + user_lookups) AS [Total Reads],

    (user_updates - (user_seeks + user_scans + user_lookups)) AS [Difference]

FROM

      sys.dm_db_index_usage_stats AS S WITH (NOLOCK)

      INNER JOIN sys.indexes AS I WITH (NOLOCK) ON S.[object_id] = I.[object_id] AND I.index_id = s.index_id

WHERE

      OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1

      AND s.database_id = DB_ID()

      AND user_updates > (user_seeks + user_scans + user_lookups)

      AND I.index_id > 1

ORDER BY

      [Difference] DESC,

      [Total Writes] DESC,

      [Total Reads] ASC

OPTION (RECOMPILE);

GO

Enjoy!

Chris

A Script A Day - Day 16 - Database Restore

Today’s script is one I have used more times that I care to remember.  As a DBA database backups and restores are your bread and butter, they are second nature (or should be;).  By this I mean good old T-SQL not using SSMS, I can honestly say I have never backed up or restored a database using SSMS, I have used EM in SQL 2000 in my pre DBA days though.

So for all you DBA’s forgive the basic nature of the script but not everyone knows T-SQL and thus wouldn’t be able to restore a database using it.  See my Database 101 post on humility. 

For everyone else the script will restore the SQLServer365 database from a backup and will overwrite the existing files.  It uses RESTORE FILELISTONLY to get the files in the backup and sp_helpfile to get the existing file locations.  The LocicalName column values from RESTORE FILELISTONLY are used to specify what we are MOVEing and the filename column values from sp_helpfile are used to specify where we are MOVEing them TO.

/*

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

      Restore 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"

     

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

*/

-- Set database to the user database

USE SQLServer365;

GO

-- Get file list from backup

RESTORE FILELISTONLY FROM DISK = 'D:\Backups\SQLServer365_20120222.bak';

GO

-- Return database file locations

EXEC dbo.sp_helpfile;

GO

-- Set database context to master

USE master;

GO

-- Restore the database

RESTORE DATABASE SQLServer365

FROM DISK = 'D:\Backups\SQLServer365_20120222.bak'

WITH REPLACE, MOVE 'SQLServer365_Data' TO 'D:\SQL\Data\SQLServer365_Data.mdf', MOVE 'SQLServer365_Log' TO 'D:\SQL\Log\SQLServer365_Log.ldf';

GO

Enjoy!

Chris

A Script A Day - Day 15 - Untrusted Check Constraints

Today’s script comes about because of a recent discussion about check constraints.  The discussion was about the use of WITH NOCHECK and the fact that I don’t like it being used.  My view is that if you are adding a check constraint then you are doing so for a reason so existing data should be validated.  I know it is quicker to add the constraint WITH NOCHECK but you then have a half-arsed solution.

WITH NOCHECK can lead to performance and consistancy problems first off the constraint can’t be used by the Query Optimiser to know what data might exist in the column(s). Second if there is data that invalidates the constraint then queries updating the column to the same value (UPDATE tablename SET columname = columname) will fail.  The errors you see are also often poor making finding the cause of the problem harder.  The decision is ultimately yours but I would avoid using WITH NOCHECK.  The script returns all untrusted check constraints for a given database.

/*

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

      Untrusted Check Constraints

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

     

      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 databasenamehere;

GO

-- Get untrusted check constraints

SELECT

      name,

      [object_id],

      principal_id,

      [schema_id],

      parent_object_id,

      [type],

      type_desc,

      create_date,

      modify_date,

      is_ms_shipped,

      is_published,

      is_schema_published,

      is_disabled,

      is_not_for_replication,

      is_not_trusted,

      parent_column_id,

      [definition],

      uses_database_collation,

      is_system_named

FROM

      sys.check_constraints

WHERE

      is_not_trusted = 1;

GO

Enjoy!

Chris

A Script A Day - Day 14 - Upgrading to SQL 2012

Today’s script is one I have used to test one possible upgrade method from SQL Server 2008 to SQL Server 2012. If truth be told this would be my prefered upgrade method I’ll explain why…

I have database mirroring in production on SQL Server 2008 two physical servers in an active passive cluster configuration as the PRINCIPAL and the FAILOVER PARTNER is a third physical server.  My plan is to create another active passive cluster with SQL Server 2012 installed and configured then break the existing mirroring partnership and setup a new mirroring partnership to the new cluster.  All this work can be done without any downtime to the current environment!  Once the new mirroring partnership is setup I can schedule a failover and a few seconds later I’m on SQL Server 2012 in production.

I can then rebuild the old SQL 2008 PRINCIPAL and FAILOVER PARTNER servers with SQL Server 2012 and create availability groups, Wohooo!  I'm way to excited about availability groups, it opens up so many possibilities!!!

/*

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

      Test upgrading SQL Server 2008 to SQL Server 2012

     

      Server1 is the PRINCIPAL and Server2 is the FAILOVER PARTNER

      The test database is called DenaliHA

      The test table is called HATest

     

      You will need to specify a login to have permissions granted on

      the endpoints

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

     

      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"

     

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

*/

--    *** RUN AT THE PRINCIPAL ***

-- Create test database

USE [master]

GO

CREATE DATABASE [DenaliHA] ON  PRIMARY

( NAME = N'DenaliHA_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DenaliHA_Data.mdf' , SIZE = 1048576KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'DenaliHA_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DenaliHA_Log.ldf' , SIZE = 1048576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [DenaliHA] SET COMPATIBILITY_LEVEL = 100

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [DenaliHA].[dbo].[sp_fulltext_database] @action = 'enable'

end

GO

-- Create test table

USE DenaliHA

GO

CREATE TABLE HATest (

                              HATestID INT IDENTITY(1,1),

                              Forename VARCHAR (100),

                              Surname VARCHAR (100)

                         );

-- Insert some data

INSERT INTO HATest (Forename, Surname) VALUES ('Chris','McGowan')

GO 10000

CREATE CLUSTERED INDEX [IDX_HATest:Composite1] ON HATest (HATestID);

GO

-- Backup database and transaction log

BACKUP DATABASE DenaliHA TO DISK  = 'C:\DenaliHA\DenaliHA.bak';

GO

BACKUP LOG DenaliHA TO DISK = 'C:\DenaliHA\DenaliHA.trn';

GO

-- Create endpoint

USE master

GO

IF NOT EXISTS (   SELECT *

                        FROM sys.endpoints

                        WHERE name = 'DenaliHADatabaseMirroringEndpoint'      )

      CREATE ENDPOINT [DenaliHADatabaseMirroringEndpoint]

      STATE = STARTED

      AS TCP (LISTENER_PORT = 1430, LISTENER_IP = ALL)

      FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,

      ENCRYPTION = REQUIRED ALGORITHM AES);

GO

           

-- Grant permissions on endpoint

IF EXISTS ( SELECT  name

                  FROM    sys.server_principals

                  WHERE   name = '' )                                                                                         -- Must add Login Name

      GRANT CONNECT ON ENDPOINT::DenaliHADatabaseMirroringEndpoint TO [Login Name Here];  -- Must add Login Name

GO

--    *** RUN AT THE FAILOVER PARTNER ***

USE master

GO

-- Create endpoint

IF NOT EXISTS (   SELECT *

                        FROM sys.endpoints

                        WHERE type_desc = 'DATABASE_MIRRORING'    )

      CREATE ENDPOINT [DenaliHADatabaseMirroringEndpoint]

      STATE = STARTED

      AS TCP (LISTENER_PORT = 1440, LISTENER_IP = ALL)

      FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,

      ENCRYPTION = REQUIRED ALGORITHM AES);

GO

-- Grant permissions on endpoint

IF EXISTS ( SELECT  name

                  FROM    sys.server_principals

                  WHERE   name = '' )                                                                                         -- Must add Login Name

      GRANT CONNECT ON ENDPOINT::DenaliHADatabaseMirroringEndpoint TO [Login Name Here];  -- Must add Login Name

GO

-- Copy backup files from server1

-- Get file locations for the restore

USE DenaliHA

GO

sp_helpfile

GO

-- Restore backups

USE master

GO

RESTORE DATABASE DenaliHA FROM DISK  = 'C:\DenaliHA\DenaliHA.bak' WITH REPLACE, MOVE 'DenaliHA_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DenaliHA_Data.mdf', MOVE 'DenaliHA_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DenaliHA_Log.ldf', NORECOVERY;

GO

RESTORE LOG DenaliHA FROM DISK = 'C:\DenaliHA\DenaliHA.trn' WITH REPLACE, MOVE 'DenaliHA_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DenaliHA_Data.mdf', MOVE 'DenaliHA_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DenaliHA_Log.ldf', NORECOVERY;

GO

-- Enable database for mirroring

ALTER DATABASE DenaliHA SET PARTNER = 'TCP://Server1.GPGROUP.COM:1430';

--    *** RUN AT THE PRINCIPAL ***

-- Enable database for mirroring

ALTER DATABASE DenaliHA SET PARTNER = 'TCP://Server2.GPGROUP.COM:1440';

-- Insert some more data to prove the database mirroring session is working

USE DenaliHA

GO

INSERT INTO HATest (Forename, Surname) VALUES ('Chris2','McGowan2');

GO 10000

-- Failover!!!

ALTER DATABASE DenaliHA SET PARTNER FAILOVER;

/*

      It is at this point where the database will be online on the SQL 2012 instance

      NOTE - Databasebase Mirroring will be suspsended and errors like the below will be received;

      'TCP://Server1.GPGROUP.COM:1430', the remote mirroring partner for database 'DenaliHA', encountered error 948, status 2, severity 20. Database mirroring has been suspended.  Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

      Error: 1453, Severity: 16, State: 1.

      This is beacuse Database Mirroring works from SQL 2008 to SQL 2012 for upgrades only!  Mirroring SQL 2012 to SQL 2008 will not work!!!

*/

--    *** RUN AT THE PRINCIPAL ***

-- Remove mirroring

ALTER DATABASE DenaliHA SET PARTNER OFF;

--    *** RUN AT THE FAILOVER PARTNER ***

-- Bring original database online

RESTORE DATABASE DenaliHA WITH RECOVERY;

-- Drop Databases

DROP DATABASE DenaliHA;

GO

--    *** RUN AT THE PRINCIPAL ***

DROP DATABASE DenaliHA;

GO

Enjoy!

Chris