Update to handle snapshots and not try to back them up in any multi-database queries

SELECT DISTINCT
s_mf.database_id
, *
FROM sys.master_files s_mf
INNER JOIN sys.databases s_db
ON s_mf.database_id = s_db.database_id
WHERE s_mf.state = 0 -- Online
AND s_db.source_database_id is null --Eliminate All Snapshots
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT db_name(s_mf.database_id) in ('tempdb', 'master', 'msdb', 'model') --eliminate temp db

Creating a snapshot

Time to learn something about creating a snapshot  of a database so I wrote a script

CREATE DATABASE [Snapshot Name for queries] ON
( NAME = [Name of the logical to be snap shot, this should include the date time to allow multiple days], FILENAME = 'e:\BackupforSnapshot_20120420.ss' )
AS SNAPSHOT OF [Database to be snap shot name];
GO

SSAS Training Books links

Useful book links from the class

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset
 

For MDX
Fast Track to MDX  Author Mosha Pasumanskyinventory of MDX

SQL Server 2008 White Paper: Analysis Services Performance Guide

Tables Change Auditing

/*
This is used to track database table changes and include the ability to email when tables are altered or dropped. The is accomplished with a DDL trigger. It assumes there is a table in an auditing database to hold the rollback information and a database mail default account.
*/ 


IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Event_Table_changes')
DISABLE TRIGGER [Event_Table_changes] ON DATABASE

GO

USE [Name of Database]
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'Event_Table_changes')DROP TRIGGER [Event_Table_changes] ON DATABASE
GO

USE [Manuals]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE trigger [Event_Table_changes]
on database
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
--print @command


INSERT INTO [AuditDB].dbo.Table_Events
           ([EventType]
           ,[EventTime]
           ,[LoginName]
           ,[UserName]
           ,[ObjectName]
           ,[ObjectType]
           ,[Command]
           ,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile

--if table drop email admin group
IF @EventType = 'DROP_TABLE'
BEGIN
     DECLARE @profile varchar(100)
     DECLARE @EmailTo varchar(128)
     DECLARE @Subject varchar(128)
     DECLARE @Body varchar(max)
     DECLARE @BodyType varchar(25)


     SELECT @profile = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName'  ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) + 'Admin'
     SET @EmailTo = [Add email name]
     SET @Subject = CAST(COALESCE(SERVERPROPERTY ( 'InstanceName'  ), SERVERPROPERTY ( 'ServerName')) AS varchar(80)) +  ' Table has been changed in DB: ' + db_name()
     SET @Body = @Command
     SET @BodyType = 'HTML'

     EXEC msdb.dbo.sp_send_dbmail
     @profile_name = @profile,
     @recipients = @EmailTo ,
     @subject = @Subject,
     @body = @Body ,
     @body_format  = @BodyType
END

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

DISABLE TRIGGER [Event_Table_changes] ON DATABASE
GO

ENABLE TRIGGER [Event_Table_changes] ON DATABASE
GO

New fixed system file full backup

The file creation is not as good as it should have been. Fixed now

USE [master]
GO


DECLARE @rootpath NVARCHAR(100);
set @rootPath = N'\\wkfile03\DiskBackup\wkshptdbtest\';

DECLARE @tDatabases TABLE
( databaseId int
--, DATABASE_NAME varchar(25)
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT s_mf.database_id
FROM sys.databases s_mf
WHERE s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND [NAME] IN ('master', 'model', 'msdb' );

DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId int;

OPEN cursor_usage;
FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @Filepath NVARCHAR(500);
declare @dbname nvarchar(100)
set @dbname = db_name(@dbId);

SET @FilePath = @RootPath + @dbName;
PRINT @FilePath

EXECUTE master.dbo.xp_create_subdir @filepath;

declare @fileName nvarchar(500);
declare @fullPath nvarchar(1000);

--set the date format yyyymmddhhmm for the file name

declare @date nvarchar(10)
declare @hour nvarchar(4)
declare @minute nvarchar(4)

--ensure hour is 2 digit

set @hour = CAST(datepart(hh,getdate()) AS nvarchar(2))

if len(@hour) = 1
set @hour = '0' + CAST(datepart(hh,getdate()) AS nvarchar(2))

--ensure hour is 2 digit

set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))

if len(@minute) = 1
set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename

select @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';

select @fullPath = @filePath + '\' + @fileName

print @filePath;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;

--run the verify

declare @backupSetId as int

select @backupSetId = position
from msdb.dbo.backupset
where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )

if @backupSetId is null
begin
declare @errMsg nvarchar(128)
select @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'
raiserror(@errMsg, 16, 1);
end

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND;
FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

Moving users and schemas

I am in the process of getting rid of universal users for all applications and have to create and move a great number of users from test to production and was looking for easiest way. This is the best I have found so far.

/* This script is used for moving a login and schema from test to
production. This includes creating a new schema and read and write access
The steps are:
1. Go to the test enviroment and select User Name -> CREATE to cliboard
2. Paste the new user information in the block below
3. Select the new user name
4. Highlight the [NewUser] name
5. Run a find and replace  on the new user name (There should be 5)
6. Copy the schema name [NewUserSchema]
7. Replace schema name in the last block
8. Set the password in the first line
9. Run on the correct server
*/


CREATE LOGIN [NewUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[AuditDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- START paste new user info here
USE [AuditDB]
GO

/****** Object:  User [NewUser]    Script Date: 01/10/2011 10:46:51 ******/
GO

CREATE USER [NewUser] FOR LOGIN [NewUser] WITH DEFAULT_SCHEMA=[NewUserSchema]
GO

-- END Paste new user

EXEC sp_addrolemember 'db_datawriter', [NewUser] ;
EXEC sp_addrolemember 'db_datareader', [NewUser] ;

GO


CREATE SCHEMA [NewUserSchema] AUTHORIZATION [NewUser]
GO

Reporting Services and xml

I am currently struggling to create method of scheduling Reporting service subscriptions without having that stupid GUID as the job name. First I needed to learn more about querying the dubiously formatted Reporting services xml.

USE [ReportServer];

/****** Script for SelectTopNRows command from SSMS  ******/

WITH cteSubs (SubscriptionId, Params) AS
(
SELECT
SubscriptionID
,  CAST(CAST([ExtensionSettings] AS NVARCHAR(max)) AS XML) AS params
  FROM [dbo].[Subscriptions] subs
  INNER JOIN [dbo].[Schedule] sch
  ON subs.SubscriptionID = sch.[EventData]
 )
SELECT
SubscriptionID
,(SELECT nref.value('Value[1]', 'nvarchar(50)') Comment FROM Params.nodes('/ParameterValues/ParameterValue') AS R(nref) WHERE  nref.exist('.[Name = "Comment"]') = 1) AS Comment
 FROM cteSubs

This extracts the value of the comment so I can use it for the Job Name later