Using the Script Task in SSIS to Process Data Files When They Arrive - SQLServerCentral

Using the Script Task in SSIS to Process Data Files When They Arrive - SQLServerCentral

It is fun to do this!

Stairway to Server-side Tracing - Level 9: How to Automate SQL Traces using the Data Collector - SQLServerCentral

Stairway to Server-side Tracing - Level 9: How to Automate SQL Traces using the Data Collector - SQLServerCentral

SQL Server Reporting Services 2012 Permissions

by  Scott Murray

http://www.mssqltips.com/sqlservertip/2793/sql-server-reporting-services-2012-permissions/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20121022

Script to retrieve SQL Server database backup history and no backups

Script to retrieve SQL Server database 

backup history and no backups

By: Tim Ford | Read Comments (4) |  Tim is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999. http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20121013 --------------------------------------------------------------------------------- --Database Backups for all databases For Previous Week --------------------------------------------------------------------------------- SELECT     CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,    msdb.dbo.backupset.database_name,     msdb.dbo.backupset.backup_start_date,     msdb.dbo.backupset.backup_finish_date,    msdb.dbo.backupset.expiration_date,    CASE msdb..backupset.type         WHEN 'D' THEN 'Database'         WHEN 'L' THEN 'Log'     END AS backup_type,     msdb.dbo.backupset.backup_size,     msdb.dbo.backupmediafamily.logical_device_name,     msdb.dbo.backupmediafamily.physical_device_name,      msdb.dbo.backupset.name AS backupset_name,    msdb.dbo.backupset.description FROM   msdb.dbo.backupmediafamily     INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)  ORDER BY     msdb.dbo.backupset.database_name,    msdb.dbo.backupset.backup_finish_date ------------------------------------------------------------------------------------------- --Most Recent Database Backup for Each Database ------------------------------------------------------------------------------------------- SELECT     CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,    msdb.dbo.backupset.database_name,     MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM   msdb.dbo.backupmediafamily     INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE  msdb..backupset.type = 'D' GROUP BY    msdb.dbo.backupset.database_name  ORDER BY     msdb.dbo.backupset.database_name ------------------------------------------------------------------------------------------- --Most Recent Database Backup for Each Database - Detailed ------------------------------------------------------------------------------------------- SELECT     A.[Server],     A.last_db_backup_date,     B.backup_start_date,     B.expiration_date,    B.backup_size,     B.logical_device_name,     B.physical_device_name,      B.backupset_name,    B.description FROM    (    SELECT          CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,        msdb.dbo.backupset.database_name,         MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date    FROM    msdb.dbo.backupmediafamily         INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id    WHERE   msdb..backupset.type = 'D'    GROUP BY        msdb.dbo.backupset.database_name     ) AS A        LEFT JOIN     (    SELECT      CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,    msdb.dbo.backupset.database_name,     msdb.dbo.backupset.backup_start_date,     msdb.dbo.backupset.backup_finish_date,    msdb.dbo.backupset.expiration_date,    msdb.dbo.backupset.backup_size,     msdb.dbo.backupmediafamily.logical_device_name,     msdb.dbo.backupmediafamily.physical_device_name,      msdb.dbo.backupset.name AS backupset_name,    msdb.dbo.backupset.description FROM   msdb.dbo.backupmediafamily     INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  WHERE  msdb..backupset.type = 'D'    ) AS B    ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] ORDER BY     A.database_name ------------------------------------------------------------------------------------------- --Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours ------------------------------------------------------------------------------------------- --Databases with data backup over 24 hours old SELECT    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,    msdb.dbo.backupset.database_name,    MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date,    DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)] FROM    msdb.dbo.backupset WHERE     msdb.dbo.backupset.type = 'D'  GROUP BY msdb.dbo.backupset.database_name  HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))  UNION  --Databases without any backup history SELECT         CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,     master.dbo.sysdatabases.NAME AS database_name,     NULL AS [Last Data Backup Date],     9999 AS [Backup Age (Hours)]  FROM    master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset        ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb' ORDER BY     msdb.dbo.backupset.database_name

Create A Folder With T-SQL - SQLServerCentral By Howard Dunn, 2012/10/16

Create A Folder With T-SQL - SQLServerCentral


/***************************************************************************
***                                                                      ***
*** Title : usp__CreateDirectory                                                   ***
*** Description : Pass a path to the SP and it will create the               ***
*** the relevant folder for you.                                                   ***
***                                                                                        ***
***************************************************************************/

CREATE PROCEDURE usp__CreateDirectory
 @NewFolder    varchar(500)
AS

SET NOCOUNT ON
-- Create a table variable to hold the results of xp_fileexist --
DECLARE @DoesFolderExist
TABLE
(
 FileExists int NOT NULL,
 FileIsDirectory int NOT NULL,
 ParentDirExists int NOT NULL
)
-- Create a table variable to hold the results of xp_fixeddrives --
DECLARE @Drives
TABLE
(
 Drive char(1) NOT NULL,
 FreeSpace int    NOT NULL
)
-- Grab a list of available drives and inse them into the @Drives table vairable --
INSERT INTO @Drives
EXEC master.dbo.xp_fixeddrives
-- Does the drive exist, if not - stop here --
IF LEFT(@NewFolder, 1) NOT IN (SELECT Drive FROM @Drives)
BEGIN
 PRINT 'That drive does not exist'
 RETURN
END
-- Check to see if the folder already exists, if not, create it --
INSERT INTO @DoesFolderExist
EXEC master.dbo.xp_fileexist @NewFolder
IF (SELECT FileIsDirectory FROM @DoesFolderExist) = 0
BEGIN
 EXECUTE master.dbo.xp_create_subdir @NewFolder
 PRINT 'Directory Created'
END
ELSE
 PRINT 'Directory Already Exists'
SET NOCOUNT OFF