Restore Most current .bak file

[JoeZim]

I've managed to get this far building a restore SP using great tips from this forum:

CREATE PROCEDURE [dbo].[DBS_restore_Basel_database] 
AS
/*restore database */
declare @file nvarchar(4000)
select @file = 'D:\MSSQL\BACKUP\Basel\Basel_db_' + CONVERT(varchar(30),getdate() - 1,112) + '2030' + '.BAK'
Restore Database Basel
from disk = @file
with move 'Basel_data' to 'd:\mssql\data\Basel_db.mdf',
move 'Basel_log' to 'd:\mssql\data\Basel_db.ldf',
replace
GO

Now, is it possible to take the @file parameter one step further - I would like to have the SP automatically select the most recent .bak file in the server directory. Right now, the directory contains several .bak files, like so:

Basel_db_200606222030.BAK
Basel_db_200606232030.BAK
Basel_db_200606242030.BAK
Basel_db_200606252030.BAK
Basel_db_200606262030.BAK

Instead of coding the following paramter to get yesterday's:

declare @file nvarchar(4000)
select @file = 'D:\MSSQL\BACKUP\Basel\Basel_db_' + CONVERT(varchar(30),getdate() - 1,112) + '2030' + '.BAK'

I would like to declare @file to look for the most recent backup file. This is for a DR process and we may not be able to restore the database for a few days, thus eliminating the "getdate() - 1"

Joe

 

[JoeZim]

I finally tracked down a solution to my post, so I figured I'd share it. Credit goes to the following link:

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1099668,00.html

Had to setup a new SP titled "sp_ListFiles", which is documented in the above post.

Then, I created another SP with the following code, that incorporates sp_ListFiles, my restore, and another snip of code from the above post:

CREATE PROCEDURE [dbo].[DBS_restore_Basel_database_Test] 
AS
/*restore database */

CREATE TABLE #Files (MyFile varchar(200))

declare @Path varchar(400)
declare @file nvarchar(4000)
declare @mostrecent varchar(200)

set @Path = 'D:\MSSQL\BACKUP\Basel\'

execute sp_ListFiles @Path,'#Files','%.bak',NULL,0

select @mostrecent = Max(MyFile) FROM #Files
select @file = 'D:\MSSQL\BACKUP\Basel\' + @mostrecent

Restore Database Basel
from disk = @file
with move 'Basel_data' to 'd:\mssql\data\Basel_db.mdf',
move 'Basel_log' to 'd:\mssql\data\Basel_db.ldf',
replace

drop table #Files
GO

Finally, setup a job to execute the DBS_restore_Basel_database_Test SP and everything is working well.

May not be the best way, but it works!

Joe