TSQL: Quick and Dirty backups

DECLARE @sql nvarchar(2000)
DECLARE @id int

DECLARE @temp TABLE (id int IDENTITY (1,1), command nvarchar(1000) NULL, done bit NULL)
INSERT @temp(command)
SELECT
'BACKUP DATABASE '
+ QUOTENAME(name)
+ ' TO DISK = '''
+ 'C:\SQLBackups\' + name
+ '_'
+ CONVERT(varchar,GETDATE(),112)
+ CONVERT(varchar,DATEPART(hour,GETDATE()))
+ CONVERT(varchar,DATEPART(minute,GETDATE()))
+ '.BAK'' WITH INIT'
FROM master.sys.databases
WHERE name NOT IN ('tempdb','pubs','northwind')
AND DATABASEPROPERTYEX (name,'status') = 'ONLINE'

WHILE EXISTS (SELECT * FROM @temp WHERE DONE IS NULL)
BEGIN
SELECT @id = (SELECT MIN(id) FROM @temp WHERE done IS NULL)
SELECT @sql = command FROM @temp WHERE id = @id
--SELECT @sql
EXEC (@sql)
UPDATE @temp
SET done = 1 WHERE id = @id
END

TSQL: create last day of month indicator

DECLARE @MonthEndInd bit
DECLARE @MonthEnd char(8)
DECLARE @Today char(8)

SELECT @MonthEnd = CONVERT(varchar , DateAdd(Month, DateDiff(Month, 0, Dateadd(month, 1, getdate())), 0) -1 , 112)
SELECT @Today = CONVERT(varchar,GETDATE(),112)

IF @Today = @MonthEnd
BEGIN
SELECT @MonthEndInd = 1
END
ELSE
SELECT @MonthEndInd = 0

SQL Server Agent: Disable and enable all jobs

CREATE PROC prc_DBA_job_Enable_Disable @penable bit
AS
BEGIN
/****HeaderStart

Author: Kevin Foreman
Date: 10-04-2007
Description:
Enable/disable all jobs on a server based on current enable status
Places all enabled job names into a table in DBAFunctions and loops
through to disable them. Loops through to enable again and then clears table.

Designed to be used twice as a wrapper around a process/task that reqiures
jobs disabled.

Starts auto-start jobs when re-enabling them.

-- CREATE TABLE [tbl_DBA_job_Enable_Disable] (
-- [job_id] [uniqueidentifier] NULL ,
-- [name] [sysname] NOT NULL ,
-- [job_enabled] [tinyint] NULL ,
-- [freq_type] [int] NULL ,
-- [schedule_enabled] [tinyint] NULL ,
-- [last_updated] [datetime] NULL ,
-- [updated_by] [sysname] NOT NULL
-- )


Input: @penable 1 = enable jobs / 0 = disable

Example:
EXEC DBAFunctions.dbo.prc_DBA_job_Enable_Disable @penable = 0
*** [do something] ***
EXEC DBAFunctions.dbo.prc_DBA_job_Enable_Disable @penable = 1

Health Warning: NO ERROR TRAPPING

Version: SQL2000.01.00P
Maintained:


HeaderEnd****/
SET NOCOUNT ON

DECLARE @command TABLE (Command nvarchar(1000) , Done bit)
DECLARE @sql nvarchar(1000)
DECLARE @s nvarchar(1000)

--cleardown and repopulate the holding table if we are disabling jobs
IF @penable = 0
BEGIN

SELECT @s = 'DISABLING jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
DELETE [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]

--get the name and id of enabled jobs
INSERT [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
(job_id,name,job_enabled,freq_type,schedule_enabled,last_updated,updated_by)
SELECT SJ.job_id,SJ.name, SJ.enabled,SJS.freq_type,SJS.enabled, GETDATE(), SYSTEM_USER
FROM msdb.dbo.sysjobs AS SJ
LEFT OUTER JOIN msdb.dbo.sysjobschedules AS SJS
ON SJ.job_id = SJS.job_id
WHERE SJ.enabled = 1

SELECT DISTINCT name FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]

INSERT @command
SELECT DISTINCT 'EXEC msdb.dbo.sp_update_job @job_name = ' + QUOTENAME(name) + ' , @enabled = 0' AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END

ELSE

--enable jobs
BEGIN
SELECT @s = 'ENABLING jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s
SELECT name FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]

INSERT @command
SELECT 'EXEC msdb.dbo.sp_update_job @job_name = ' + QUOTENAME(name) + ' , @enabled = 1' AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]

END

WHILE EXISTS (SELECT Command FROM @command WHERE Done = 0)
BEGIN
SELECT @sql = (SELECT TOP 1 Command FROM @command WHERE Done = 0)
SELECT @sql
--EXEC @sql
UPDATE @command SET Done = 1 WHERE Command = @sql
END

IF @penable = 1
BEGIN
--restart jobs that auto-start with SQL Server Agent
IF EXISTS (SELECT * FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
WHERE freq_type = 64 AND schedule_enabled = 1)
BEGIN
SELECT @s = 'STARTING auto-start jobs on server: ' + QUOTENAME(@@SERVERNAME) + ' at: ' + QUOTENAME(GETDATE())
SELECT @s

DELETE @command
INSERT @command
SELECT DISTINCT 'EXEC msdb.dbo.sp_start_job @job_name = ' + QUOTENAME(name) AS Command , 0 AS Done
FROM [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
WHERE freq_type = 64
AND schedule_enabled = 1

WHILE EXISTS (SELECT Command FROM @command WHERE Done = 0)
BEGIN
SELECT @sql = (SELECT TOP 1 Command FROM @command WHERE Done = 0)
SELECT @sql
--EXEC @sql
WAITFOR DELAY '000:00:05'
UPDATE @command SET Done = 1 WHERE Command = @sql
END
END

--cleardown table
DELETE [DBAFunctions].[dbo].[tbl_DBA_job_Enable_Disable]
END

END

TSQL: Restore commands for al DBs

Generate restore commands for all user databases on a server. To replace curent file locations with a new file location you can strip out the filenames and feed in a paramer for the new data/log folder. Or edit/replace the output.

SELECT SUBSTRING
( 'filename',
LEN('filename') - CHARINDEX('\',REVERSE('filename')) + 2 ,
CHARINDEX('\',REVERSE('filename'))
)

Better still, and to be posted later, have a stanard folder location and naming convention and move the logical files to a standard named file based upon the name of the DB.

/****CommentStart

Author : Kevin Foreman
Date: 21-11-2006
Description:
Generates restore copmmands for all databases on a server
where dbid > 4

Uses WITH MOVE, STATS = 10 and REPLACE

char(13) used to format it nicely if exec in text

Maintained:

CommentEnd****/

SET NOCOUNT ON

-- setup varaibles
DECLARE
@db_name sysname ,
@sql nvarchar(2000) ,
@aCur_db_name sysname ,
@bCur_name varchar(200),
@bCur_filename varchar(200)

DECLARE
@Database_Name TABLE
(
name sysname,
Done bit DEFAULT 0
)

CREATE TABLE
#Database_Files
(
name sysname ,
fileid int,
filename varchar(2000),
filegroup varchar(100) ,
size varchar(32) ,
maxsize varchar(32) ,
growth varchar(32) ,
usage varchar(32)
)

CREATE TABLE
#Database_Files_Summary
(
database_name sysname NULL,
name sysname ,
fileid int,
filename varchar(2000),
)

--get user database names
INSERT
@Database_Name
(Name)
SELECT
sd.name AS 'Name'
FROM
master.dbo.sysdatabases AS sd
WHERE
sd.dbid > 4

-- get user database file info
WHILE EXISTS (SELECT * FROM @Database_Name WHERE Done = 0)
BEGIN
SELECT @db_name =
(SELECT TOP 1 dn.name
FROM @Database_name as dn
WHERE Done = 0)

SELECT @sql = @db_name + '.dbo.sp_helpfile'

INSERT #Database_Files
EXEC sp_executesql @sql

INSERT #Database_Files_Summary
(database_name,name,fileid,filename)
SELECT
@db_name,name,fileid,filename FROM #Database_Files

DELETE FROM #Database_Files

UPDATE @Database_name
SET Done = 1
WHERE name = @db_name
END

--cursor through database names and then files to generate restore commands
--database name cursor
DECLARE aCur CURSOR
FOR SELECT name FROM @Database_Name ORDER BY name ASC
OPEN aCur
FETCH NEXT FROM aCur INTO @aCur_db_name
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @sql = 'RESTORE DATABASE ''' + LTRIM(RTRIM(@aCur_db_name)) + ''' FROM DISK = ''[filename]''' + char(13) + 'WITH ' + char(13)

--database file cursor
DECLARE bCur CURSOR
FOR SELECT name , filename FROM #Database_Files_Summary WHERE database_name = @aCur_db_name ORDER BY fileid ASC
OPEN bCur
FETCH NEXT FROM bCur INTO @bCur_name , @bCur_filename
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM bCur INTO @bCur_name , @bCur_filename
SELECT @sql = @sql + ' MOVE ''' + LTRIM(RTRIM(@bCur_name)) + ''' TO ''' + LTRIM(RTRIM(@bCur_filename)) + ''' ,' + char(13)
END
CLOSE bCur
DEALLOCATE bCur

FETCH NEXT FROM aCur INTO @aCur_db_name
SELECT @sql = @sql + ' STATS = 10 , REPLACE'
--return the restore command
SELECT @sql
END
CLOSE aCur
DEALLOCATE aCur

--cleanup
DROP TABLE #Database_Files
DROP TABLE #Database_Files_Summary

TSQL: Generate sp_configure commands

Generate sp_configure commands from a server to replay on another or after a rebuild. Correct spelling of create before runnig

SET NOCOUNT ON

SELECT '/****--' + @@SERVERNAME

EXEC sp_configure 'show advanced options' , '1'
RECONFIGURE WITH OVERRIDE

SELECT '****/'

CREAT TABLE #temp
(
name varchar(35) ,
minimum varchar(12) ,
maximum varchar(12) ,
config_value varchar(12) ,
run_value varchar(12) ,
)
INSERT #temp
exec sp_configure

SELECT 'EXEC sp_configure ''' + LTRIM(RTRIM(name)) + ''' , ''' + LTRIM(RTRIM(run_value)) + '''' FROM #temp

DROP TABLE #temp

TSQL: Generate Make Directory (MD) commands

Cheap and cherful way to create MD commands from an existing server. Handy for building a new server based upon an old.

/*
CREATE PROCEDURE #DBA_Create_Folder_Create_Batch_File
AS
BEGIN
*/

/****CommentStart

Author: Kevin Foreman
Date: 09-11-2006
Description:

Quick and dirty piece of code to generate make directory (MD) commands
based upon an existing folder structure. Save output to a .BAT/.CMD
file, eyaball and edit as required then call on destination server.

Input:
@drive_letter >> drive that you want folder structure from,
@include_folder1 to @include_folder4 >> 4 top level folders
Output:
MD commands for all subfolder/folders included
HEALTH WARNING:
No error checking

Maintained:


CommentEnd****/

SET NOCOUNT ON

--setup variables
DECLARE
@s varchar(2000) ,
@sql varchar(2000),
@i int ,
@drive_letter char(1) ,
@include_folder1 varchar(200) ,
@include_folder2 varchar(200) ,
@include_folder3 varchar(200) ,
@include_folder4 varchar(200)

--populate variables
SELECT
@drive_letter = 'i' ,
@include_folder1 = 'Build' ,
@include_folder2 = 'SQLBACK' ,
@include_folder3 = 'wibble' ,
@include_folder4 = 'wobble'
SELECT
@s = 'DIR ' + @drive_letter + ': /AD /S /B'

--create temp table to hold folder list
CREAT TABLE
#Folder_Name
(Folder_Name varchar(2000))

--get folder info
INSERT
#Folder_Name (Folder_Name)
EXEC
master.dbo.xp_cmdshell @s

--output make directory commands
SELECT DISTINCT
'MD "' + LTRIM(RTRIM(Folder_Name)) + '"' AS 'Create_Folder'
FROM
#Folder_Name
WHERE
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder1)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder1)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder1))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder2)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder2)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder2))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder3)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder3)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder3))) + 4) )
)
OR
(
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder4)) ) = UPPER ( LTRIM(RTRIM(Folder_Name)) )
OR
UPPER (@drive_letter + ':\' + LTRIM(RTRIM(@include_folder4)) + '\' ) = UPPER ( SUBSTRING(Folder_Name,1,LEN(LTRIM(RTRIM(@include_folder4))) + 4) )
)
ORDER BY
'Create_Folder'

--cleanup
DROP TABLE #Folder_Name

/*
END
EXEC #DBA_Create_Folder_Create_Batch_File
DROP PROC #DBA_Create_Folder_Create_Batch_File
*/

TSQL: SQLServerAgent job history

Cheap and cheerful summary of most recent run of jobs. Note the comments before --create. Those are required for me to post the code, if I start with the c-word I get a page error.

--create table #tbl
(
instance_id int ,
job_id uniqueidentifier ,
job_name sysname ,
step_id int ,
step_name sysname ,
sql_message_id int ,
sql_severity int ,
message nvarchar(1024) ,
run_status int ,
run_date char(8) ,
run_time char(23) ,
run_duration int ,
operator_emailed nvarchar(20) ,
operator_netsent nvarchar(20) ,
operator_paged nvarchar(20) ,
retries_attempted int ,
server nvarchar(30)
)
insert #tbl
exec msdb.dbo.sp_help_jobhistory @mode = 'FULL'
GO

--format our date and time
--add leading zeroes
update #tbl
set run_time =
CASE len(run_time)
WHEN 6 THEN run_time
WHEN 5 THEN '0' + run_time
WHEN 4 THEN '00' + run_time
WHEN 3 THEN '000' + run_time
WHEN 2 THEN '0000' + run_time
WHEN 1 THEN '00000' + run_time
else 'wibble'
end
--add colons
update #tbl
set run_time = run_date + ' ' + STUFF(STUFF(ltrim(rtrim(run_time)), 3, 0, ':'), 6, 0, ':') + ':000'

select
max(instance_id) as instance_id
into
#tbl2
from
#tbl
where
step_id = 0
group by
job_name

select #tbl.* from #tbl
inner join #tbl2 on #tbl.instance_id = #tbl2.instance_id

drop table #tbl
drop table #tbl2