TSQL - Automate DB restore

This script restores a database without having to know the logical file names. It also lets you place the MDF, LDF and NDF files wherever you want them.  I mainly use it to run a nightly restore on my local machine.  The line with "business logic" does a quick test to see if the database has been changed at all.  There's no need to restore a database if it hasn't been changed, so the "business logic" line is how I tell it to skip the restore step.

/* Automate restore w/o needing to know the logical file names. */

 -- BEGIN - MODIFY THIS CODE - create a blank db
if not exists(select * from master.sys.databases where [name]='DBNAME')
begin
    create database DBNAME
end
go
-- END - MODIFY THIS CODE - create a blank db

declare @strDatabase varchar(130)='DBNAME' -- MODIFY THIS LINE - db name
declare @strBackupFile varchar(500)='e:\docs\db-backups\dbname_backup_file.bak' -- MODIFY THIS LINE - source db backup file
declare @strRestoreMDFFilesTo varchar(500)='e:\docs\sqldata\default\' -- MODIFY THIS LINE - destination restore directory for main files
declare @strRestoreLDFFilesTo varchar(500)='c:\docs\sqldata\default\' -- MODIFY THIS LINE - destination restore directory for tlog files
declare @strRestoreNDFFilesTo varchar(500)='c:\docs\sqldata\default\' -- MODIFY THIS LINE - destination restore directory for non-main files

-- other variables used
declare @strSQL nvarchar(max)
declare @strOriginalPhysicalName varchar(150)
declare @strPhysicalName varchar(150)
declare @strLogicalName varchar(150)
declare @intReturn int

-- begin restoring
begin try
    drop table #tmpFilelist
end try
begin catch
end catch
create table #tmpFilelist (
    LogicalName varchar(64), PhysicalName varchar(130), [Type] varchar(1), FileGroupName varchar(64), Size decimal(20, 0)
    ,MaxSize decimal(25, 0), FileID bigint, CreateLSN decimal(25,0), DropLSN decimal(25,0), UniqueID uniqueidentifier
    ,ReadOnlyLSN decimal(25,0), ReadWriteLSN decimal(25,0), BackSizeInBytes decimal(25,0), SourceBlockSize int
    ,filegroupid int, loggroupguid uniqueidentifier, differentialbaseLSN decimal(25,0), differentialbaseGUID uniqueidentifier
    ,isreadonly bit, ispresent bit, TDEThumbpr decimal
)
if not exists(select * from DBNAME.sys.tables) -- MODIFY THIS LINE - business logic to see if we need to restore the database at all
begin
    print 'Restoring '+@strDatabase+' db ...'
    use master
    exec msdb.dbo.sp_delete_database_backuphistory @database_name = @strDatabase
    use [master]
    exec('alter database '+@strDatabase+' set single_user with rollback immediate')
    use [master]
    exec('drop database '+@strDatabase)
    insert into #tmpFilelist
        exec('restore filelistonly from disk = '''+@strBackupFile+'''')
    set @strSQL='restore database ['+@strDatabase+'] from disk='''+@strBackupFile+''' with '
    set @strSQL=@strSQL+ 'file=1 '
    set @strSQL=@strSQL+ ',nounload '
    set @strSQL=@strSQL+ ',replace '
    set @strSQL=@strSQL+ ',stats=10 '
    while exists(select * from #tmpFilelist)
    begin
        select top 1 @strOriginalPhysicalName=PhysicalName, @strLogicalName=LogicalName from #tmpFilelist
        set @strPhysicalName=@strOriginalPhysicalName
        set @strPhysicalName=reverse(@strPhysicalName)
        set @strPhysicalName=left(@strPhysicalName, charindex('\', @strPhysicalName)-1)
        set @strPhysicalName=reverse(@strPhysicalName)
        set @strPhysicalName=replace(@strPhysicalName, '.', '_'+@strDatabase+'.')
        if @strPhysicalName like '%.mdf'
            set @strPhysicalName=@strRestoreMDFFilesTo+@strPhysicalName
        else if @strPhysicalName like '%.ldf'
            set @strPhysicalName=@strRestoreLDFFilesTo+@strPhysicalName
        else
            set @strPhysicalName=@strRestoreNDFFilesTo+@strPhysicalName
        set @strSQL=@strSQL+ ',move '''+@strLogicalName+''' to '''+@strPhysicalName+''' '
        delete from #tmpFilelist where PhysicalName=@strOriginalPhysicalName
    end
    execute @intReturn=sp_executesql @strSQL
end

SSIS - FTP using Script Task

I was asked to write a SSIS package to download a file from a website (using FTP with a WWW... weird) at 2:00 am automatically. SSIS's FTP tool couldn't do it because it passes the username and password on the URL. (Rotten idea from a security POV, but that's what I was given.) It took me about two weeks, but I cobbled this code together. I had to perform several more major steps after getting the code to run, and I hope I remember them all here. To get it to run automatically:

1. Create your SSIS package (.dtsx)
2. Copy just the .dtsx file to the directory where you want to run it from
3. Create a new domain user with "Log on as a batch file" permissions
4. If your SSIS package connects to a database, add that user as a login on your SQL Server (Server / Security / Logins).
5. On the database server you want to run it on, create a batch file to execute your SSIS package. The "output.txt" file pipes the output to a separate file, which can be very handy.
   
   d:
   cd \directory
   dtexec /FILE "your-file-name.dtsx" /CHECKPOINTING OFF /REPORTING E > "your-file-name-lastrun-output.txt"
6. Launch Task Scheduler on the machine you want to run it on, give it the name of your batch file, give it a schedule, and tell it to execute as that new domain user.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net

Imports System.IO

Imports System.Collections.Generic

Imports System.Text

<system.addin.addin("scriptmain", )="" ,="" description:="" publisher:="" version:="1.0"> _

<system.clscompliantattribute(false)> _

Partial Public Class ScriptMain

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults

        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

    Public Sub Main()

        Try

            Dim restURL As New StringBuilder()

            Dim objRequest As FtpWebRequest

            Dim objResponse As FtpWebResponse

            Dim objStream As StreamReader

            Dim arySourceFiles(0) As String

            Dim aryDestinationFiles(0) As String

            Dim intFile As Int16

            ' - original file names from FTP site

            arySourceFiles(0) = "original_filename.txt"

            ' - new file names - rename arySourceFiles(n) to aryDestinationFiles(n)

            aryDestinationFiles(0) = "\\servername\d$\directory\new_filename.txt"

            ' - download each file

            For intFile = 0 To UBound(arySourceFiles)

                objRequest = DirectCast(FtpWebRequest.Create("ftp://nt_domainname%5Cusername:password@www.website.com/directory1/directory2/" & arySourceFiles(intFile)), FtpWebRequest)

                objResponse = DirectCast(objRequest.GetResponse(), FtpWebResponse)

                objStream = New StreamReader(objResponse.GetResponseStream())

                File.WriteAllText(aryDestinationFiles(intFile), objStream.ReadToEnd)

            Next

            Dts.TaskResult = ScriptResults.Success

        Catch webEx As WebException

            Dim [error] As New StringBuilder()

            'catch protocol errors

            If webEx.Status = WebExceptionStatus.ProtocolError Then

                [error].AppendFormat("Status code: ", DirectCast(webEx.Response, HttpWebResponse).StatusCode)

                [error].AppendFormat("Status description: ", DirectCast(webEx.Response, HttpWebResponse).StatusDescription)

                ' post the error message we got back.  This is the old error catch code that might work better with SSIS.

                Dts.Events.FireError(0, String.Empty, webEx.Message.ToString(), String.Empty, 0)

                Dts.TaskResult = ScriptResults.Failure

            End If

        End Try

    End Sub

End Class