Backup Strategy in Azure SQL?
Everybody says that the backup process in Azure SQL is very easy. Is that true? In this new article, we will show how to do it.
2016-08-08
1,386 reads
if object_id('DBA_Restore_TransactionLog','P') is not null drop procedure DBA_Restore_TransactionLog go create procedure DBA_Restore_TransactionLog ( @database varchar(200), -- Database Name @from_path varchar(1000) -- Path from where the transaction files are. ) as begin set nocount on; Declare @Script nvarchar(1000), @count int, @filename varchar(500), @search_frompath varchar(1000) -- Temp Table to get the results into the temp table. if object_id('tempdb..#Temp_CheckExistenceFolder', 'U') is not null drop table #Temp_CheckExistenceFolder create table #Temp_CheckExistenceFolder ( id int identity(1,1), Output_Results varchar(2000) ) --Temp table to store the execution script for the transaction logs applied. if object_id('Temp_ExecuteFileList','U') is not null drop table Temp_ExecuteFileList create table Temp_ExecuteFileList ( Exec_Script nvarchar(1500) ) -- Temp table to store the File List with date and time got from the dir command. The table helps in to store the transaction log -- file details to be applied on the database. if object_id('Temp_FileList', 'U') is not null drop table Temp_FileList create table Temp_FileList ( Id int identity(1,1), Dates varchar(20), Times varchar(20), Filenames varchar(500) ) --The DIR command to take the file list for the transaction file. select @script = 'exec master..xp_cmdshell ''dir '+@from_path+'/T ''' --The output of the DIR commad is inserted into the table. insert into #Temp_CheckExistenceFolder ( Output_Results ) exec (@script) -- The variable is used to delete a line from the table #Temp_CheckExistenceFolder select @search_frompath = '%'+@from_path +'%' /************ Delte statements from the table #Temp_CheckExistenceFolder unwanted data generated from the DIR command. *********************/ -- Delete any rows which had values as Null delete #Temp_CheckExistenceFolder where Output_Results is null -- Delete any rows which has values as %volume%' delete #Temp_CheckExistenceFolder where Output_Results like '%Volume%' -- Delete any rows which has values as <DIR> delete #Temp_CheckExistenceFolder where Output_Results like '%<Dir>%' -- Delete any rows which provides count of files persent in the path delete #Temp_CheckExistenceFolder where Output_Results like '%file(s)%' -- Delete any rows which provides count of folders persent in the path delete #Temp_CheckExistenceFolder where Output_Results like '%Dir(s)%' -- Delete any rows which provides from what path result is from. delete #Temp_CheckExistenceFolder where Output_Results like @search_frompath -- Get the total Files list into the FileList table with columns with Date, Time and Filename. insert into Temp_FileList ( Dates, Times, Filenames ) select left(output_results, 10) as 'Dates' , -- Get the date from the row. ltrim(rtrim(substring(output_results, len(left(output_results, 13)),6))), -- Get the Time from the row. ltrim(rtrim(right(output_results,NULLIF(charindex(' ', REVERSE(output_results)),0)))) as 'Filenames' -- get the filenames from the row. from #Temp_CheckExistenceFolder order by 2 desc -- Take the total File count for the transaction log. select @count = count(1) from Temp_FileList -- While loop to apply the transaction log. while(@count >=1) begin -- Take the file names from the table to be applied. select @filename = Filenames from Temp_FileList where Id = @count -- Generate the script for transaction log with from path and filename. select @script = ' RESTORE LOG '+ @database + ' FROM disk = '''+@from_path +'\'+@filename +'''' + ' WITH NORECOVERY;' -- The execution script is inserted into the table to debug if any problems. insert into Temp_ExecuteFileList ( Exec_Script ) select @script -- Execute the actual script to apply the transaction log. exec(@script) if(@@error<> 0) begin select @script end -- @Count variable gets values for the next run for the while loop. select @count = @count -1 end end go