if exists (select 'SP exists' from sysobjects where name = 'usp_autorestore_backups' and xtype = 'p')
	drop proc usp_autorestore_backups
go
create procedure usp_autorestore_backups
	@movepathdata varchar(255),
	@movepathlog varchar(255),
	@backuppath varchar(255)
--	Create By:	Regan Galbraith
--	Create On:	2004-06-24
--	Purpose:	
--		This stored procedure was written to facilitate the automatic restore of databases.
--		It relies upon, and helps enforce, certain standards, for example the naming standard for
--		data files that it creates, and the log files. It relies upon a namiing standard for backups. 
--		
--		Currently, the environment that I am in has a naming standard that enforces no _'s in the 
--		database names. This allows me to do the charindex('_') searching I do to split out the DB name 
--		from a backup. If you environment allows _'s in the DB name, consider changing this to a 
--		patindex('_x_') or something like that, and then ensuring that it is used consistently in the backup
--		creation. You could even implement that as a input variable.
--
--	Example:
--		exec usp_autorestore_backups 'd:\data\','d:\log\','d:\backups\'
--
--	Possible future additions:
--		1> 	stripping out date of backup file creation ... maybe by removing /b from dir, and then 
--			accepting an input variable for date, to restore only certain backups
--			2>	accepting a parameter instead of apply the default .bak. Use the .bak as default
--		3>	implementing default value's for dir's, so that it can run without parm's ... good or bad?
--
--	Change Control:	version 1 - creation and adding of comment
--
as
begin
set nocount on
set quoted_identifier off

create table #backuplist(backupname varchar(128))

create table #filelistinfo
(LogicalName sysname null,
PhysicalName sysname null,
Type varchar(20) null,
FileGroupName sysname null,
FileSize bigint null ,
FileMaxSize Bigint null)

create table #direxists
(FileExists int,
FileIsDir int,
ParentDirExists int)

declare @database sysname
--declare @dbstatement varchar(255)
declare @restoreSQL varchar(510)
declare @filelistSQL varchar(255)
declare @datafile varchar(255)
declare @logfile varchar(255)
--declare @backupsubstring varchar(255)
--declare @movepathdata varchar(255)
--declare @movepathlog varchar(255)
--declare @movepathdocument varchar(255)
--declare @dirliststring varchar(255)
declare @backupname varchar(255)
--declare @backuppath varchar(255)
declare @dirSQL varchar(255)
--declare @filexists int
declare @succeed int
declare @ErrorDir varchar(6)

--set @movepathdata = 'd:\data\'
insert into #direxists exec master..xp_fileexist @movepathdata
if not exists(select 'Dir Exists' from #direxists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
	begin
	set @ErrorDir = 'Data'
	goto DirError
end
else
	begin
	delete from #direxists
--	set @movepathlog = 'd:\log\'
	insert into #direxists exec master..xp_fileexist @movepathlog
	if not exists(select 'Dir Exists' from #direxists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
		begin
		set @ErrorDir = 'Log'
		goto DirError
	end
	else
		begin
		delete from #direxists
--		set @backuppath = 'd:\backups\'
		insert into #direxists exec master..xp_fileexist @backuppath
		if not exists(select 'Dir Exists' from #direxists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
			begin
			set @ErrorDir = 'Backup'
			goto DirError
		end
	end
end

set @dirSQL = 'dir '+@backuppath+'*.bak /b'
--print @dirSQL

delete from #backuplist
insert into #backuplist
exec master..xp_cmdshell @dirSQL
--select * from #backuplist

declare backuplist cursor for select backupname from #backuplist

open backuplist
fetch next from backuplist into @backupname

while @@fetch_status <> -1
begin
-- this assumes a naming standard where your back names are databasename_ and then more detail. If you have 
-- a naming standard for backups of simply databasename.bak, then change this to charindex ('.' , for example.

    set @database = left(@backupname,(charindex('_',@backupname)-1))
    set @filelistSQL = 'restore filelistonly '--+@database
    set @filelistSQL = @filelistSQL + ' from disk = '''+@backuppath+@backupname+''''
--    print @filelistSQL
    insert into #filelistinfo exec (@filelistSQL)
    select @datafile=LogicalName from #filelistinfo where type = 'D'
--    print @datafile
    select @logfile=LogicalName from #filelistinfo where type = 'L'
--    print @logfile
    set @restoreSQL = 'restore database '+@database
    set @restoreSQL = @restoreSQL + ' from disk = '''+@backuppath+@backupname
    set @restoreSQL = @restoreSQL + ''' with replace,stats,move '''+@datafile
    set @restoreSQL = @restoreSQL + ''' to '''+@movepathdata + @database+'data.mdf'', move '''
    set @restoreSQL = @restoreSQL + @logfile+''' to '''+@movepathlog+@database+'log.ldf'''
    delete from #filelistinfo
    print @restoreSQL
    exec (@restoreSQL)
    fetch next from backuplist into @backupname
end

close backuplist
deallocate backuplist
goto finish

DirError:
Print 'An error with the '@errordir+' Directory given - please check that it exists. Thanks'
drop table #filelistinfo
drop table #backuplist
drop table #direxists
RETURN -1

Finish:
drop table #filelistinfo
drop table #backuplist
drop table #direxists
RETURN 0

end

--exec usp_autorestore_backups 'd:\data\','d:\log\','d:\backups\'

DECLARE @strSQL varchar(8000),
	@MyRestoreFilePath varchar(8000)

SELECT	@MyRestoreFilePath = 'D:\MSSQL\BACKUP\MyFileName.BAK'

CREATE TABLE #TEMP_TABLE
(
	LogicalName	nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	PhysicalName	nvarchar(260) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Type		char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	FileGroupName	nvarchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Size]		numeric(20,0) NULL,
	[MaxSize]	numeric(20,0) NULL
)

SELECT	@strSQL = 'RESTORE FILELISTONLY FROM DISK = ''' + @MyRestoreFilePath + ''''

INSERT INTO #TEMP_TABLE
	exec(@strSQL)

SELECT * FROM #TEMP_TABLE

quote:

---

Originally posted by supersql

How can i do autorestore in sql 2005 if i have 15 file groups and 12 ndf files in each group.

thanks

---