[Code] Powershell - a basic script for all servers

Just acquainting myself with Powershell - I've known about it, but have preferred using Cygwin's shell. However, I wanted to run some code against all servers, and this seemed a good time to try with Powershell.

I found this original script, which would run the same code against all servers in a list. (http://www.quicksqlserver.com/2009/01/powershell-sqlcmd-and-invoke-expression.html)

foreach ($svr in get-content "C:\MyInstances.txt"){
    $svr
    invoke-expression "SQLCMD -E -S $svr -i createMyuser.sql"
   
}

So then I adapted it - using SQLCMD /Lc to get a list of servers on the network, print the server name, then run SQLCMD against each server, running the c:\simplemodel.sql script (while you can run SQL inline, it kept parsing the @@ and choking on it). Not foolproof, mind you, but a good starting point.

foreach ($svr in (sqlcmd /Lc))
{
        $svr
        invoke-expression "SQLCMD -E -S $svr -i c:\simplemodel.sql"
}

[Maint] Quick and Dirty maintenance

Is this suitable most places? No. Is it fast and easy? Yes.

Reindex all tables in a database (2000 & 2005)

exec sp_MSforeachtable "DBCC DBREINDEX ('?')"

or

EXEC sp_MSforeachtable "print '?' DBCC DBREINDEX ('?', ' ', 85)"

Update statistics in a database
For 2000 (since sp_updatestats can break certain things)

EXEC sp_MSforeachtable "update STATISTICS ?"

For 2005:

sp_updatestats

[Cooking] Marathon weekend

So, overall a good weekend. If ever there was a post of mine that qualified as "useless twitter feed", this would be it.

I made the following this Sunday:
Latkes for breakfast - a bit salty when I doubled the recipe. Maybe I subbed Tbsp for Tsp?

Dinner:
Ham (prefab from Sam's), Mixed veggies (prefab steamer bag), Prefab bread (I know, I know - just didn't have time or forethought to do something special), and I made:
Glazed Carrots - worked well, but for some reason I needed to cook them for longer than stated in the recipe. It took a while, and while the glaze wasn't perfect (next time try 1.5 cups of Ginger Ale), it was passable.
Bag Lady's Favorite Chocolate Pound Cake. Don't make this in a Bundt pan - it'll overflow. I used an Angel food pan, and it worked perfectly. Pulled it out after 85 minutes and it was done and came out moist. I used the Hershey's Perfectly Chocolate Icing (recipe below) and that worked wonderfully with it.

I do need to see if there's some sort of liquid-center bundt-cake recipe, where when it bakes enough it's cake, and where it's not it's pudding-like. That would be yummy.

Here's the recipe - I didn't make the cake, just the icing.
http://www.hersheys.com/recipes/recipes/detail.asp?id=184
"PERFECTLY CHOCOLATE" CHOCOLATE FROSTING

1/2 cup (1 stick) butter or margarine
2/3 cup HERSHEY'S Cocoa
3 cups powdered sugar
1/3 cup milk
1 teaspoon vanilla extract

Melt butter. Stir in cocoa. Alternately add powdered sugar and milk, beating to spreading consistency. Add small amount additional milk, if needed. Stir in vanilla. About 2 cups frosting.

[Backups] Restoring Litespeed archival backups to secondary server

After a year or so, databases on my primary server get moved to a secondary server. I wrote the below script to look in a particular for 1 backup, then it determines where to restore it based off the database name. Hopefully someone else can use this.

DECLARE @backup_directory VARCHAR(500), @Verified VARCHAR(100), @full_backup_name VARCHAR(600),
  @database_name sysname, @restore_directory VARCHAR(500), 
  @logical_name_log  VARCHAR(50), @physical_name_log  VARCHAR(50),
  @logical_name_data VARCHAR(50), @physical_name_data VARCHAR(50)
  ,@with_data VARCHAR(200), @with_log VARCHAR(200)


---------------------------------------------------------------------
--put the name of the folder with the backup you want restored here--
---------------------------------------------------------------------
SET @backup_directory = '\\servername\share\databasefolderbackup'


IF RIGHT(@backup_directory, 1) <> '\'
 SET @backup_directory = @backup_directory + '\'

IF EXISTS 
 (
 SELECT * FROM dbo.sysobjects 
 WHERE id = OBJECT_ID(N'tempdb.[dbo].[#listing]') 
 AND OBJECTPROPERTY(id, N'IsUserTable') = 1
 )
DROP TABLE #Listing
create table #Listing
(resultant nvarchar (255))

--Find the backups in the given folder
declare @dirlist varchar(500)
select @dirlist = 'exec master..xp_cmdshell ''dir /b "'+  @backup_directory + '*"'''
insert #Listing exec (@dirlist)

--get the most recent 
select @Verified = MAX(resultant) from #listing WHERE resultant LIKE '%.bak'
DROP TABLE #Listing

--get a list of all logical files within the backup so we can restore with the right data
SELECT @full_backup_name = @backup_directory + @Verified

if object_id('tempdb..#database_details') is not null
    DROP TABLE #database_details

CREATE TABLE #database_details 
 (
  LogicalName sysname, 
  PhysicalName varchar(500), 
  [TYPE] VARCHAR(2),
  FileGroupName varchar(50),
  [Size] VARCHAR(50),
  [MaxSize] VARCHAR(50))
INSERT INTO #database_details
 EXEC MASTER..xp_restore_filelistonly @filename = @full_backup_name
 
--get the names of everything.
SELECT @logical_name_data = LogicalName, @physical_name_data = PhysicalName
 FROM #database_details WHERE [TYPE] = 'D'
SELECT @logical_name_log = LogicalName, @physical_name_log = PhysicalName 
 FROM #database_details WHERE [TYPE] = 'L'

SELECT @database_name = @logical_name_data

--set up the folders where the restore will automatically go. 
SELECT @physical_name_data = CASE 
 WHEN @logical_name_data LIKE '2009%' THEN 'L:\2009\'
 WHEN @logical_name_data LIKE '2008%' THEN 'M:\2008\' 
 ELSE 'not known'
 END 
 + right(@physical_name_data, CHARINDEX('\', reverse(@physical_name_data))-1)

SELECT @physical_name_log = CASE 
 WHEN @logical_name_data LIKE '2009%' THEN 'L:\2009_Logs\'
 WHEN @logical_name_data LIKE '2008%' THEN 'M:\2008_Logs\' 
 ELSE 'not known'
 END 
 + right(@physical_name_log, CHARINDEX('\', reverse(@physical_name_log))-1)

--need to set these separately since you can't call within the SP
SELECT @with_data = 'MOVE "' + @logical_name_data + '" TO "' + @physical_name_data + '"'
SELECT @with_log = 'MOVE "' + @logical_name_log  + '" TO "' + @physical_name_log  + '"'

--Now do a restore with MOVE.  
exec master..xp_restore_database @database=@database_name
  , @filename= @full_backup_name
  , @with = @with_data
  , @with = @with_log 

[Objects] dropping objects via code

The following code works in both SQL 2000 and SQL 2005.

Tables

if object_id('tempdb..#database_details') is not null
    DROP TABLE #database_details

Stored Procedures

IF EXISTS 
 (
 SELECT * FROM dbo.sysobjects 
 WHERE id = OBJECT_ID(N'[dbo].[the_procedure_name]') 
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1
 )
DROP PROCEDURE the_procedure_name

Alternatively, the following code will CREATE a dummy SP if it doesn't exist, then ALTER it. This way you will only CREATE, not DROP, which can come in handy in certain circumstances, since it will save permissions. Note that this uses INFORMATION_SCHEMA, which is more portable than sysobjects.

IF NOT EXISTS 
 (
 SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
 WHERE ROUTINE_NAME = 'the_routine_name' 
 and routine_type = 'PROCEDURE' --could also be 'FUNCTION' for a function
 )
        EXEC ('CREATE PROC dbo.the_procedure_name AS SELECT 1')
GO
ALTER PROCEDURE dbo.the_procedure_name
AS
SELECT  *
FROM    myTable

[Backups] Determine your database growth via backup history

One thing about backups in SQL Server is that the history is kept forever, unless you clean it up using a maintenance plan, or one of the not-very-well-documented SPs.

But we can have it work for us.
Case in point - database growth estimations. This is a basic view that will show what your database growth has been like for the past 60 days. Turn it into a chart with reporting services, and you can see what's growing, at what rate, and what you need to be concerned with.

Yes, this is pretty basic code, but I hadn't seen anybody do this before.
And obviously, if you're cleaning up your backup history this won't necessarily do much.

CREATE VIEW [dbo].[backup_history]
as
SELECT 
 server_name,
 DATABASE_name,
-- catalog_family_number,  --not sure what this does; unclear in BOL
 backup_size/1000000 AS backup_size,
 CONVERT(CHAR(12),backup_start_date,101) AS backup_date--, 
-- in case you want to look at a particular type of backup
-- CASE [type]
--  WHEN 'D' then 'Database'
--  WHEN 'I' then 'Differential database'
--  WHEN 'L' then 'Log'
--  WHEN 'F' then 'File or filegroup'
--  WHEN 'G' then 'Differential file'
--  WHEN 'P' then 'Partial'
--  WHEN 'Q' then 'Differential partial'
--  END AS Backup_Type,
-- [NAME], 
-- [description] 
FROM msdb.dbo.backupset 
WHERE [TYPE] IN ('D','F') --full backups, though tlogs could be interesting
AND server_name = @@SERVERNAME
AND database_name NOT IN ('msdb', 'MASTER', 'model')
AND backup_start_date > GETDATE()-60
--ORDER BY SERVER_name, DATABASE_name, backup_start_date, catalog_family_number

[Entree] Gordon Ramsey Cookalong Lasagna, Take 2

I've been following Gordon Ramsey's "Cookalong Live", albeit a bit delayed. However, I have to share and show off my attempt at his Lasagna Al Forno. The first time I did full mise en place, and it took about 75 minutes to put together. The second time it took about 45 minutes, and is utterly fantastic. Only takes a few ingredients that you might not have, nothing really too special (except for the pre-cooked lasagna sheets), and comes together VERY easily.

Highly recommended. There's even videos on how to make it.
Two hints
1) Tomato paste, not puree - the brits apparently name things differently.
2) Mince - ground beef, about 2/3 - 3/4 of a pound. I use 85/15 ground beef and it comes out wonderfully.

http://www.channel4.com/food/recipes/chefs/gordon-ramsay/gordon-s-lasagne-recipe_p_1.html

And since they can't be arsed to keep the recipe in one place (or even searchable on their own site...)

2 tbsp olive oil
½ large onion, peeled
1 large carrot, peeled
2 cloves garlic, peeled
2 pinches dried oregano
300g minced beef
1 tbsp tomato purée
1 tbsp Worcestershire sauce
1 bay leaf
30ml (2 tbsp) red wine
1 x 400g tin chopped tomatoes
50ml milk
Salt and freshly ground black pepper
For the sauce
25g butter
25g flour
300ml milk
Pinch of ground nutmeg
60g Cheddar cheese, grated
30g Parmesan cheese, grated
6 sheets of 'non-cook' lasagne sheets
For the salad
1 tsp Dijon mustard
1 tbsp white wine vinegar
2-3 tbsp olive oil
Salt and pepper
1 x round lettuce head, rinsed and dried
METHOD

How to make Gordon's classic lasagne al forno
1. Pre-heat the oven to 220C/ gas mark 7.

2. Heat the olive oil in a hot pan. Grate the onion and carrot and crush the garlic before frying together. Season with the bay leaf, a pinch of oregano, Worcestershire sauce and a little salt and pepper. Allow the onion to soften before making a well in the centre of the pan. Place the mince in the middle of the pan and stir to break it up. Add the tomato puree and allow to cook out for 30 seconds. Continue until all the meat has browned nicely. Add the wine and cook off the alcohol before adding the tomatoes. Leave to simmer for a further 2-3 minutes. Finally add the milk, turn off the heat and set aside (watch Gordon's video on how to prepare bolognaise).

3. To make the cheese sauce, first melt the butter in a saucepan. Add the flour and using a wooden spoon, stir to form a paste. Over a gentle heat add a third of the milk, whisking to prevent any lumps forming. Add the rest of the milk a third at a time, whisking as you go. Season with salt and pepper and a pinch of ground nutmeg. Allow the sauce to cook out for another minute before adding the Cheddar cheese. Stir and remove from the heat (watch Gordon's video on how to make the best white sauce).

4. Spoon half of the meat sauce into the bottom of the baking dish and place pasta sheets on top (break the sheets if necessary to avoid any overlapping). Next, pour in just under half of the cheese sauce, and spread evenly using a spatula before spooning the remaining meat on top. Add the final layer of pasta and use the spatula to pour over the remaining cheese sauce.

5. Finish with the grated Parmesan and sprinkle with another pinch of oregano. Add a light seasoning of salt and pepper before cleaning the edges of the dish and placing in the oven to bake for 20-25 minutes, or until golden brown.

6. In the bottom of your salad bowl use a fork to whisk together the mustard, vinegar and olive oil. Season with salt and pepper. Carefully open the head of lettuce, season inside with salt and pepper. Upend the lettuce headfirst into the salad bowl. Holding the root, wipe the leaves around the bowl to coat in the vinaigrette. Twist the root and pull it out. Turn the dressed head of lettuce onto a large plate and gently open out.

7. Portion out the lasagne and serve alongside the salad.

[Russian] Olga's Haluski (halushki/helushki)

No baking on this one, but a really good Russian version of dumplings. This recipe includes all the stuff we serve it with; potatoes, kielbasa, onions. It's very simple, tastes really good, is very filling, and cheap. Russian poverty food, with some meat added.

Ingredients:

• Heluski


• 4 Cups AP flour
• 1.25 cup warm water
• 1 teaspoon salt
• 1 tablespoon oil


• 3 medium potatoes
• Vinegar (serve with food)
• 1-2 pounds of kielbasa/sausage
• 3 medium onions
  • 1 stick of butter

Onions:
First, we need to cook the onions down, since this takes longer than the other steps. Peel and slice 3 medium onions into rings. Put in a pan, add one stick butter, cover, and cook on medium until clarified. Once this occurs, turn it to medium-high (not high but close to it) and let them fry and caramellize. You want to fry the onions. Once you get them nice and brown, turn to low. Yes, there will be an oil slick of butter; that's fine

Meat:
In another pan, cook kielbasa or a sausage. Cut into whatever size you desire (4-5 inch-long pieces work best). Cook and brown to taste, and once cooked set on low. This step can be done at the same time as the boiling, but doing it ahead of time will make it easier if you're not a whiz in the kitchen.


Haluski:
To make the Haluski itself, mix all 4 ingredients together and knead on a floured board. When I did it, it initially was very dry, but as it was kneaded it came together and absorbed all the flour. The consistency when done was not sticky, and texture was almost plasticine. Now, take a hunk of the dough and roll it with your hand into what looks like a snake - a round, long strip of dough about 1 inch thick. Once you have that, take a butter knife or regular non-sharp knife and cut off slices about 1/4-1/2 inch thick. It will deform as you do it; that's fine. The end result looks like this:

Potatoes:
Peel 2-3 Medium potatoes. Add to pot of water, with about 2tbl salt, and boil until about mostly done. At this point, add haluski, all at once (yes, this'll drop the temperature) and boil approximately 4-5 minutes. The haluski will float to the top when done; you can either fish out the potatoes & haluski, or drain.

Serving:
Get a large serving bowl. Pour the haluski and potatoes into it, pour the onions & butter over them, then take the sausage and place it on top.

Eating:
In our family, we use a serving spoon to grab mass quantities of potatoes/heluski, and a piece or two of kielbasa. Pour a splash of vinegar over the potatoes/onions/heluski - even my wife the Southerner agrees on that. Eat.

[Index] Find duplicate indexes

I've been working on indexes lately, if it's not been obvious. I went looking for code to detect identical indexes, and this one seems the best.

http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx

Here's the top one, which looks for identical indexes.

-- exact duplicates
with indexcols as
(
select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as 'index',
c2.name as 'exactduplicate'
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

[Replication] List your subscriptions, including articles, from the subscriber machine

Sometimes you need to know what articles you've received via a subscription. From Computer B (which is a subscriber), you can run this against Computer a (the publisher or distributor) and get a list of articles (tables) that are being replicated. A rare thing to need, but durn handy. You will need a linked server on Computer A that can read from Computer B, and you need to run it against each replicated database.

(remember that are you need to copy even though you can't see the whole line
DECLARE @sqlstatement VARCHAR(1000)
SELECT @sqlstatement = 'SELECT identity(int, 1,1) as ID,
* INTO ##Publications FROM OPENQUERY([computera],
''SET FMTONLY OFF {call replicated_db..sp_helpsubscription}'')'

EXEC(@sqlstatement)
SELECT * FROM ##publications where subscriber = @@servername
DROP TABLE ##publications