sql backup script

<#region SYNOPSYS
0. CLEANUP
1. BACKUP TG DB on HS1TGDATA.crm.hosted SQL cluster
2. TRANSFER TG DB BAK to HS1-SQLSTDCLIN1.crm.hosted SQL cluster
3. RESTORE TG DB BAK on HS1-SQLSTDCLIN1.crm.hosted SQL cluster
4. CUSTOMIZE RESTORED TG DATABASE
#>

#region 0. CLEANUP
Remove-Variable * -ErrorAction SilentlyContinue
#endregion
#region 1. BACKUP TG DB on HS1TGDATA.crm.hosted SQL cluster
$TGdatabase = Read-Host -Prompt "Please enter the name of the TG database to be backed up."
$bkpfolder = "\\TG-SQLENT01.crm.hosted\J$\TempBackups\"
#Celanup earlier works
Get-ChildItem -Path $bkpfolder -Recurse | Where-Object { $_.Name -like "*$TGdatabase*" } | Remove-Item
#
$SOURCE_SQLServer = "hs1tgdata.crm.hosted" #use Server\Instance for named SQL instances! 
$SQLDBName = "master"
#T
$time = Get-Date
Write-Host -fore Cyan "Backing up database: $TGdatabase - $time "
## Command to run
$SqlQuery = "
DECLARE @myDate VARCHAR(100)
DECLARE @dbName VARCHAR(50)
DECLARE @fileName VARCHAR(MAX)
DECLARE @folder VARCHAR(MAX)
SELECT @folder = '$bkpfolder'
SELECT @myDate = REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),' ','_'),':','');
SELECT @dbName = '$TGdatabase'
SELECT @fileName = @folder + @myDate + '_' + @dbName + '_full.bak'
BACKUP DATABASE @dbName
TO��DISK = @fileName
WITH COPY_ONLY, NOFORMAT, NOINIT,�
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,�
STATS = 10;
"
<# 
     $SqlQuery ="
    SELECT name 
    FROM sys.databases d
    WHERE d.database_id > 4
                #>
## Get connected 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SOURCE_SQLServer; Database = $SQLDBName; Integrated Security = True"
## Run the command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
## end connection
$SqlConnection.close()
Clear-Host
$DataSet.Tables[0]
#T
$time = Get-Date
Write-Host -fore Cyan "DB backup complete - $time "
#endregion
#region 2. TRANSFER TG DB BAK to HS1-SQLSTDCLIN1.crm.hosted SQL cluster
#T
$time = Get-Date
Write-Host -fore Cyan "Starting copy $TGdatabase to HS1-SQLSTDCLIN1 SQL cluster - $time "
#
$SourceFile = Get-ChildItem -Path $bkpfolder | Where-Object { $_.Name -like "*$TGdatabase*" }
# Test path & create copy destination
$Path = "\\HS1-SQLSTDCLIN1\b`$\TempBackups"
$TestPath = Test-Path $Path
if ($TestPath -notlike "false") {
        $destination = $Path + '\' + $SourceFile.Name
        #Copy the data over to the live server
        Start-BitsTransfer -Source $SourceFile.FullName -Destination $destination
        #T
        $time = Get-Date
        Write-Host -ForegroundColor Cyan "Finished copying database - $time " 
}
else
{ Write-Host -ForegroundColor Red "Destination folder unreachable - transfer terminated" }
#endregion
#region 3. RESTORE TG DB BAK on HS1-SQLSTDCLIN1.crm.hosted SQL cluster
$time = Get-Date
Write-Host -fore Cyan "Restoring  $TGdatabase - $time"
#
$SQLServer = "HS1-SQLSTDCLIN1.crm.hosted" #use Server\Instance for named SQL instances! 
$SQLDBName = "master"
## Command to run
$loc = "B:\TempBackups\" + $SourceFile.Name
$dat = "E:\MSSQL\DATA\" + $TGdatabase + ".mdf"
$log = "L:\MSSQL\LOG\" + $TGdatabase + ".ldf"
$SqlQuery = "Use Master
    DECLARE @DBNAME nvarchar(200)
    DECLARE @MDF_File nvarchar(200)
    DECLARE @LDF_File nvarchar(200)
    DECLARE @BACKUP_SOURCE nvarchar(200)
                --This will be the database to be restored
    SET @DBNAME = N'$TGdatabase'
                --Backup file path
    SET @BACKUP_SOURCE = N'$loc'
                --Corrected locations for physical files
    SET @MDF_File = N'$dat'
    SET @LDF_File = N'$log'
                --The actual restore process # 
                --IMPORTANT: if th restore fails, verify, if the DB names are correct (TimeGate_dat & TimeGate_log). If neccesery, ammend.
    Restore database @DBNAME
    FROM DISK = @BACKUP_SOURCE
			            WITH  REPLACE,
			            MOVE 'TimeGate_dat' TO @MDF_File, 
			            MOVE 'TimeGate_log' To @LDF_File"

## Get connected 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
## Run the command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
## end connection
$SqlConnection.close()
Clear-Host
$DataSet.Tables[0]
#
$time = Get-Date
Write-Host -fore Cyan "Restoring complete. - $time"
#endregion
#region 4. CUSTOMIZE RESTORED TG DATABASE
$time = Get-Date
Write-Host -fore Cyan "Start customization of $TGdatabase - $time"
#
$SQLServer = "HS1-SQLSTDCLIN1.crm.hosted" #use Server\Instance for named SQL instances! 
$SQLDBName = "master"
## Command to run

$SqlQuery = " USE $SQLDBName 
                
                ALTER DATABASE [$TGdatabase] SET RECOVERY SIMPLE WITH NO_WAIT;
                -- Drop TG user
                USE [$TGdatabase]
                
                DROP USER [timegate];
                -- Shrink log file
                USE [$TGdatabase]
                
                DBCC SHRINKFILE (N'TimeGate_log' , 0, TRUNCATEONLY);
                --Add TG to the maser
                USE [$TGdatabase]
                
                CREATE USER [TimeGate] FOR LOGIN [TimeGate];
                USE [$TGdatabase]
                
                ALTER ROLE [db_owner] ADD MEMBER [TimeGate]
                "

## Get connected 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
## Run the command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
## end connection
$SqlConnection.close()
Clear-Host
$DataSet.Tables[0]
#
$time = Get-Date
Write-Host -fore Cyan "Customization complete - $time"
#endregion