Thursday, April 23, 2009

[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. (

foreach ($svr in get-content "C:\MyInstances.txt"){
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))
invoke-expression "SQLCMD -E -S $svr -i c:\simplemodel.sql"

Tuesday, April 21, 2009

[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 ('?')"

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:

Monday, April 13, 2009

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

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.

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.

Friday, April 10, 2009

[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 + '\'

SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'tempdb.[dbo].[#listing]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1
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'

--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),
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'
+ 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'
+ 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.


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

Stored Procedures

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.

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')
ALTER PROCEDURE dbo.the_procedure_name
FROM myTable

Wednesday, April 8, 2009

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