Restor Verify Only command

Vijay Kumar 2,016 Reputation points

Hi Team,

Please help me to fix syntax error near set backup path?

/* get the latest full backup files */

  @backup_source_path VARCHAR(1000),
  @LS_backup_name VARCHAR(1000),
  @file_names VARCHAR(4000),
  @sql_cmd VARCHAR(1000)

SET @backup_source_path = '\\\bkup_vol01\DB\FULL,\\\bkup_vol02\DB\FULL,\\\bkup_vol03\DB\FULL,\\\bkup_vol04\DB\FULL',

CREATE TABLE #backup_filename (backup_filename VARCHAR(1000) NULL)

SET @sql_cmd = 'master..xp_cmdshell ' + '''dir /B '+ @backup_source_path + '*.bak'''
exec sp_configure 'show advanced', 1; reconfigure with override;
exec sp_configure 'xp_cmdshell', 1; reconfigure with override;

INSERT #backup_filename (backup_filename) EXEC (@sql_cmd)

exec sp_configure 'xp_cmdshell', 0; reconfigure with override;

exec sp_configure 'show advanced', 0; reconfigure with override;

DELETE #backup_filename WHERE backup_filename IS NULL  or backup_filename = 'File Not Found'


SELECT @LS_backup_name =  MAX(backup_filename) FROM #backup_filename
select @LS_backup_name = substring(@LS_backup_name, 1, len(@LS_backup_name) - 6)

-- delete all backup files that are not similar to latest file name
delete #backup_filename where backup_filename not like @LS_backup_name + '%'

select @file_names =  


                             select ', '''  + @backup_source_path + u.backup_filename + '''' 

                             from #backup_filename u

                             where u.backup_filename = backup_filename

                             order by u.backup_filename

                             for xml path('')


from #backup_filename

group by backup_filename        


DROP TABLE #backup_filename
DECLARE @sql  varchar(8000) =''


FROM DISK ='+@file_names+''

print @sql

exec (@sql)

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,607 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Viorel 111.6K Reputation points

    Remove ‘,’ at the end of line 10 and try again.

  2. Guoxiong 8,126 Reputation points

    Did you try this?

    SET @Chuck _source_path = '\\cifs_careabcdefx_bkup_vol01\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\cifs_careabcdefx_bkup_vol02\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\cifs_careabcdefx_bkup_vol03\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\cifs_careabcdefx_bkup_vol04\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\'

  3. MelissaMa-MSFT 24,176 Reputation points

    Hi @Vijay Kumar ,

    Thank you so much for posting here in Microsoft Q&A.

    It seems that you already fixed the syntax error by removing final ‘,’ and adding '\' after 'FULL'.

    Invalid device name The length of the device name provided exceeds supported limit (maximum length is:259). Resume the backup statement with valid device name

    This is a warning message returned by the SQL server, suggesting that length of the directory path to the backup device (e.g. temporary folder path plus the database name) exceeded 259 characters.

    As the message suggests the length of the path + database name exceeds 259 characters.

    select len('\\\cifs_careabcdefx_bkup_vol01\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\\cifs_careabcdefx_bkup_vol02\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\\cifs_careabcdefx_bkup_vol03\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\\cifs_careabcdefx_bkup_vol04\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\')  


    Please consider to follow below options:

    1. Relocate the database to another location where the path + database name is less than 259 characters.
    2. Rename the database to a shorter name if possible.
    3. Backup to less folder path every time.

    Best regards

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Hot issues November--What can I do if my transaction log is full?
    Hot issues November--How to convert Profiler trace into a SQL Server table

  4. MelissaMa-MSFT 24,176 Reputation points

    Hi @Vijay Kumar ,

    Could you please provide the output of #backup_filename table?

    I tried your query from my side but failed to get the proper backup_filename by executing below line:

     SET @sql_cmd = 'master..xp_cmdshell ' + '''dir /B '+ @backup_source_path + '*.bak'''  

    You could try with below format by adding Double quotes and changing the location of '/b'.

    SET @sql_cmd = 'master..xp_cmdshell  DIR ' + '"' + @backup_source_path + '" /b'  

    But above may be still not working since there are 4 paths in your @Chuck _source_pat which is not accepted in xp_cmdshell DIR command.

    You could try to use a loop or cursor to split the @Chuck _source_path into 4 parts and insert into the xp_cmdshell DIR command one by one.

    Best regards

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  5. Erland Sommarskog 100.8K Reputation points MVP

    I reviewed the original post more closely, which lead me to delete my previous answer, since it was flat wrong. And furthermore, I found more details in Vijay's post that I find dubious.

    The script starts off:

     SET @backup_source_path = '\\\bkup_vol01\DB\FULL,\\\bkup_vol02\DB\FULL,\\\bkup_vol03\DB\FULL,\\\bkup_vol04\DB\FULL',  
     SET @sql_cmd = 'master..xp_cmdshell ' + '''dir /B '+ @backup_source_path + '*.bak'''  

    This dir command will list all files in the first three folders, and then only the *.bak files in the last folder. That makes little sense to me. OK, so it seems reasonable that these folders only have .bak files. However, at this point you only know the file names, but you don't know in which folder they are. I think you need to run one dir command per folder.

    The same problem comes back when you try to form the RESTORE VERIFYONLY command. You use @Chuck _source_path to form a complete path, but for that to work out, there must be a single path. When I posted my first answer, I did not pay to attention to that @Chuck _source_path included a list of paths.

    There are also problem with the RESTORE VERIFYONLY command. You are trying to do

    RESTORE VERIFYONLY FROM DISK = 'file1.bak', 'file2.bak', ...  

    but the correct syntax is (as I just learned):

    RESTORE VERIFYONLY FROM DISK = 'file1.bak', DISK = 'file2.bak', ...  

    However, you can only provide a list of files if they are part of the same backup. That is, you have a backup striped over multiple files. If the files you have are backups from different databases, you need to run one RESTORE VERIFYONLY per backup. (And which then can be done without dynamic SQL, as you can say


    Vijay, you need to give a thought of what you are really trying to do before we can proceed further.

    0 comments No comments