I run a small hosting provider and one of my issues is backing up SQL
databases. Everytime I create a database I have to create a script to
back them up. Here is a handy script that I found and modified a bit
that will backup all databases to a specified folder. It also keeps a
retention history for a user defineable number of days

here is a sample usage:
isp_FullBackup_UserDBs ‘c:\backups’, 10

this will backup all user databases (note it wil not backup the master database) and keep a history of 10 days.

—————————————————————————————————-
– OBJECT NAME : isp_FullBackup_UserDBs
–
– AUTHOR : Tara Duggan
– DATE : December 18, 2003
–
– INPUTS : @Path – location of the backups
@HistoryDays – Number of days back to keep

– OUTPUTS : None
– DEPENDENCIES : None
–
– DESCRIPTION : This stored procedure performs a full backup on all of the user databases
–
– EXAMPLES (optional) : EXEC isp_FullBackup_UserDBs @Path = ‘C:\MSSQL\Backup\’, @HistoryDays=14
–
– MODIFICATION HISTORY :
– Jan 3, 2005: David Woods – Added the @HistoryDays filter to allow a user specified time period of backups
– Feb 2, 2005: David Woods – Fixed a bug that would not backup databases with dashes (‘-’) in the name.

—————————————————————————————————-
–
—————————————————————————————————-
CREATE PROC isp_FullBackup_UserDBs
@Path VARCHAR(100),
@HistoryDays int –number of days to hold the database
AS

SET NOCOUNT ON

DECLARE @Now CHAR(14) — current date in the form of yyyymmddhhmmss
DECLARE @DBName SYSNAME — stores the database name that is currently being processed
DECLARE @SQL VARCHAR(7000) — stores the dynamically created xp_backup_database command
DECLARE @cmd SYSNAME — stores the dynamically created DOS command
DECLARE @Result INT — stores the result of the dir DOS command
DECLARE @RowCnt INT — stores @@ROWCOUNT

–make sure our path has a trailing slash otherwise we will have a mess of prefixed directories
if SUBSTRING(@path, len(@path), 1) != ‘\’
Set @Path = @Path + ‘\’

– Get the list of the databases to be backed up, does not include master, model, msdb, tempdb, Northwind, or pubs
SELECT name
INTO #WhichDatabase
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’, ‘model’, ‘msdb’, ‘pubs’, ‘tempdb’, ‘Northwind’)
ORDER BY name

– Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

– Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN

– Get the current date using style 120, remove all dashes, spaces, and colons
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), ‘-’, ”), ‘ ‘, ”), ‘:’, ”)

– Build the dir command that will check to see if the directory exists
SELECT @cmd = ‘dir ‘ + @Path + @DBName

– Run the dir command, put output of xp_cmdshell into @result
EXEC @result = master.dbo.xp_cmdshell @cmd

– If the directory does not exist, we must create it
IF @result <> 0
BEGIN

— Build the mkdir command
SELECT @cmd = ‘mkdir ‘ + @Path + @DBName

— Create the directory
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

END
– The directory exists, so let’s delete files older than two days
ELSE
BEGIN

— Stores the name of the file to be deleted
DECLARE @WhichFile VARCHAR(1000)

CREATE TABLE #DeleteOldFiles
(
DirInfo VARCHAR(7000)
)

— Build the command that will list out all of the files in a directory
SELECT @cmd = ‘dir ‘ + @Path + @DBName + ‘ /OD’

— Run the dir command and put the results into a temp table
INSERT INTO #DeleteOldFiles
EXEC master.dbo.xp_cmdshell @cmd

— Delete all rows from the temp table except the ones that correspond to the files to be deleted
DELETE
FROM #DeleteOldFiles
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 0 OR DirInfo LIKE
‘%<dir>%’ OR SUBSTRING(DirInfo, 1, 10) >= GETDATE() -
@HistoryDays

— Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) – PATINDEX(‘% %’, REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles

SET @RowCnt = @@ROWCOUNT

— Interate through the temp table until there are no more files to delete
WHILE @RowCnt <> 0
BEGIN

— Build the del command
SELECT @cmd = ‘del ‘ + @Path + + @DBName + ‘\’ + @WhichFile + ‘ /Q /F’

— Delete the file
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

— To move to the next file, the current file name needs to be deleted from the temp table
DELETE
FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, LEN(DirInfo) – PATINDEX(‘% %’, REVERSE(DirInfo)) + 2, LEN(DirInfo)) = @WhichFile

— Get the file name portion of the row that corresponds to the file to be deleted
SELECT TOP 1 @WhichFile = SUBSTRING(DirInfo, LEN(DirInfo) – PATINDEX(‘% %’, REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #DeleteOldFiles

SET @RowCnt = @@ROWCOUNT

END

DROP TABLE #DeleteOldFiles

END

– Build the xp_backup_database command dynamically
SELECT @SQL = ”
SELECT @SQL = @SQL + ‘BACKUP DATABASE [' + @DBName + ']‘ + CHAR(10)
SELECT @SQL = @SQL + ‘TO DISK = ”’ + @Path + @DBName + ‘\’ + @DBName + ‘_’ + @Now + ‘.BAK”’ + CHAR(10)
SELECT @SQL = @SQL + ‘WITH INIT ‘ + CHAR(10)
print @SQL
– Backup the database using xp_backup_database
EXEC (@SQL)

– To move onto the next database, the current database name needs to be deleted from the temp table
DELETE
FROM #WhichDatabase
WHERE name = @DBName

– Get the database to be backed up
SELECT TOP 1 @DBName = name
FROM #WhichDatabase

SET @RowCnt = @@ROWCOUNT

– Let the system rest for 5 seconds before starting on the next backup
WAITFOR DELAY ’00:00:05′

END

DROP TABLE #WhichDatabase

SET NOCOUNT OFF

RETURN

GO

Here is cool technique I saw here (http://www.sqlteam.com/item.asp?ItemID=2209)

Basically I knew of no way to do a dynamic order by / group by without creating dynamic sql i.e.

DECLARE @SortOrder varchar(30)DECLARE @sql varchar(8000)SET @SortOrder = 'CompanyName'

set @sql = 'SELECT CompanyName,        ContactName,        ContactTitle     FROM Customers      ORDER BY ' + @SortOrder

exec(@sql)

This
sucks as SQL can not validate the syntax, the engine can not cache the
execution plan, and this could open up a SQL injection attack (I am
pretty sure that sql will filter / escape the incomming string though).

Anyways here is the better way to do it:

DECLARE @SortOrder tinyintSET @SortOrder = 2

SELECT CompanyName,     ContactName,     ContactTitleFROM CustomersORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName            WHEN @SortOrder = 2 THEN ContactName            ELSE ContactTitle       END

Pretty freakin sweet! There is also an interesting item on that site about creating a dynamic where clause by using COALLESCE.

I ran into a strange situation today. I need to make a comma seperated list of values in tsql here is what I came up with

declare @cycles varchar(8000)
set @cycles = ”
SELECT @cycles = @cycles + CAST(producer_cycle_id AS varchar(100)) + ‘, ‘
from cycles

Note
that this function does not work very with nulls (hence why @cycles is
initialized to ”. If you have null data make sure you have an isnull()
in their to convert it!

I have a lot of stored procedures that I need to retreive, update,
delete multiple records based on ID (usually on a screen where a user
can select the customers to run a report on). I have seen a lot of
developers create dynamic sql (which I hate) or call the same stored
proc multiple times (which I hate). I created a little sql function
that takes a string of delmited data and turns it into a table:

CREATE fuNCTION fnStringToCol (@data varchar(8000), @delimiter varchar(100))RETURNS @output TABLE (data varchar(8000))ASBEGIN declare @position int declare @start int declare @end int declare @part varchar(8000)

 if (charindex(@delimiter, @data) = 0) --if only one value append a , so that it still gets parsed  set @data = @data + ','

 set @position = charindex(@delimiter, @data) insert into @output values (ltrim(rtrim(substring(@data, 0, @position))))

 while ((charindex(@delimiter, @data, @position)) <> 0) begin  set @start = charindex(@delimiter, @data, @position)  set @end = charindex(@delimiter, @data, @start+1)  if (@end = 0)   set @end = len(@data) - @start

  set @part =  ltrim(rtrim(substring(@data, @start+1, abs(@end - @start-1))))  insert  into @output  values (@part)  set @position = charindex(@delimiter, @data, @position) + 1 end returnEND

To use this is quite easy:

declare @producers
set @producers = ’1,7,35,26′

select *
from producer
where producer_id in (select * from dbo.fnStringToCol(@producers, ‘,’))

Here is a hany security script that will generate the grant execute /
select /delete permissions on all objects in your database if you have
the need to do so:

Declare @RevokeSQL varchar(1000)
Declare @GrantSQL varchar(1000)
declare @EveryoneRoleName varchar(30)

set @EveryoneRoleName=’Public’

set nocount on

select P.ID, U.Name as UserName, o.name as ObjectName,
case P.ProtectType
when 204 then ‘GRANT_W_GRANT’
when 205 then ‘GRANT’
when 206 then ‘REVOKE’
end as ProtectType,
case p.action
when 26 then ‘REFERENCES’
when 178 then ‘CREATE FUNCTION’
when 193 then ‘SELECT’
when 195 then ‘INSERT’
when 196 then ‘DELETE’
when 197 then ‘UPDATE’
when 198 then ‘CREATE TABLE’
when 203 then ‘CREATE DATABASE’
when 207 then ‘CREATE VIEW’
when 222 then ‘CREATE PROCEDURE’
when 224 then ‘EXECUTE’
when 228 then ‘BACKUP DATABASE’
when 233 then ‘CREATE DEFAULT’
when 235 then ‘BACKUP LOG’
when 236 then ‘CREATE RULE’
end as PermissionGranted
into #Temp
from sysprotects P
inner join sysusers U on P.UID = U.UID
inner join sysobjects O on P.ID=O.ID where
P.uid=0 and o.Type<>’S’ and
(O.Name not like ‘sync%’
and O.Name not like ‘sys%’
and O.Name not like ‘dt_%’)
– just added the funky syntax for o.name filters
order by UserName, ObjectName

DECLARE cur CURSOR
READ_ONLY
FOR Select UserName, PermissionGranted, ObjectName from #Temp

DECLARE @name varchar(40)
DECLARE @ProtectType varchar(100)
DECLARE @ObjectName varchar(100)
OPEN cur

FETCH NEXT FROM cur INTO @name, @ProtectType, @ObjectName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
–create has a slightly different syntax, so we have to branch
–here
if @ProtectType like ‘Create%’
begin
set @GrantSQL =’Grant ‘ + @ProtectType + ‘ to ‘ + @EveryoneRoleName
print @GrantSQL
– exec(@GrantSQL)
– set @RevokeSQL =’Revoke ‘ + @ProtectType + ‘ on [' + @ObjectName + '] from ‘ + @Name
– print @RevokeSQL
–exec(@RevokeSQL)
end
else
begin
set @GrantSQL =’Grant ‘ + @ProtectType + ‘ on [' + @ObjectName + '] to ‘ + @EveryoneRoleName
print @GrantSQL
– exec(@GrantSQL)

end
END
FETCH NEXT FROM cur INTO @name, @ProtectType, @ObjectName
END

CLOSE cur
DEALLOCATE cur

–clean up the working table
drop table #Temp

set nocount off

I have several stored procs that take a flag that tells them wether to
show expired records or not and usually looks something like this:

DECLARE @ShowExpired tinyint
SET @ShowExpired = 1

declare @sql varchar(1000)
set @sql = ‘SELECT * FROM TABLE’
IF @ShowExpired = 1
set @sql = @sql + ‘ WHERE eff_end_dt is NULL”
execute (@sql)

While
this works fine if I loose the advantages of stored procs and if I
rename a table and recreate the procedure (I periodically recreate all
procedures to test this) then I will not see any error.

Instead I came up with this:

DECLARE @ShowExpired tinyint
SET @ShowExpired = 1

select * from Table
where ((@ShowExpired=1) or (@ShowExpired=0 and eff_end_dt is null))

basically
all the is happening if @ShowExpired=1 then everything gets returned.
But if @ShowExpired=0 then only records that have a null end date are
returned.

I love the formatting of SQL from this article.
http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqlpro04/html/sp04l9.asp