script how to get last backup to restore from AZURE

Question

I want to restore the last backup from mu AZURE server. I have one script but just I achieve to take the backup with current date but not the last one. Do you have any idea how to get it?

`USE [master] GO DECLARE @name VARCHAR(50) -- database name
DECLARE @URL VARCHAR(256) -- URL for backup
DECLARE @fileDate VARCHAR(20) -- used for file date

-- specify date format to take the last backup. SELECT @fileDate = REPLACE (CONVERT (VARCHAR (10), GETDATE (), 120), '-','_')

--Create a credential to connect to the windows azure storage service IF NOT EXISTS (SELECT * FROM sys.credentials WHERE credential_identity = 'aaaaaaaaaa') BEGIN CREATE CREDENTIAL Pyramidsqlcredential WITH IDENTITY = 'bbbbbbbbbb' ,SECRET = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' END GO

--Code to be able to restore specific databases DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name IN ('STG','DWH','AUXDB','TESTPORTAL') -- only restore these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
BEGIN

--Creation of PRE databases IF OBJECT_ID(N'PRE_@name', N'U') IS NOT NULL DROP DATABASE @name; GO CREATE DATABASE [@name] CONTAINMENT = NONE ON PRIMARY ( NAME = N'PRE_@name', FILENAME = N'G:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\PRE_@name.mdf' , SIZE = 609344KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), FILEGROUP [INMEMORY] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT LOG ON ( NAME = N'PRE_@name', FILENAME = N'H:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\PRE_@name_log.ldf' , SIZE = 833024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

set @URL = 'https://xxxxxxxxxx.blob.core.windows.net/'

--RESTORE the database to the windows azure storage service - blob using URL RESTORE DATABASE PRE_AUXDB FROM URL = @URL+@name+'/'+@name+''+@fileDate+'*.bak' --FROM URL = 'https://xxxxxxxxxxxxxxxxxxxxxxx.blob.core.windows.net/AUXDB/AUXDB'+REPLACE (CONVERT (VARCHAR (10), GETDATE (), 120), '-','_')+'*.bak' WITH CREDENTIAL = 'aaaaaaaaaaaaaaa' ,MOVE 'PRE_AUXDB_Data' to 'G:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\PRE_@name_Data.mdf' ,STATS = 10

END

CLOSE db_cursor
DEALLOCATE db_cursor`

Answer 1

I think I found a solution. the script is not tested yet. Perhaps it helps to someone.

                    USE [master]
                    GO
                    DECLARE @name VARCHAR(50) -- database name  
                    DECLARE @URL VARCHAR(256) -- URL for backup  

                    --Create a credential to connect to the windows azure storage service
                    IF NOT EXISTS (SELECT * FROM sys.credentials WHERE credential_identity = 'credentialname')
                    BEGIN
                    CREATE CREDENTIAL credentialname WITH IDENTITY = 'introduceyouridentity'
                    ,SECRET = 'accountkey'
                    END
                    GO

                    --Code to be able to restore specific databases
                    DECLARE @db_cursor CURSOR 
                        SET @db_cursor = CURSOR FOR
                    SELECT name 
                    FROM master.dbo.sysdatabases 
                    WHERE name IN ('STG','DWH','AUXDB','TESTPORTAL')  -- only restore these databases

                    OPEN @db_cursor   
                    FETCH NEXT FROM @db_cursor INTO @name  

                    WHILE @@FETCH_STATUS = 0   
                    BEGIN 
                    declare @backupfile as Varchar(255) --latest bck in AZURE
                    declare @DBname VARCHAR(50) = 'PRE_'+@name  --name new database ex: PRE_AUXDB

                    --Check latest backup file in Azure per type
                    /* type char(1)
                    Backup type. Can be:
                    D = Database
                    I = Differential database
                    L = Log
                    F = File or filegroup
                    G =Differential file
                    P = Partial
                    Q = Differential partial
                    Can be NULL. */
                    SELECT TOP 1 @backupfile=mf.physical_device_name from msdb..backupset bk      
                    join msdb..backupmediafamily mf on bk.media_set_id = mf.media_set_id   
                    where database_name=@name and bk.type='D' order by  
                    backup_set_id desc
                    --Creation of PRE databases
                    IF  OBJECT_ID(@DBname, N'U') IS NOT NULL 
                    DROP DATABASE @DBname;
                    GO
                    CREATE DATABASE [@DBname]
                     CONTAINMENT = NONE
                     ON  PRIMARY 
                    ( NAME = @DBname, FILENAME = N'\\servername\G$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\'+@DBname+'.mdf' , SIZE = 609344KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
                        FILEGROUP [INMEMORY] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
                     LOG ON 
                    ( NAME = @DBname, FILENAME = N'\\servername\H$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data'+@DBname_log+'.ldf' , SIZE = 833024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
                    GO
                    set @URL = 'https://azureservername.blob.core.windows.net/'

                            /*NOT USE
                            --Backup the database to the windows azure storage service - blob using URL
                            BACKUP DATABASES 
                            TO URL = @backupfile
                            WITH CREDENTIAL = 'credentialname' 
                            ,COMPRESSION --Compress the backup
                            ,STATS = 10 --This reports the percentage complete as of the threshold for reporting the next interval
                            GO
                            */

                    --RESTORE the database to the windows azure storage service - blob using URL
                    RESTORE DATABASE PRE_AUXDB 
                    FROM URL = @backupfile
                    WITH CREDENTIAL = 'credentialname'
                    ,MOVE @DBname+'_Data' to '\\servername\G$\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\'+@DBname+'_Data.mdf'
                    ,STATS = 10

                    END   

                    CLOSE db_cursor   
                    DEALLOCATE db_cursor