quote:

---

Originally posted by darkdusky

Here's a link to a vbscript program you can set-up using Windows Scheduler to find latest *.bak file and copy it to a new location, and deletes old *.bak files in the new folder. So the new folder contains only the latest backup file.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117870&SearchTerms=bak

Here is a stored procedure to restore a database in a directory folder from *.bak file. The above script ensures there is only the latest BAK file in the folder.
I based following on a proc by nigel rivett which includes restoring from transaction backups also. http://www.nigelrivett.net/SQLAdmin/s_RestoreDatabase.html

You will need to alter the Bak file name to match your own naming, e.g. change " '_db_%.bak' " section to match your files.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[s_RestoreFullDatabase] Script Date: 06/06/2008 11:45:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Used to restore database from a windows directory


CREATE proc [dbo].[s_RestoreFullDatabase]
@SourcePath varchar(200) ,
@DataPath varchar(200) ,
@LogPath varchar(200)

as

/*
usage:
exec s_RestoreFullDatabase
@SourcePath = 'c:\a_test\' ,
@DataPath = 'c:\a_test\' ,
@LogPath = 'c:\a_test\'

*/

/*
Get all files from directory (they will be *.bak)
The process is controlled by the files in the directory
If there is a full backup then restore it.

*/

declare @dbname varchar(128) ,
@cmd varchar(2000) ,
@filename varchar(128) ,
@s varchar(128) ,
@t varchar(128) ,
@sql nvarchar(2000)

create table #files (lname varchar(128), pname VARCHAR(128), type varchar(10), fgroup varchar(128), size varchar(50), maxsize varchar(50))
create table #dir (s varchar(2000))

-- get list of files in directory
select @cmd = 'dir /B ' + @SourcePath + '*.bak'
insert #dir exec master..xp_cmdshell @cmd

delete #dir
where s is null
or s not like '%.bak'
or ( s not like '%^_db^_%' escape '^'
)


select * from #dir

-- deal with each database in turn
while exists (select * from #dir)
begin
-- any Full backups
select @dbname = null
select top 1 @dbname = left(s,charindex('_db_', s) - 1)
from #dir
where charindex('_db_', s) <> 0
order by s


-- find the last full backup for this db
select @filename = null
select @filename = max(s)
from #dir
where s like @dbname + '_db_%.bak'


-- now we can go through each file in turn for this database and restore it
while exists (select * from #dir where s like @dbname + '^_%' escape '^')
begin
select top 1 @filename = s
from #dir
where s like @dbname + '^_%' escape '^'
order by right(s, 20)

select filename = @filename

if @filename like '%^_db^_%' escape '^'
begin
-- restore a full backup
if exists (select * from master..sysdatabases where name = @dbname)
begin
select @cmd = 'drop database ' + @dbname
exec (@cmd)
end

-- now buld the restore
select @cmd = null ,
@s = ''

while @s < (select max(lname) from #files)
begin
select top 1 @s = lname, @t = type from #files where lname > @s order by lname

select @cmd = coalesce(@cmd + ',move ', '') + '''' + @s
+ ''' to ''' + case when @t = 'D' then @DataPath else @LogPath end
+ @s + ''''
end
select @cmd = 'restore database ' + @dbname
+ ' from disk = ''' + @SourcePath + @filename + ''' with File =1, NOUNLOAD, STATS = 10, REPLACE'
exec (@cmd)
end


delete #dir where s = @filename
end

end

---