SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Generic SQL backup with Azure support

By Haden Kingsland (theflyingdba),

--############################################################################# -- YOU MUST BE A SYSADMIN IN ORDER TO SWITCH ON XP_CMDSHELL, SO YOU MUST ENSURE THAT THE -- AGENT JOB FOR ALL BACKUPS RUNS UNDER THE CONTEXT OF A SYSADMIN USER! --############################################################################# -- -- You will need to create a credential for your Azure account in order to perform -- backups to this location. This must relate to the Blob storage location in azure... -- -- http://msdn.microsoft.com/en-GB/library/jj919148(v=sql.110).aspx#Support -- http://azure.microsoft.com/en-us/documentation/articles/storage-manage-storage-account/ -- http://msdn.microsoft.com/en-us/library/jj919149.aspx ------------------------------------------------------------ -- EXAMPLE CALL TO SP VIA a SQL SERVER AGENT JOB STEP ------------------------------------------------------------ --Differential is type "D" and Daily --Full is type "F" and Weekly --Transaction Log is type "T" and Daily or Weekly --IsCloud -- it this going to be a Cloud (URL) backup or not? --Adhoc is any type plus a database name must be present --For a copy backup @copy must be a value of "1" --System is type "S" and Daily or Weekly --create a directory where ones doesn't exist --create a production and non-production backup --process SIMPLE mode db's or not --either ignore specified database passed in if set to 'Y' or only process those passed in if set to 'N' --@checksum tells the backup process whether to perform a checksum once the backup is complete so that we can verify its integrity. --the value of @checksum can be either 1 = Yes or 0 = No! --declare @RC int --declare @job_name varchar(128) --declare @BACKUP_LOCATION varchar(200) --declare @isCloud varchar(1) --declare @backup_type VARCHAR(1) --declare @dbname nvarchar(2000) --declare @copy varchar(1) --declare @freq varchar(10) --declare @production varchar(1) --declare @INCSIMPLE varchar(1) --declare @ignoredb varchar(1) --declare @checksum varchar(1) --declare @isSP varchar(1) --declare @recipient_list varchar(2000) -- select @job_name = ' Database Backups' -- select @backup_location = 'https://.blob.core.windows.net/sqlbak/' -- select @isCloud = 'Y' -- select @backup_type = 'F' -- 'F', 'S', 'D', 'T' -- select @dbname ='' -- a valid database name or spaces -- select @copy = 0 -- 1 or 0 -- copy only backup or not -- select @checksum = 1 -- 1 or 0 -- create a checksum for backup integrity validation -- select @freq = 'Daily' -- 'Weekly', 'Daily' -- select @production = 'Y' -- 'Y', 'N' -- only use 'N' for non production instances -- select @INCSIMPLE = 'Y' -- 'Y', 'N' -- include SIMPLE recovery model databases -- select @ignoredb = 'N' -- 'Y' or 'N' -- if "Y" then it will ignore the databases in the @dbname parameter -- select @isSP = 'N' -- 'Y' or 'N' -- set to Y if the instance is used for SharePoint. Implemented due to extra long SP database names! -- select @recipient_list = 'hkingsland@laingorourke.com' -- ;rfinney@laingorourke.com' -- EXECUTE @RC = [dbadmin].[dba].[generic_backup_all_databases_cloud] -- @backup_location, -- @isCloud, -- @job_name, -- @backup_type, -- @dbname, -- @copy, -- @freq, -- @production, -- @INCSIMPLE, -- @ignoredb, -- @checksum, -- @isSP, -- @recipient_list

Total article views: 321 | Views in the last 30 days: 2
 
Related Articles
FORUM

DataBase Backup for selected tables through SSIS 2005

DataBase Backup for selected tables

FORUM

How to declare parameters inside "select"?

select asset.asset_name asset_name (varchar(100))?

FORUM

Database backups best practice

Database backups best practice

FORUM

Database backup

Database backup

FORUM

Database Backup

Database Backup

Tags
azure    
backups    
cloud    
differential    
full recovery mode    
transaction log    
 
Contribute