1.如果你够懒，不想一步一步点路径，一步一步选择
2.如果你连单个备份数据库的存储过程都不想多执行,一般每还原一个需要修改数据库名
下面的脚本适合你：
1 /********************************************** 2 Description:This script help you restore database batch. 3 Pay attention:You'd better name the database like databaseName+number. 4 The database name is nessasery. 5 6 This script not perfect,and please make it progress if you want. 7 8 Author:jiangxiaoqaing 9 Script Date:2013-09-29 10 Modify history: 11 12 13 **********************************************/ 14 15 ALTER procedure [dbo].[sp_restoreDBBatch] 16 --The path your .bak file store 17 @database_bak_path varchar(200), 18 --The distination you want your database file store 19 @database_path varchar(200) 20 as 21 begin 22 declare @bak_databaseName varchar(200), 23 @DynamicSql varchar(500)=null 24 25 /*Judge the '#tmpTable' object if exists,the #tmpTable store backup 26 database name and path*/ 27 if (OBJECT_ID('#tmpTable') is not null) 28 drop table #tmpTable 29 create table #tmpTable 30 ( 31 DBName varchar(200) 32 ) 33 34 /*using extended procedure xp_cmdshell to get the path and name*/ 35 set @DynamicSql='cd /d "'+@database_bak_path+'"&&dir /a /b /s *.bak' 36 insert into #tmpTable exec xp_cmdshell @DynamicSql 37 38 /*If the backup path not exists,make the directory*/ 39 set @DynamicSql='if not exist '+@database_path+' mkdir '+@database_path+'' 40 exec xp_cmdshell @DynamicSql 41 42 declare bak_DBPathandName cursor 43 for 44 select DBName from #tmpTable 45 open bak_DBPathandName 46 while @@FETCH_STATUS=0 47 begin 48 fetch next from bak_DBPathandName into @bak_databaseName 49 declare @s varchar(200) 50 /*Get the database backup file name,store your specify path*/ 51 set @s= reverse(substring(reverse(@bak_databaseName),0,CHARINDEX('\',reverse(@bak_databaseName)))) 52 /*To get the database name,and tick the number*/ 53 BEGIN 54 WHILE PATINDEX('%[0-9]%',@S) > 0 55 BEGIN 56 set @s=stuff(@s,patindex('%[0-9]%',@s),1,'') 57 end 58 /*Tick the '.bak' affix*/ 59 set @s=replace(@s,'.bak','') 60 end 61 /*Backup single database*/ 62 exec restore_database_proc @bak_databaseName,@s,@database_path 63 end 64 close reachDBName 65 deallocate reachDBName 66 end
Resore single DB:
1 /******************************************* 2 Descript:SQL server 2008 backup database. 3 Author:jiangxiaoqiang 4 Date:2013-09-26 5 Modify history: 6 7 *******************************************/ 8 9 ALTER PROCEDURE [dbo].[restore_database_proc] 10 @database_bak_path varchar(100),--bak file store path 11 @database_name varchar(100),--The new database name(Not a LogicalName and PhysicalName) 12 @database_path varchar(200)--restore new database file store path,the path you want to store data file 13 AS 14 --exec( 'RESTORE FILELISTONLY FROM DISK = N''' + @database_bak_path + '''') 15 --select * from tempdb..sysobjects where name ='#tmp_file' 16 if OBJECT_ID('tempdb..#tmp_file') is not null 17 DROP TABLE #tmp_file 18 19 create table #tmp_file 20 ( 21 LogicalName nvarchar(128), 22 PhysicalName nvarchar(260), 23 Type char(1), 24 FileGroupName nvarchar(128), 25 Size numeric(20,0), 26 MaxSize numeric(20,0), 27 FileId bigint, 28 CreateLSN numeric(25,0), 29 DropLSN numeric(25,0) NULL, 30 UniqueID uniqueidentifier, 31 ReadOnlyLSN numeric(25,0) NULL, 32 ReadWriteLSN numeric(25,0) NULL, 33 BackupSizeInBytes bigint, 34 SourceBlockSize int, 35 FileGroupID int, 36 LogGroupGUID uniqueidentifier NULL, 37 DifferentialBaseLSN numeric(25,0) NULL, 38 DifferentialBaseGUID uniqueidentifier, 39 IsReadOnly bit, 40 IsPresent bit, 41 TDEThumbprint varbinary(32) 42 ) 43 --Database datafile full path 44 declare @database_mdf_path varchar(1000) 45 --Database log file full path 46 declare @database_log_path varchar(1000) 47 48 --The old database name 49 declare @database_mdf_oldname varchar(1000) 50 --The old database old log name 51 declare @database_log_oldname varchar(1000) 52 53 set @database_mdf_path = @database_path + '/' + @database_name + '.mdf' 54 set @database_log_path = @database_path + '/' + @database_name + '_Log.ldf' 55 56 --INSERT INTO #tmp_file EXEC ('restore_database_proc N''' + @database_bak_path + '''') 57 INSERT INTO #tmp_file EXEC ('RESTORE FILELISTONLY FROM DISK = N''' + @database_bak_path + '''') 58 set @database_mdf_oldname = (select LogicalName from #tmp_file where Type = 'D') 59 set @database_log_oldname = (select LogicalName from #tmp_file where Type = 'L') 60 --select @database_mdf_oldname=LogicalName from #tmp_file where Type = 'D' 61 62 --select @database_log_oldname =LogicalName from #tmp_file where Type = 'L' 63 exec( 64 ' 65 RESTORE DATABASE ' + @database_name + 66 ' 67 FROM DISK = ''' + @database_bak_path + '''' + 68 ' 69 WITH 70 MOVE ''' + @database_mdf_oldname + ''' TO ''' + @database_mdf_path + ''',' + 71 ' 72 MOVE ''' + @database_log_oldname + ''' TO ''' + @database_log_path + '''' 73 )
