/**********************************************

     Description:This script help you restore database batch.

     Pay attention:You'd better name the database like databaseName+number.

     The database name is nessasery.

     This script not perfect,and please make it progress if you want.

     Author:jiangxiaoqaing

     Script Date:2013-09-29

     Modify history:

 **********************************************/

 ALTER procedure [dbo].[sp_restoreDBBatch]

     --The path your .bak file store

     @database_bak_path varchar(200),

     --The distination you want your database file store

     @database_path varchar(200)

 as

     begin

         declare @bak_databaseName varchar(200),

                 @DynamicSql varchar(500)=null            

         /*Judge the '#tmpTable' object if exists,the #tmpTable store backup

          database name and path*/

         if (OBJECT_ID('#tmpTable') is not null)

         drop table #tmpTable

         create table #tmpTable

         (

             DBName varchar(200)

         )

         /*using extended procedure xp_cmdshell to get the path and name*/

         set @DynamicSql='cd /d "'+@database_bak_path+'"&&dir /a /b /s *.bak'

         insert into #tmpTable exec xp_cmdshell @DynamicSql

         /*If the backup path not exists,make the directory*/

         set @DynamicSql='if not exist '+@database_path+' mkdir '+@database_path+''

         exec xp_cmdshell @DynamicSql        

         declare bak_DBPathandName cursor

         for

         select DBName from #tmpTable

         open bak_DBPathandName

         while @@FETCH_STATUS=0

         begin

             fetch next from bak_DBPathandName into @bak_databaseName

             declare @s varchar(200)

             /*Get the database backup file name,store your specify path*/

             set @s= reverse(substring(reverse(@bak_databaseName),0,CHARINDEX('\',reverse(@bak_databaseName))))

             /*To get the database name,and tick the number*/

             BEGIN

                 WHILE PATINDEX('%[0-9]%',@S) > 0

                 BEGIN

                     set @s=stuff(@s,patindex('%[0-9]%',@s),1,'')

                 end

                 /*Tick the '.bak' affix*/

                 set @s=replace(@s,'.bak','')

             end

             /*Backup single database*/

             exec restore_database_proc @bak_databaseName,@s,@database_path

         end

         close reachDBName

         deallocate reachDBName

     end

 /*******************************************

     Descript:SQL server 2008 backup database.

     Author:jiangxiaoqiang

     Date:2013-09-26

     Modify history:

 *******************************************/

 ALTER PROCEDURE [dbo].[restore_database_proc]

     @database_bak_path varchar(100),--bak file store path

     @database_name varchar(100),--The new database name(Not a LogicalName and PhysicalName)

     @database_path varchar(200)--restore new database file store path,the path you want to store data file

 AS

     --exec( 'RESTORE FILELISTONLY FROM DISK = N''' + @database_bak_path + '''')

     --select * from tempdb..sysobjects where name ='#tmp_file'

     if OBJECT_ID('tempdb..#tmp_file') is not null

     DROP TABLE  #tmp_file

     create table #tmp_file

     (

         LogicalName    nvarchar(128),

         PhysicalName nvarchar(260),

         Type char(1),

         FileGroupName nvarchar(128),

         Size numeric(20,0),

         MaxSize numeric(20,0),

         FileId bigint,

         CreateLSN numeric(25,0),

         DropLSN    numeric(25,0) NULL,

         UniqueID uniqueidentifier,

         ReadOnlyLSN    numeric(25,0) NULL,

         ReadWriteLSN numeric(25,0) NULL,

         BackupSizeInBytes bigint,

         SourceBlockSize    int,

         FileGroupID    int,

         LogGroupGUID uniqueidentifier NULL,

         DifferentialBaseLSN    numeric(25,0) NULL,

         DifferentialBaseGUID uniqueidentifier,

         IsReadOnly bit,

         IsPresent bit,

         TDEThumbprint varbinary(32)

     )

     --Database datafile full path

     declare @database_mdf_path varchar(1000)

     --Database log file full path

     declare @database_log_path varchar(1000)

     --The old database name

     declare @database_mdf_oldname varchar(1000)

     --The old database old log name

     declare @database_log_oldname varchar(1000)

     set @database_mdf_path =  @database_path + '/' + @database_name + '.mdf'

     set @database_log_path =  @database_path + '/' + @database_name + '_Log.ldf'     

     --INSERT INTO #tmp_file EXEC ('restore_database_proc N''' + @database_bak_path + '''')

     INSERT INTO #tmp_file EXEC ('RESTORE FILELISTONLY FROM DISK = N''' + @database_bak_path + '''')

     set @database_mdf_oldname = (select LogicalName from #tmp_file where Type = 'D')

     set @database_log_oldname = (select LogicalName from #tmp_file where Type = 'L')

     --select @database_mdf_oldname=LogicalName from #tmp_file where Type = 'D'

     --select @database_log_oldname =LogicalName from #tmp_file where Type = 'L'

     exec(

     '

     RESTORE DATABASE ' + @database_name  +

     '

     FROM DISK = ''' + @database_bak_path + '''' +

     '

     WITH

     MOVE ''' + @database_mdf_oldname + ''' TO ''' + @database_mdf_path + ''',' +

     '

     MOVE ''' + @database_log_oldname + ''' TO ''' + @database_log_path + ''''

     )