At a certain time, a backup database is formed. The file name contains the extension, time, date.

There is a procedure that takes the last formed backup.
I would like to know how, using the input parameter, I could indicate the time at which the backup was formed, and he took it.

  • Your question is a bit out of stackoverflow format. First, show the text of your procedure or a snippet of code. And explain what you tried to do and what you can’t do. - newman

1 answer 1

Here is my procedure.

USE testDB GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].get_file (@Dir varchar(600)) AS BEGIN DECLARE @Directory TABLE ([subdirectory] varchar(256), [depth] int, [file] int) DELETE FROM @Directory INSERT INTO @Directory EXECUTE master..xp_dirtree @Dir, 1, 1 DECLARE @top1datetime TABLE ([subdirectory] varchar(256),[extension] varchar(4),[time] varchar(6),[date] varchar(10)) INSERT INTO @top1datetime SELECT [subdirectory],[extension],[time],[date] FROM (SELECT [subdirectory], --*************** Get Expansion File *********************** CASE WHEN RIGHT([subdirectory],3) = 'bak' THEN RIGHT([subdirectory],3) ELSE CASE WHEN RIGHT([subdirectory],4) = 'diff' THEN RIGHT([subdirectory],4) END END AS [extension], --*************** get time from file title **************** CASE WHEN RIGHT([subdirectory],3) = 'bak' THEN SUBSTRING(RIGHT([subdirectory],29),12,6) ELSE CASE WHEN RIGHT([subdirectory],4) = 'diff' THEN SUBSTRING(RIGHT([subdirectory],30),12,6) END END AS [time], --*************** get date from filt title ***************** CASE WHEN RIGHT([subdirectory],3) = 'bak' THEN LEFT(RIGHT([subdirectory],29),10) ELSE CASE WHEN RIGHT([subdirectory],4) = 'diff' THEN LEFT(RIGHT([subdirectory],30),10) END END AS [date] FROM @Directory) AS C ORDER BY CAST (REPLACE ((CASE WHEN RIGHT([subdirectory],3) = 'bak' THEN LEFT(RIGHT([subdirectory],29),10) ELSE CASE WHEN RIGHT([subdirectory],4) = 'diff' THEN LEFT(RIGHT([subdirectory],30),10) END END),'_','') AS date) DESC --******************* Get last 'bak' file *********************** DECLARE @bak_f varchar(200) SET @bak_f=(SELECT TOP 1 [subdirectory] FROM @top1datetime WHERE [extension] = 'bak' ORDER BY CAST (REPLACE ((CASE WHEN RIGHT([subdirectory],3) = 'bak' THEN LEFT(RIGHT([subdirectory],29),10) ELSE CASE WHEN RIGHT([subdirectory],4) = 'diff' THEN LEFT(RIGHT([subdirectory],30),10) END END),'_','') AS date) DESC) DECLARE @bak_path varchar(600) SET @bak_path = @Dir+@bak_f --*************** Get last file 'diff' ************************** DECLARE @diff_f varchar(200) SET @diff_f=(SELECT TOP 1 [subdirectory] FROM @top1datetime WHERE [extension] = 'diff' ORDER BY CAST (REPLACE ((CASE WHEN RIGHT([subdirectory],3) = 'bak' THEN LEFT(RIGHT([subdirectory],29),10) ELSE CASE WHEN RIGHT([subdirectory],4) = 'diff' THEN LEFT(RIGHT([subdirectory],30),10) END END),'_','') AS date) DESC) DECLARE @diff_path varchar(600) SET @diff_path = @Dir+@diff_f IF OBJECT_ID('[testDB].[dbo].[tempFileBak]','U') IS NOT NULL DROP TABLE [testDB].[dbo].[tempFileBak] SELECT @bak_path AS bak_path , @bak_f AS bak_f , @diff_path AS diff_path , @diff_f AS diff_f , (SELECT CAST(LEFT([date],4)+'-'+SUBSTRING([date],6,2)+'-'+RIGHT([date],2)+'T'+LEFT([time],2)+':'+SUBSTRING([time],3,2)+':'+RIGHT([time],2) as datetime) FROM @top1datetime WHERE [subdirectory] = @bak_f) AS bak_t , (SELECT CAST(LEFT([date],4)+'-'+SUBSTRING([date],6,2)+'-'+RIGHT([date],2)+'T'+LEFT([time],2)+':'+SUBSTRING([time],3,2)+':'+RIGHT([time],2) as datetime) FROM @top1datetime WHERE [subdirectory] = @diff_f) AS diff_t INTO [testDB].[dbo].[tempFileBak] END

DeploymentTool_backup_2016_01_17_230003_0023014.diff DeploymentTool_backup_2016_01_17_000102_3844565.bak

Here is the name of the backup files