Restor Verify Only command

Vijay Kumar 2,016 Reputation points
2020-12-22T17:20:14.167+00:00

Hi Team,

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

/* get the latest full backup files */

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


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

-- CREATE TEMP TABLE TO STORE DIR OUTPUT
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'

-- GET THE MOST RECENT BACKUP FILE

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 =  

              stuff((

                             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('')

              ),1,1,'')

from #backup_filename

group by backup_filename        


-- CLEAN UP

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

select @sql = 'RESTORE VERIFYONLY

FROM DISK ='+@file_names+''



print @sql

exec (@sql)





GO
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,808 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 112.5K Reputation points
    2020-12-22T17:23:05.237+00:00

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

  2. Guoxiong 8,126 Reputation points
    2020-12-22T19:02:29.223+00:00

    Did you try this?

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

  3. MelissaMa-MSFT 24,176 Reputation points
    2020-12-23T02:58:57.753+00:00

    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('\\10.11.12.13\cifs_careabcdefx_bkup_vol01\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\10.11.12.13\cifs_careabcdefx_bkup_vol02\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\10.11.12.13\cifs_careabcdefx_bkup_vol03\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\, \\10.11.12.13\cifs_careabcdefx_bkup_vol04\PCLSCAERPT1P$CAERPT1P\CKOLTP1P\FULL\')

    Output:
    318

    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
    Melissa

    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
    2020-12-24T05:47:23.557+00:00

    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
    Melissa

    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 101.8K Reputation points MVP
    2020-12-24T10:22:44.873+00:00

    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 = '\\10.11.12.13\bkup_vol01\DB\FULL,\\10.11.12.13\bkup_vol02\DB\FULL,\\10.11.12.13\bkup_vol03\DB\FULL,\\10.11.12.13\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 

    RESTORE VERIFYONLY FROM DISK = @filename

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

    0 comments