Post reply

Use T-SQL to read backup filename from disk

  • April 25, 2006 at 8:36 am

    #111924

    Is it possible to use a SQL script to check for a filename on disk? I would like to restore a .bak file from disk that was copied from another server nightly (therefore this SQL server is not aware of it). I'd like to check in a specified directory for the existence of a backup with the filename format of dbname_db_yyyymmddtttt.bak (e.g. pubs_db_200604200100.bak) with today’s date (yyyymmdd) and ignoring the time (tttt) and then restore it. Thanks for any advice you can offer!

  • April 25, 2006 at 10:37 pm

    #634205

    You can use the stock standard DIR command executed via xp_cmdshell. Just insert the result into a temp table.

    EG:

     CREATE TABLE #dir (
  [ID] int IDENTITY(1,1)
  , [Dir] varchar(255) 
)
 
 INSERT INTO #Dir([Dir])
 EXEC master.dbo.xp_cmdshell 'DIR <my path/file name>'

    You could also try using the undocumented, unsupported extended stored procedure xp_getfiledetails

     CREATE TABLE #file (
  Altname varchar(30)
  , Filesize int
  , CreateDate varchar(8)
  , CreateTime varchar(8)
  , WriteDate varchar(8)
  , WriteTime varchar(8)
  , AccessDate varchar(8)
  , AccessTime varchar(8)
  , Attributes int 
)
    INSERT INTO #File 
EXEC master.dbo.xp_getfiledetails '<my path/file name>'

    Of course, as noted above, this is unsupported and as I discovered, won't work in SQL 2005.

     

    --------------------
    Colt 45 - the original point and click interface

  • April 26, 2006 at 6:59 am

    #634267

    Thanks for your reply. I'm going to try to use something like this:

    CREATE TABLE #dir ([ID] int IDENTITY(1,1), [Dir] varchar(255) )

    INSERT INTO #Dir([Dir])

    EXEC master.dbo.xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs_db_20060419.bak2"'

    IF NOT EXISTS (SELECT DIR FROM #dir WHERE ID = '6' AND DIR = 'File Not Found')

    PRINT '1'

    ELSE PRINT '0'

    DROP TABLE #dir

  • April 26, 2006 at 7:45 am

    #634287

    Alternatively, you could use the following undocumented command

    exec master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs_db_20060419.bak2'

    which will give you the following output

    File Exists File is a Directory Parent Directory Exists

    ----------- ------------------- -----------------------

    0           0                   1

    Not sure whether it exists in SQL 2005 as is, it might have been changed to be being owned by sys

     

     

    I feel the need - the need for speed

    CK Bhatia

  • April 26, 2006 at 8:10 am

    #634300

    CK Bhatia,

    Thanks for your reply. I like your suggestion but I am stuck on a couple things. Here is what I am trying:

    CREATE TABLE #fileexist ([ID] int IDENTITY(1,1), [File Exists] int, [File is a Directory] int, [Parent Directory Exists] int)

    INSERT INTO #fileexist([File Exists], [File is a Directory], [Parent Directory Exists])

    EXEC master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs_db_' + Right(convert(char(8),getdate(),112),8) + '.bak'

    SELECT * FROM #fileexist

    IF EXISTS (SELECT File_Exists FROM #fileexist WHERE File_Exists = '1')

    PRINT '1'

    ELSE PRINT '0'

    DROP TABLE #fileexist

    There is something wrong with the string concatenation with the getdate. Also, do I need to bother dumping the result of xp_fileexist in a temp table or is there a better way to find whether it returns 'o' or '1' for the File Exists column?

    By the way, the file that I'm trying to check existence for is C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs_db_20060426.bak, and eventually I will need to check for existence of backup files with the time included in the stamp (i.e. pubs_db_200604260100.bak) so I will need to use a wildcard for the 4 characters for tttt. Thanks again.

  • April 26, 2006 at 8:19 am

    #634306

    You can use the following snippet of code

    CREATE TABLE #fileexist ([File Exists] int, [File is a Directory] int, [Parent Directory Exists] int)

    declare @fileName varchar(255)

    select @fileName = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs_db_' + Right(convert(char(8),getdate(),112),8) + '.bak'

    INSERT INTO #fileexist([File Exists], [File is a Directory], [Parent Directory Exists])

    EXEC master.dbo.xp_fileexist @filename

    IF EXISTS (SELECT 1 FROM #fileexist WHERE [File Exists] = 1)

    PRINT 1

    ELSE PRINT 0

    DROP TABLE #fileexist

     

    Also, I noticed that in your first post you were searching for a file with the extension .bak2 while this script had the extension as .bak

    Just didnt want you wondering later what was wrong with the script

    I feel the need - the need for speed

    CK Bhatia

  • April 26, 2006 at 8:36 am

    #634320

    Your script works and helps me achieve my goal. Thanks for your help!

  • April 26, 2006 at 11:40 am

    #634384

    One more thing about this. I would like to pass in the entire file path to xp_fileexist like this:

    EXEC master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @dbname + '_db_' + Right(convert(char(8),getdate(),112),8) + %

    Notice that I tried to use a "%" wildcard on the end so that it will check for the existance of a file with a name starting with "pubs_db_0426". That way it will ignore the 4 characters for the time stamp (tttt.bak, which could be different everyday) and the file extension. Basically I need to check for existence of a backup of the pubs db with today's date but I haven't been able to get this to work. Please let me know if you have any suggestions.

  • April 26, 2006 at 3:38 pm

    #634444

    Ryan - My apologies. I guess I didnt read your entire post - I am known to be too excitable. Must be all that coffee.

    Anyways - the following script can be used to find out the actual file name of the backup for a given database

    select f.physical_device_name from msdb.dbo.backupset s with (nolock), msdb.dbo.backupmediafamily f with (nolock)

    where s.database_name = 'pubs' and s.type = 'D'

    and s.backup_finish_date > convert(varchar(10), getdate(), 101)

    and s.media_set_id = f.media_set_id

    You can then pass that filename to the xp_fileexist procedure.

    Some people may suggest that you should not read or depend on the system tables, since they may be changed in a future version. My take on this is that, even if that does happen, I would need to change my scripts once.

     

    I feel the need - the need for speed

    CK Bhatia

  • April 27, 2006 at 5:04 am

    #634526

    But what about if the server you're restoring to does not know about the backup file as per the original posting?

    I think my DIR proposal provides much more flexibility. You can retrieve all the files in the directory so you can restore multiple files if necessary. You don't need to pass a file/database name. You can wrap it up in a function to do away with the temp table. I'd also change your logic a bit though.

    EG:

    CREATE TABLE #dir ([ID] int IDENTITY(1,1), [Dir] varchar(255) )

INSERT INTO #Dir([Dir])
EXEC master.dbo.xp_cmdshell 'DIR "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs_db_20060419.bak2"'

IF EXISTS (SELECT DIR FROM #dir WHERE DIR LIKE '%File Not Found%')
PRINT '0'
ELSE PRINT '1'

DROP TABLE #dir

    Also, for the wildcard, use an asterisk. The % is the wildcard for T-SQL. It'll make for easier debugging if you store the filename in a variable and print it before the Exec.

    EG:

    DECLARE @file varchar(100)
SET @File = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @dbname + '_db_' + Right(conver(char(8),getdate(),112),8) + '*'
PRINT @file
EXEC master.dbo.xp_fileexist @file

     

    --------------------
    Colt 45 - the original point and click interface

  • April 27, 2006 at 6:26 am

    #634543

    Thanks for joining back in with your comments. It's true that these backup files are copied from another server so this SQL server has no record of it in the backup tables. I tried this and I don't think the "*" works with xp_fileexist.

    EXEC master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs_db_20060427*'

    Returns "0" for File Exists.

    EXEC master.dbo.xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\pubs_db_200604270100.bak'

    Returns "1" for File Exists.

    That's why I was trying to use "%" but I found that "%" must be used with LIKE, so that wouldn't work either. Maybe a regular expression would work???

  • April 27, 2006 at 7:12 am

    #634554

    I'm betting xp_fileexist doesn't understand wildcards. It's probably looking for the existence of a fully specified file. How about trying the DIR method again

     

    --------------------
    Colt 45 - the original point and click interface

  • April 27, 2006 at 7:24 am

    #634559

    But will using DIR with xp_cmdshell allow you to plug in variables and funtions like this?

    DECLARE @dir varchar(255)

    SET @dir = 'DIR "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\" + @dbname + "_db_" + Right(convert(char(8),getdate(),112),8) + "*"'

    EXEC master.dbo.xp_cmdshell @DIR

    I haven't been able to get that to work. If the syntax can be worked out, I think we could have a solution.

  • May 5, 2006 at 7:32 am

    #636217

    Hi,

    try

    DECLARE @dir varchar(255)

    SET @dir = 'DIR /B "C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\ + @dbname + _db_ + Right(convert(char(8),getdate(),112),8) + '*' + '"'

    EXEC master.dbo.xp_cmdshell @DIR

    regards karl

    Best regards
    karl

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply