PCfromDCSnippets

Import-Module SQLPS -DisableNameChecking -EA 0

$dbName = "deleteme"             # Database Name
$mdfNewLocation = "G:\Data"      # Where the .mdf file will be moved to
$ldfNewLocation = "H:\Logs"      # Where the .ldf file will be moved to
$sqlSA = "sqlSA"                 # The SQL SA Account username

$domain = $env:USERDOMAIN
$sqlSA = $domain + "\" + $sqlSA  # Converts the SA account from username to Account Name (domain\username)
$location = Get-Location         # Gets current location to return at the end

PCfromDCSnippets

#region Get Database Info
	$query1 = "SELECT name, physical_name AS CurrentLocation, state_desc
		FROM sys.master_files
		WHERE database_id = DB_ID(N'" + $dbName + "');"
	Write-Verbose "Getting [$dbName] information..." -Verbose
	$items = Invoke-Sqlcmd -Query $query1
	Write-Output $items
#endregion

PCfromDCSnippets

#region Take DB Offline
	# Set DB OFFLINE (Rollback is set to IMMEDIATE)
	$query2 = "ALTER DATABASE [" + $dbName + "] SET OFFLINE WITH ROLLBACK IMMEDIATE;"
        Write-Verbose "Taking [$dbName] Offline..." -Verbose
	Invoke-Sqlcmd -Query $query2
	# Get DB Status
	function getDBStatus ($dbName) {
		$query3 = "SELECT DATABASEPROPERTYEX('" + $dbName + "', 'Status') AS dbStatus"
		$dbStatus = Invoke-Sqlcmd -Query $query3
		return $dbStatus

PCfromDCSnippets

#region Copy Files, Set ACLs, and Alter Database
	foreach ($item in $items) {
        function copyItem ($newLocation, $file, $currentItem) {                 # copyItem Function to copy file
        	$destination = $newLocation + "\" + $file                       # Set destination of file
                Write-Verbose "Moving $file to $destination..." -Verbose
		Copy-Item -Path $currentItem -Destination $destination          # Copy the file to the destination
            return $destination                                                 # Returns the new file location
        }
		$currentItem = $item.CurrentLocation                            # Gets the location of either the .mdf or .ldf file
		$itemExtension = [System.IO.Path]::GetExtension($currentItem)   # Grabs file extension

PCfromDCSnippets

#region Bring DBs Online
	$query5 = "ALTER DATABASE [" + $dbName + "] SET ONLINE;"
        Write-Output "Bring $dbName ONLINE..."
	Invoke-Sqlcmd -Query $query5
#endregion

PCfromDCSnippets

#region wait for 10 then Delete Old Files
	# wait for 10
	for ($i = 10; $i -gt 0; $i--) { 
		Write-Host("Deleting old files in $i seconds") 
		Start-Sleep -Seconds 1  
	} 
	# delete old files
	foreach ($item in $items) {
		Remove-Item -Path $item.CurrentLocation -Force
	}             

PCfromDCSnippets

$bkdir = "\\SQL2012B\Shared\Temp" # Set Backup Path! 
# SSMS needed to be installed to load the SQL Assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | out-null
$s = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instance
# Grabs ALL of the databases
$dbs = $s.Databases
foreach ($db in $dbs) 
  {
     if(($db.Name -ne "tempdb") -and ($db.Name -ne "master") -and ($db.Name -ne "model") -and ($db.Name -ne "msdb")) 

PCfromDCSnippets

$restoreDir = "J:\Backups"
# Get files in backup directory
$files = get-childitem $restoreDir -recurse
foreach ($file in $files)
  {
     $query = "RESTORE DATABASE [" + $file.basename + "] 
               FROM DISK = N'" + $restoreDir + $file + "' WITH  FILE = 1, 
               MOVE N'" + $file.basename + "' TO N'" + $dataLocation + $file.basename + ".mdf', 
               MOVE N'" + $file.basename + "_log' TO N'" + $logLocation + $file.basename + "_log.LDF', 
               NOUNLOAD,  

PCfromDCSnippets

javascript:!function(e){function a(e,a){var n=/.+\//,o=e.href&&e.href.replace(n,"")||"",r="https://www.yammer.com/api/v1/uploaded_files/"+o+"/download";return r}function n(e){var a=document.createElement("a");a.download="filename",a.target="_blank",a.href=e,document.body.appendChild(a),a.click(),document.body.removeChild(a),delete a}var o=Array.from(e.querySelectorAll(".page-content .yj-tabular-data-name"));o.map(a).forEach(n)}(document);

PCfromDCSnippets

#region Create Resource Group
    $rg = Get-AzureRmResourceGroup -Name $resourceGroup -Location $location -ErrorAction SilentlyContinue
    if ($rg) {
        Write-Host "$resourceGroup resource group already exists..."
    }
    else {
        Write-Host "Creating $resourceGroup Resource Group..."
        $rg = New-AzureRmResourceGroup -Name $resourceGroup -Location $location -Force
    }
#endregion