Use T-SQL to read backup filename from disk
-
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!
-
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
-
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
-
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
-
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.
-
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
-
Your script works and helps me achieve my goal. Thanks for your help!
-
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.
-
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.
-
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 -
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???
-
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 -
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.
-
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