This is a great query that helps move and restore dbs very bulk very fast, however, it was made to work with sql 2000 and couldn't believe that it was not updated for any of the newer versions so we have modified the script to run on SQL Servers 2008, 2008r2, and 2012 (match the number of columns for the mentioned versions). we will also include a bulk back query that backs up all dbs in an instance but the system dbs.

the modifications were (we believe that Server 2005 should use the same code included in the script):

/***************************************************************************************/-- Procedure Name: sp_CSS_RestoreDir-- Purpose: Restore one or many database backups from a single directory. This script reads all -- database backups that are found in the @restoreFromDir parameter.-- Any database backup that matches the form %_db_% will be restored to-- the file locations specified in the RestoreTo... parameter(s). The database-- will be restored to a database name that is based on the database backup-- file name. For example Insurance_db_200305212302.BAK will be restored to-- a database named Insurance. The characters preceeding the '_db_' text determines-- the name.---- Input Parameters: @restoreFromDir - The directory where the database backups are located-- @restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to-- @restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to. If-- this parameter is not provided then the log files are restored to @restoreToDataDir.-- @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,-- also allows for secondary data files 'ndf' to to be in a different dir than mdf files-- @DBName - restore just this one database - selects the latest bak file-- -- Output Parameters: None---- Return Values: ---- Written By: Chris Gallelli -- 8/22/03-- Modified By: -- Modifications: Bruce Canaday -- 10/20/2003-- Added optional parameters @MatchFileList and @DBName-- Bruce Canaday -- 10/24/2003-- Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame-- This is to handle databases such as ALIS_DB-- Bruce Canaday -- 10/28/2003-- When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist-- Bruce Canaday -- 11/04/2003-- Allow spaces in the @restoreFromDir directory name-- paul Wegmann -- 07/11/2012-- Chnaged the create table to allow more feilds to support SQL Server 2008r2 and SQl Server 2012-- create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),-- FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,-- FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )-- Paul Wegmann -- 07/11/2012 changed from stored proc to set-- declare @restoreFromDir varchar(255), -- @restoreToDataDir varchar(255),-- @restoreToLogDir varchar(255) ,-- @MatchFileList char(1) ,-- @OneDBName varchar(255) ---- set @restoreFromDir = 'location of directory where your backup exist'-- set @restoreToDataDir = 'location where your data files will be restored too'-- set @restoreToLogDir = 'location of LDF files needs to be restored too'-- set @MatchFileList = 'N'-- set @OneDBName = null---- Sample Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log' (if you use declare/set option then you don't have to use this command to restore) ---- Alternate Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', @MatchFileList = 'Y' (if you use declare/set option then you don't have to use this command to restore) ---- Reviewed By: Anoar Hassan -- /***************************************************************************************/

select * from #dirList where filename like '%_db_%' --order by filename

if @OneDBName is null declare BakFile_csr cursor for select * from #dirList where filename like '%_db_%bak' order by filenameelse begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above select @searchName = @OneDBName + '_db_%bak' declare BakFile_csr cursor for select top 1 * from #dirList where filename like @searchName end

ahassan 82526 (7/12/2012)This is a great query that helps move and restore dbs very bulk very fast, however, it was made to work with sql 2000 and couldn't believe that it was not updated for any of the newer versions so we have modified the script to run on SQL Servers 2008, 2008r2, and 2012 (match the number of columns for the mentioned versions). we will also include a bulk back query that backs up all dbs in an instance but the system dbs.

the modifications were (we believe that Server 2005 should use the same code included in the script):

/***************************************************************************************/-- Procedure Name: sp_CSS_RestoreDir-- Purpose: Restore one or many database backups from a single directory. This script reads all -- database backups that are found in the @restoreFromDir parameter.-- Any database backup that matches the form %_db_% will be restored to-- the file locations specified in the RestoreTo... parameter(s). The database-- will be restored to a database name that is based on the database backup-- file name. For example Insurance_db_200305212302.BAK will be restored to-- a database named Insurance. The characters preceeding the '_db_' text determines-- the name.---- Input Parameters: @restoreFromDir - The directory where the database backups are located-- @restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to-- @restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to. If-- this parameter is not provided then the log files are restored to @restoreToDataDir.-- @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,-- also allows for secondary data files 'ndf' to to be in a different dir than mdf files-- @DBName - restore just this one database - selects the latest bak file-- -- Output Parameters: None---- Return Values: ---- Written By: Chris Gallelli -- 8/22/03-- Modified By: -- Modifications: Bruce Canaday -- 10/20/2003-- Added optional parameters @MatchFileList and @DBName-- Bruce Canaday -- 10/24/2003-- Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame-- This is to handle databases such as ALIS_DB-- Bruce Canaday -- 10/28/2003-- When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist-- Bruce Canaday -- 11/04/2003-- Allow spaces in the @restoreFromDir directory name-- paul Wegmann -- 07/11/2012-- Chnaged the create table to allow more feilds to support SQL Server 2008r2 and SQl Server 2012-- create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),-- FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,-- FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )-- Paul Wegmann -- 07/11/2012 changed from stored proc to set-- declare @restoreFromDir varchar(255), -- @restoreToDataDir varchar(255),-- @restoreToLogDir varchar(255) ,-- @MatchFileList char(1) ,-- @OneDBName varchar(255) ---- set @restoreFromDir = 'location of directory where your backup exist'-- set @restoreToDataDir = 'location where your data files will be restored too'-- set @restoreToLogDir = 'location of LDF files needs to be restored too'-- set @MatchFileList = 'N'-- set @OneDBName = null---- Sample Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log' (if you use declare/set option then you don't have to use this command to restore) ---- Alternate Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', @MatchFileList = 'Y' (if you use declare/set option then you don't have to use this command to restore) ---- Reviewed By: Anoar Hassan -- /***************************************************************************************/

select * from #dirList where filename like '%_db_%' --order by filename

if @OneDBName is null declare BakFile_csr cursor for select * from #dirList where filename like '%_db_%bak' order by filenameelse begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above select @searchName = @OneDBName + '_db_%bak' declare BakFile_csr cursor for select top 1 * from #dirList where filename like @searchName end

I could really use this but I get the following error after I create and try to run this SP. I've tried it using with and without the set/declare option.

RESTORING DATABASE MY2008_db_Data12_From_backup_2012_11_01_180010_0528432.Msg 102, Level 15, State 1, Line 1Incorrect syntax near '.'.Msg 319, Level 15, State 1, Line 1Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.