Post reply

Restore from a backup with a wildcard ??

  • January 30, 2015 at 8:15 am

    #314195

    I have a backup that comes to me nightly in the format of XXXX_YY_MM_DD.bak

    where the date is incremented each night the previous night backup is deleted when the next days is added so in general

    I have only one in that folder. I wanted to setup a restore to run nightly

    RESTORE DATABASE [XXXData] FROM DISK = 'C:\folder\ExtractedData\app_XXX_backup_15_01_28.bak' --location of .bak file

    WITH REPLACE

    I would like to do something like

    RESTORE DATABASE [XXXData] FROM DISK = 'C:\folder\ExtractedData\app_XXX_backup*.bak' --location of .bak file

    WITH REPLACE

    While I'm asking.. In case there is an older one left behind(which shouldn't happen) I saw something about "LATESTFULL" but think its a redgate command?

    Thanks in Advance

    Joe

  • January 30, 2015 at 9:15 am

    #1774381

    Hi

    I have read a bit and think it cannot use a wild card

    I seems that this addition will work..

    declare @Bakpath varchar(80)

    set @bakpath = 'C:\xxx\ExtractedData\app_xxx_backup_' + cast(year(getdate()) as varchar(12)) + '_'+ RIGHT('0' + RTRIM(month(getdate())), 2) +'_' + RIGHT('0' + RTRIM(Day(getdate())), 2)+'.bak'

    RESTORE DATABASE [xxx] FROM DISK = @bakpath --location of .bak file

    WITH REPLACE

    GO

    Does anyone see any drawback in using this?

    Thanks

    Joe

  • January 30, 2015 at 9:30 am

    #1774388

    The only real drawback is the pattern of the file changing or you not accounting for something with the conversions.

    The other options are writing some type of script that looks at the actual files and picks the latest one, but that's certainly more complex in development and potentially maintenance.

  • January 30, 2015 at 9:51 am

    #1774398

    If you usually only have one file in your folder, you should be able to get its name quite easily using xp_cmdshell, Powershell, or your favourite other scripting tool. Even on the occasions there are more than one, you can import the names in date order and delete every reference except the most recent.

    John

  • January 30, 2015 at 2:46 pm

    #1774487

    There are many options for obtaining the file name for an automated restore routine. You could connect to the source SQL Server directly to obtain backup metadata from msdb. You could run xp_cmdshell, as mentioned by John previously. xp_cmdshell will allow you to run MSDOS commands to identify your most recent .BAK file. One final alternative would be to have a separate script or job that inserts backup metadata into a table (from SQL directly or use Powershell to read Windows).

    Blog: http://sqlexchange.wordpress.com

  • January 30, 2015 at 3:16 pm

    #1774498

    xp_DirTree 'd:path',1,1 will return file names without a trip through xp_CmdShell if all you're looking for is file names.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 21, 2016 at 8:32 pm

    #1902328

    I think the easiest way to solve this problem by creating a powershell script to rename database backup to generic name. Which you can use in your restore script.

    Rename-Item C:\ProdBackup\BackupDatabase_*.bak BackupDatabase.bak

    Hope that will help someone.

  • September 22, 2016 at 2:57 am

    #1902374

    Steve Jones - SSC Editor (1/30/2015)

    The only real drawback is the pattern of the file changing or you not accounting for something with the conversions.

    The other options are writing some type of script that looks at the actual files and picks the latest one, but that's certainly more complex in development and potentially maintenance.

    I've written a script that does just that. Some day I'll get round to genericising it and turning it into a blog post.

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

Viewing 8 posts - 1 through 7 (of 7 total)

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