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