Labels

How to Restore backup from Azure




Please find steps to restore from Azure. As backup is available in Azure server, You have to pass certain additional parameters in normal restore script
If you have any issues let me know

Step 1: Run below on Source server to find available backups for specific database
/**************************
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type, msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 10)
and msdb.dbo.backupset.database_name = 'dbname' and msdb.dbo.backupmediafamily.physical_device_name like 'ht%'
ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date desc
*******************************/


Step 2: In the output , backup type: “Database” is a full backup and backup type “Null” is differential


Restore only latest full and latest differential(as per timestamp required)

Step 3: Restore Database using below script

Don't forget to change
             Destination Database name
             Logical and Physical name for destination database data file and the log file (you can get is using “sp_helpdb  dbanme")

/************************************
RESTORE DATABASE [new db]
FROM URL = N'https://backupurl/full/_FULL_20180113_190407.bak'
WITH MOVE N'logicalname' TO N'backupparth\_Primary.mdf',
Logical and physical file name of destination database data file
MOVE N'logical_logfilename' TO N'backup path\_Primary.ldf',
Logical and physical file name of destination database log file
NORECOVERY, CREDENTIAL = N'AzureCredential', REPLACE;
****************************/

In the above command, we have used a few options
  1.        NORECOVERY: the database will remain in restoring mode and ready for the next file to be restored
  2.        CREDENTIAL = N'AzureCredential' : This is used to connect to Azure site
  3.        Replace: only require if you overwrite existing database, if not then remove that option
  4.        To get the logical and physical name for DB files to use below





Step 4: Once all file restore complete, run below command to make database in use
/*******************
RESTORE DATABASE [DBNAME] WITH RECOVERY;
********************/


No comments:
Write comments

Please do not enter spam links

Services

More Services