SQL Smart Admin Backups

gistfile1.sql

-- Powershell Backup-SqlDatabase 
Backup-SqlDatabase -ServerInstance $env:computername -Database DB -BackupAction Database -BackupFile "C:\bak\db.bak"

-- http://msdn.microsoft.com/en-us/library/dn449496.aspx#relatedtasks
-- Enable Smart Backup to Azure on a specifc DB with no Encryption 
-- System stored procedure for enabling and configuring SQL Server Managed Backup to Windows Azure for a database.

begin tran
GO
EXEC smart_admin.sp_set_db_backup
	@database_name = N'database',
	@retention_days = 30,
	@credential_name = 'Credential',
	@encryption_algorithm = 'NO_ENCRYPTION',
	@storage_url = 'url'
	@enable_backup = 1
GO
rollback

EXEC smart_admin.sp_set_db_backup  
     [@database_name = ] 'database name'
    ,[@enable_backup = ] { 0 | 1}
    ,[@storage_url = ] 'storage url'
    ,[@retention_days = ] 'retention_period_in_days'
    ,[@credential_name = ] 'sql_credential_name'
    ,[@encryption_algorithm] 'name of the encryption algorithm'
    ,[@encryptor_type] {'CERTIFICATE' | 'ASYMMETRIC_KEY'}
    ,[@encryptor_name] 'name of the certificate or asymmetric key'

-- Query configuration of Smart Backup for DB
SELECT * FROM smart_admin.fn_backup_db_config('database')

-- Query configuration for SQL instance (all databases)
SELECT * FROM smart_admin.fn_backup_instance_config()

-- List Backups
SELECT * FROM smart_admin.fn_available_backups('database')

-- List SQL Errors for Last Week associated with Smart Admin 
-- backup process

DECLARE @startofweek datetime
DECLARE @endofweek datetime
SET @startofweek = DATEADD(Day, 1-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP) 
SET @endofweek = DATEADD(Day, 7-DATEPART(WEEKDAY, CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
SELECT *
FROM smart_admin.fn_get_health_status(@startofweek, @endofweek)


-- Force Backup for database full or log

Use MSDB
Go
EXEC smart_admin.sp_backup_on_demand
 @database_name = 'TestDB'
,@type = 'Database'


-- Backing up to a new Database from Log files

-- SQL Available Memory

SELECT name, value, value_in_use, [description] 
FROM sys.configurations
WHERE name like '%server memory%'
ORDER BY name OPTION (RECOMPILE);

-- SQL Buffer Pools
-- http://azure.microsoft.com/blog/2014/09/22/new-d-series-virtual-machine-sizes/
-- http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/25/buffer-pool-extension-to-ssds-in-sql-server-2014.aspx
-- Recommended 1:16 ratio of max_server_memory to buffer pool memory size 1:16 = max_server_memory/bufferpoolmemory

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
SIZE = <size> [ KB | MB | GB ]
FILENAME = 'D:\SSDCACHE\EXAMPLE.BPE'

-- Notes on SSD D: Drive on Azure D Machines
-- Drive is not persistent, so the tempdb and bpe extension cache can get deleted.  Recommendations is to start
-- SQL server as a Powershell script that runs on startup.  The script verifies that the D:tempsql drive has been
-- created
-- Documentation: http://blogs.technet.com/b/dataplatforminsider/archive/2014/09/25/using-ssds-in-azure-vms-to-store-sql-server-tempdb-and-buffer-pool-extensions.aspx

-- Move Temp DB to SSD
USE MASTER
GO
ALTER DATABASE tempdb MODIFY FILE (NAME= tempdev, FILENAME= 'D:\SQLTEMP\tempdb.mdf') 
GO
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'D:\SQLTEMP\templog.ldf') 
GO

-- Alter BPE (4 -6 times SQL server memory)
-- KB, MB, GB
-- This example is 6 times a 3.5 GB server

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
( FILENAME = 'D:\SQLTEMP\ExtensionFile.BPE' , SIZE = 21504 MB )


-- Set MSSQLSERVER start to manual and start with this powershell script as a task
$SQLService="SQL Server (MSSQLSERVER)"
$SQLAgentService="SQL Server Agent (MSSQLSERVER)"
$tempfolder="D:\SQLTEMP"
if (!(test-path -path $tempfolder)) {
    New-Item -ItemType directory -Path $tempfolder
}
Start-Service $SQLService
Start-Service $SQLAgentService

-- Troubleshooting why Log backups are not being created
-- http://msdn.microsoft.com/en-us/library/dn449495.aspx

-- Recovery Model - for managed Azure backups needs to be full

Use MSDB
Go

SELECT name, recovery_model_desc
   FROM sys.databases
      WHERE name = 'kno2.ds1' ;
GO

-- Restore Existing Backup with new MDF and Log files
RESTORE DATABASE newdbname
FROM DISK = 'C:\temp\bak\backup.bak'
WITH 
   MOVE 'OldDbName' TO 'c:\Program Files (x86)\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\newdbname.mdf',
   MOVE 'OldDbName_log' TO 'c:\Program Files (x86)\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\newdbname.mdf',
REPLACE

-- Rename Database
USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

-- Query for Open Connections to a Database
SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName,
        hostname as host
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame, hostname