How To Use Wildcard in SELECT Statement (but not in WHERE clause)
-
I'm trying to check for the existence of a backup file created on today's date but I won't know (or care about) the hour or minute that it was backed up. I know you can use a wildcard such as '%' in a WHERE clause but can you use a wildcard in the first part of a SELECT statement? Below is a snippit of the query. I'd like to add something before '.bak' to wildcard the four characters representing hour and minutes.
USE master
--Create temp table for use as array
Declare @tbl table (RowId int identity(1,1), DBName varchar(30))
CREATE TABLE #fileexist ([File Exists] int, [File is a Directory] int, [Parent Directory Exists] int)
--populate temp table with all user db names
Insert @tbl
--SELECT name FROM sysdatabases WHERE dbid > 5 AND name NOT LIKE 'LiteSpeedLocal'
SELECT name FROM sysdatabases WHERE name = 'testdb'
--Verify File Existence
DECLARE @bakfile varchar(255)
SELECT @bakfile = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + 'testdb' + '_db_' + Right(convert(char(8),getdate(),112),8) + '.bak'
PRINT @bakfile
INSERT INTO #fileexist([File Exists], [File is a Directory], [Parent Directory Exists])
EXEC master.dbo.xp_fileexist @bakfile
IF EXISTS (SELECT 1 FROM #fileexist WHERE [File Exists] = 1)
PRINT 1
USE TEMPDB DROP TABLE #fileexist
-
xp_fileexist does not work with wild cards, so unless you know the exact file name I don't think you are going to have any luck getting what you want.
James.
-
It's a horrible design but it sounds like you want something like the following:
USE master
--Create temp table for use as array
Declare @tbl table (RowId int identity(1,1), DBName varchar(30))
CREATE TABLE #fileexist (Direntry VARCHAR(8000))
--populate temp table with all user db names
Insert @tbl
--SELECT name FROM sysdatabases WHERE dbid > 5 AND name NOT LIKE 'LiteSpeedLocal'
SELECT name FROM sysdatabases WHERE name = 'testdb'
--Verify File Existence
DECLARE @DynamicSQL varchar(4000)
SELECT @DynamicSQL = 'dir "E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\testdb*.bak"'
INSERT #fileexist([Direntry])
EXEC master.dbo.xp_cmdshell @DynamicSQL
SET @DynamicSQL = 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\testdb_db_' + Right(convert(char(8),getdate(),112),8) + '%.bak'
IF EXISTS (SELECT 1 FROM #fileexist WHERE DirEntry LIKE @DynamicSQL)
PRINT 1
USE TEMPDB DROP TABLE #fileexist
There's no reason you don't know what the name of the file is as all that information is available in the MSDB database, that's what it's there for. For example, if I wanted to know what the OS filename (physical filename) was for the last backup of the tempdb database on my server, I would do the following:
DECLARE @BackupFilename VARCHAR(512)
SELECT TOP 1
@BackupFilename = MF.[physical_device_name]
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily AS MF
ON MF.[media_set_id] = S.[media_set_id]
WHERE [database_name] = 'tempdb'
ORDER BY [backup_start_date] DESC
-
Ed: why would you ever backup tempdb? there's not much point as it's wiped clean each time you restart sql server.
or is this just an example?
---------------------------------------
elsasoft.org -
Actually I can't use the backups recorded in MSDB because these backup files are copied from another server. I'm attempting to automate restores of .bak files from another server, so the destination server is unaware of these backups. Perhaps I could just write a script to modify the filename to not include the hour and minutes, then I would know the exact filename.
Has anyone attempted to do this (copy backups from one server to another and then restore via schedule job)? Thanks.
By the way, I'm not backing up tempdb, it's testdb.
-
"or is this just an example?"
It's an example.
"Has anyone attempted to do this (copy backups from one server to another and then restore via schedule job)? Thanks."
I do just that on demand with a web application. If the backups are made on another server then the names of the back files are available on the other server in the MSDB so adding a linked server would make that information available to you. You can fully automate the process of copying databases from one server to another like I have. I have encapsulated the entire process in one procedure on my production server, the procedure does the following:
- Backs up the database on the Production server to a shared NAS device so there's no copy involved, but copying the file is a terribly simple matter.
- Composes a restore statment.
- Executes the restore statment via sp_executesql on the development server.
If this is something you are doing frequently, which it sounds like it is, then it will cost you less to fully automate it in the long run.
-
Thanks for your reply. It's kind of a kludgy setup but we're required to copy these backup files to a DRF and restore them there daily. I'm unable to set up a linked server, mirroring, log shipping, or replication, etc because the source and destination servers cannot talk to each other due to firewalls in between.
-
See my reply to you in the backup forum.
You could of course have the other SQL machine send an email that inserts that information to a table on the destination server, might be some slight security issues with that approach though
It should be possible though! Although xp_readmail will be removed in a future version of SQL (anyone know what the replacement is?) -
Then you are looking for a kludgy solution to a kludgy problem. I personally perfer something like Anders' idea in this case. My approach is always to look for the solution that provides the most certainty. If there's any way you can provide the job with the names of the files you are better off in my opinion. Email is one but you could also create a .txt file that you could copy along with the database backups in which you could write the names of the files. Heck you could even create another database which contains the names of the files. Then you could restore that database first and read the names of the files prior to restoring.
-
Another solution could be to use a For Each loop in SSIS to do the restore, some rules would have to be set down for identifying which file is which database, but even using the default names from SQL that should be pretty easy. With the For Each loop you would not need to know the file names at all, it could just simply look at the directory and restore all files there (just make really sure that security is not breeched).
-
Guys, thanks for the suggestions. I'm going to try using a scheduled job with a script that creates the backup files with a filename that is easier to work with. Then when I restore them on the destination server I will not need to deal with the hours/minutes in the filename that could be different from day to day. If that fails I can always look into your other suggestions.
-
Sorry if I am a little naive here, but if both of the databases are on different networks could you not create a VPN from the "copy from" network to the "copy to" network and schedule A t-sql script?
I would guess that this would be a slow approach though.
----------------------------------------------------
-
You could use this query to see if the backup finished properly and is not damaged.
select a.database_name,b.physical_device_name,is_damaged, backup_start_date, backup_finish_date
from msdb..backupset a
join msdb..backupmediafamily b on a.media_set_id=b.media_set_id
where database_name like 'AdventureWorks' and
type='D' and convert(varchar,backup_start_date,101) = convert(varchar,getdate(),101)
order by backup_finish_date desc
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply