[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