file concatenation

%3CLINGO-SUB%20id%3D%22lingo-sub-2488727%22%20slang%3D%22en-US%22%3Efile%20concatenation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2488727%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20writing%20a%20proc%20to%20handle%20backup%20location%20for%20a%20restore%20script.%20can%20anyone%20suggest%20me%20how%20to%20generate%20complete%20path%20using%20both%20locations%20as%20below%20and%20final%20path%20name%20silty%20different%20as%20we%20need%20to%20ignore%20first%20part%20from%20location%202%20(%20i.e%20K%3A%5Cbackup%5C)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBackup%20location1%20%3A%20%5C%5Cbackupserver%5Csqlbackup%3C%2FP%3E%3CP%3EBackup%20location%202%26nbsp%3B%20%3A%26nbsp%3BK%3A%5CBackups%5Clocalserver%5CPRD_DW_ABCD%5CFULL%5Clocalserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20for%20final%20location%20by%20using%20both%20backup%20locations%20(concatenation%20and%20remove%20part%20of%20k%3A%5Cbackup%20from%20send%20backup%20location%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPath%20should%20be%20as%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%5C%5Cbackupserver%5Csqlbackup%5Clocalserver%5CPRD_DW_ABCD%5CFULL%5Clocalserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2488727%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQL%20Server%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2491493%22%20slang%3D%22en-US%22%3ERe%3A%20file%20concatenation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1088915%22%20target%3D%22_blank%22%3E%40nasdba%3C%2FA%3E%26nbsp%3B%2C%20do%20you%20mean%20a%20simple%20replace%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Edeclare%20%40base%20nvarchar(400)%20%3D%20N'%5C%5Cbackupserver%5Csqlbackup'%0Adeclare%20%40path%20nvarchar(400)%20%3D%20N'K%3A%5CBackups%5Clocalserver%5CPRD_DW_ABCD%5CFULL%5Clocalserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak'%0A%0Aselect%20replace(%40path%2C%20N'K%3A%5CBackups'%2C%20%40base)%3B%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi ,

 

I am writing a proc to handle backup location for a restore script. can anyone suggest me how to generate complete path using both locations as below and final path name silty different as we need to ignore first part from location 2 ( i.e K:\backup\) 

 

Backup location1 : \\backupserver\sqlbackup

Backup location 2  : K:\Backups\localserver\PRD_DW_ABCD\FULL\localserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak

 

I am looking for final location by using both backup locations (concatenation and remove part of k:\backup from send backup location )

 

Path should be as

 

\\backupserver\sqlbackup\localserver\PRD_DW_ABCD\FULL\localserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak

 

1 Reply

@nasdba , do you mean a simple replace?

 

declare @base nvarchar(400) = N'\\backupserver\sqlbackup'
declare @path nvarchar(400) = N'K:\Backups\localserver\PRD_DW_ABCD\FULL\localserver_PRD_DW_ABCD_FULL_20210621_140002_1.bak'

select replace(@path, N'K:\Backups', @base);