How do I backup a SQL Server database with URL?
As I mentioned in my earlier blog, backup to URL is one of the common methods used in SQL Server performs a backup to Azure Blob Storage. In this blog, I am going to share a script to generate the create credential and backup command using Shared Access Signature also called as SAS token. Show If you don’t know already, Backup to URL also has two methods to connect to the storage account
In my earlier blog, I have shared script to use the first method. SQL SERVER – Msg 3292: A Failure Occurred While Attempting to Execute Backup or Restore With a URL Device Specified In this blog, I would show the second method – Backup using Shared Access Signature. WORKAROUND/SOLUTIONIn the script, we need to provide below parameters.
We need to click on “Shared access signature” as shown below. Then we need to click on “Generate SAS and connection string” button. Once done, scroll down and we should see something like below. The value should be assigned to variable @SASKey ---- Backup To URL (using SAS Token) : --- =================================== --- DECLARE @Date AS NVARCHAR(25) ,@TSQL AS NVARCHAR(MAX) ,@ContainerName AS NVARCHAR(MAX) ,@StorageAccountName AS VARCHAR(MAX) ,@SASKey AS VARCHAR(MAX) ,@DatabaseName AS SYSNAME; SELECT @Date = REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, GETDATE(), 100), ' ', '_'), ' ', '_'), '-', '_'), ':', '_'); SELECT @StorageAccountName = ''; --- Find this from Azure Portal SELECT @ContainerName = ''; --- Find this from Azure Portal SELECT @SASKey = ''; --- Find this from Azure Portal SELECT @DatabaseName = 'master'; IF NOT EXISTS ( SELECT * FROM sys.credentials WHERE name = '''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '''' ) BEGIN SELECT @TSQL = 'CREATE CREDENTIAL [https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '] WITH IDENTITY = ''SHARED ACCESS SIGNATURE'', SECRET = ''' + REPLACE(@SASKey, '?sv=', 'sv=') + ''';' --SELECT @TSQL EXEC (@TSQL) END SELECT @TSQL = 'BACKUP DATABASE [' + @DatabaseName + '] TO ' SELECT @TSQL += 'URL = N''https://' + @StorageAccountName + '.blob.core.windows.net/' + @ContainerName + '/' + @DatabaseName + '_' + @Date + '.bak''' SELECT @TSQL += ' WITH COMPRESSION, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536, CHECKSUM, FORMAT, STATS = 1;' --SELECT @TSQL EXEC (@TSQL) Once the script was executed, we could see credential in SSMS and backup in Azure. Hope this would help you in creating the script in an easier way. Reference: Pinal Dave (http://blog.SQLAuthority.com) Related PostsHow do I backup a SQL Server URL?SQL Server backup can use either blob type depending upon the Transact-SQL syntax used. Blobs are addressable using the following URL format: https://
What is backup to URL?Backup to URL was introduced in SQL Server 2012 SP1 CU2 and allows backups to be taken directly to an Azure Storage Account.
How do I backup my entire SQL Server database?To take a backup of your database, follow these steps:. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server instance.. Expand the Databases node in Object Explorer.. Right-click the database, hover over Tasks, and select Back up..... Under Destination, confirm that the path for your backup is correct.. How do I backup a SQL Server database using a script?SQL BACKUP DATABASE for SQL Server. BACKUP DATABASE databasename. TO DISK = 'filepath';. BACKUP DATABASE databasename. TO DISK = 'filepath' WITH DIFFERENTIAL;. Example. BACKUP DATABASE testDB. TO DISK = 'D:\backups\testDB.bak';. Example. BACKUP DATABASE testDB. TO DISK = 'D:\backups\testDB.bak' WITH DIFFERENTIAL;. |