--Script to generate backup and restore commands
DECLARE @URLPath AS VARCHAR(125) = 'storage account';<<--
DECLARE @ContainerName AS NVARCHAR(MAX) = 'nonprodcon';
DECLARE @Credential AS VARCHAR(MAX) = 'AzureBackups';
DECLARE @date CHAR(8) = (SELECT CONVERT(char(8), GETDATE(), 112));
DECLARE @RecoveryType as VARCHAR(15) = 'NORECOVERY';
DECLARE @BackupOption as VARCHAR(15) = 'DIFFERENTIAL,';
--DECLARE @BackupType as VARCHAR(15) = 'FULL';
DECLARE @BackupType as VARCHAR(15) = 'DIFF';
--DECLARE @BackupType as VARCHAR(15) = 'FINAL';
IF @BackupType = 'DIFF' SET @BackupType = 'DIFF_' + @date;
IF @BackupType = 'FINAL' SET @RecoveryType = 'RECOVERY';
--IF @BackupType = 'FULL' SET @BackupOption = 'COPY_ONLY,'; --copy only backups for testing
IF @BackupType = 'FULL' SET @BackupOption = '';
;WITH MoveCmdCTE ( DatabaseName, MoveCmd )
AS ( SELECT DISTINCT
DB_NAME(database_id) ,
STUFF((SELECT ' ' + CHAR(13)+', MOVE ''' + name + ''''
+ CASE Type
WHEN 0 THEN ' TO ''G:\SQLData'
ELSE ' TO ''H:\SQLLogs'
END
+ REVERSE(LEFT(REVERSE(physical_name),
CHARINDEX('',
REVERSE(physical_name),
1) - 1)) + ''''
FROM sys.master_files sm1
WHERE sm1.database_id = sm2.database_ID
FOR XML PATH('') ,
TYPE).value('.', 'varchar(max)'), 1, 1, '') AS MoveCmd
FROM sys.master_files sm2
WHERE database_id > 4
)
SELECT
'BACKUP DATABASE [' + name + '] TO URL = ''' + @URLPath + '/' + @ContainerName + '/' + name + '' + @BackupType + '.bak''' + ' WITH CREDENTIAL = ''' + @Credential + '''' + ', COMPRESSION, ' + @BackupOption + ' STATS=10;' as [Backup Commands],
'RESTORE DATABASE [' + name + '] FROM URL = ''' + @URLPath + '/' + @ContainerName + '/' + name + '' + @BackupType + '.bak''' + ' WITH CREDENTIAL = ''' + @Credential + '''' + ', STATS=10, ' +
case when @BackupType = 'FULL' THEN @RecoveryType + movecmdCTE.MoveCmd ELSE @RecoveryType END + ';' as [Restore Commands]
FROM sys.databases d
INNER JOIN MoveCMDCTE ON d.name = movecmdcte.databasename;