Restore multiple databases with sql scripts

You would need to post your dp definitions unless you want people to tell you to read the error messages.
The first is a mismatch in column names or number of data values does not meet the column numbers .
The second is a repeated iteration.

I used the below stored proc to

EXEC sp_RestoreFromAllFilesInDirectory 'C:\backup\', 'C:\Data\',  'C:\log\'

with error
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.


CREATE PROC [dbo].[sp_RestoreFromAllFilesInDirectory]

@SourceDirBackupFiles nvarchar(200), @DestDirDbFiles

nvarchar(200),@DestDirLogFiles nvarchar(200)

AS

--Originally written by Tibor Karaszi 2004. Use at own risk.

--Restores from all files in a certain directory. Assumes that:

--  There's only one backup on each backup device.

--  Each database uses only two database files and the mdf file

---is returned first from the RESTORE FILELISTONLY command.

--Sample execution:

-- EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\',

----'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’

SET NOCOUNT ON



--Table to hold each backup file name in

CREATE TABLE #files(fname varchar(200),depth int, file_ int)

INSERT #files

EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1



--Table to hold the result from RESTORE HEADERONLY. Needed to get

---the database name out from

CREATE TABLE #bdev(

 BackupName nvarchar(128)

,BackupDescription nvarchar(255)

,BackupType smallint

,ExpirationDate datetime

,Compressed tinyint

,Position smallint

,DeviceType tinyint

,UserName nvarchar(128)

,ServerName nvarchar(128)

,DatabaseName nvarchar(128)

,DatabaseVersion int

,DatabaseCreationDate datetime

,BackupSize numeric(20,0)

,FirstLSN numeric(25,0)

,LastLSN numeric(25,0)

,CheckpointLSN numeric(25,0)

,DatabaseBackupLSN numeric(25,0)

,BackupStartDate datetime

,BackupFinishDate datetime

,SortOrder smallint

,CodePage smallint

,UnicodeLocaleId int

,UnicodeComparisonStyle int

,CompatibilityLevel tinyint

,SoftwareVendorId int

,SoftwareVersionMajor int

,SoftwareVersionMinor int

,SoftwareVersionBuild int

,MachineName nvarchar(128)

,Flags int

,BindingID uniqueidentifier

,RecoveryForkID uniqueidentifier

,Collation nvarchar(128)

,FamilyGUID uniqueidentifier

,HasBulkLoggedData int

,IsSnapshot int

,IsReadOnly int

,IsSingleUser int

,HasBackupChecksums int

,IsDamaged int

,BegibsLogChain int

,HasIncompleteMetaData int

,IsForceOffline int

,IsCopyOnly int

,FirstRecoveryForkID uniqueidentifier

,ForkPointLSN numeric(25,0)

,RecoveryModel nvarchar(128)

,DifferentialBaseLSN numeric(25,0)

,DifferentialBaseGUID uniqueidentifier

,BackupTypeDescription nvarchar(128)

,BackupSetGUID uniqueidentifier

,CompressedBackupSize nvarchar(128)

)



--Table to hold result from RESTORE FILELISTONLY. Need to

---generate the MOVE options to the RESTORE command

CREATE TABLE #dbfiles(

 LogicalName nvarchar(128)

,PhysicalName nvarchar(260)

,Type char(1)

,FileGroupName nvarchar(128)

,Size numeric(20,0)

,MaxSize numeric(20,0)

,FileId int

,CreateLSN numeric(25,0)

,DropLSN numeric(25,0)

,UniqueId uniqueidentifier

,ReadOnlyLSN numeric(25,0)

,ReadWriteLSN numeric(25,0)

,BackupSizeInBytes int

,SourceBlockSize int

,FilegroupId int

,LogGroupGUID uniqueidentifier

,DifferentialBaseLSN numeric(25)

,DifferentialBaseGUID uniqueidentifier

,IsReadOnly int

,IsPresent int

,TDEThumbprint nvarchar(128)

)





DECLARE @fname varchar(200)

DECLARE @dirfile varchar(300)

DECLARE @LogicalName nvarchar(128)

DECLARE @PhysicalName nvarchar(260)

DECLARE @type char(1)

DECLARE @DbName sysname

DECLARE @sql nvarchar(1000)



DECLARE files CURSOR FOR

SELECT fname FROM #files



DECLARE dbfiles CURSOR FOR

SELECT LogicalName, PhysicalName, Type FROM #dbfiles



OPEN files

FETCH NEXT FROM files INTO @fname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @dirfile = @SourceDirBackupFiles + @fname



--Get database name from RESTORE HEADERONLY, assumes there's

---only one backup on each backup file.

TRUNCATE TABLE #bdev

INSERT #bdev

EXEC('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')

SET @DbName = (SELECT DatabaseName FROM #bdev)



--Construct the beginning for the RESTORE DATABASE command

SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' +

@dirfile + ''' WITH MOVE '



--Get information about database files from backup device into temp table

TRUNCATE TABLE #dbfiles

INSERT #dbfiles

EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')



OPEN dbfiles

FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type

--For each database file that the database uses

WHILE @@FETCH_STATUS = 0

BEGIN

IF @type = 'D'

SET @sql = @sql + '''' + @LogicalName + ''' TO ''' +

@DestDirDbFiles + @LogicalName  + '.mdf'', MOVE '

ELSE IF @type = 'L'

SET @sql = @sql + '''' + @LogicalName + ''' TO ''' +

@DestDirLogFiles + @LogicalName  + '.ldf'''

FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type

END



--Here's the actual RESTORE command

PRINT @sql

--Remove the comment below if you want the procedure to

---actually execute the restore command.

--EXEC(@sql)

CLOSE dbfiles

FETCH NEXT FROM files INTO @fname

END

CLOSE files

DEALLOCATE dbfiles

DEALLOCATE files

Five backup files with .bak resides in c:\backup folder
test.bak
test2.bak
test3.bak

I want to use the restore all datbases to restore all files to c:\data,c:\log

please help me write restore sripts that will loop over this to restore

With whose credentials foes ms SQL server is running, local/network account)
Run the exec command to get file listing in 'c:\backup'
Are the backups od distinct DBS
The headersonly attempts, possibly the script defining B dev does not match the headersonlly
Run the command to verify your #bdev definition does not have too many columns compared to what this server returns from the query.

 RESTORE HEADERONLY FROM DISK = 'C:\backup\test.bak'
The script is from 2004 and presumably for pre SQL 2005 versions.
You have to adapt the #bdev temp table to match the new output.....

thanks there are 2 files in my c:\backup folder

test.bak
test2.bak

I want to be able to use the sp's to restore these 2 back file in c:\backup folder
please amend the script to allow me to carry these restore

restore to c:\ data, c:\log

much apprecaited

I understand, first thing is to unwind the script you already have to determine why it is failing.
The errors point to the process that the script uses to identify the databasename that is in each backup file.
On that attempt it tries to insert the response from the restore headersonly (meaning get info about the db that was backed up) since the insert does not specify columns of interest the definition of #bdev is enforced.

The restore heathersonly is informational.
Run the command and compared the number/names of columns returned to the create table #bdev......
Once this step is corrected, the script you have might work as intended. If there is a different error on the restore files attempt, that gas to be dealt with ....

If the database already exist, the restore will fail unless another option is used to overwrite the existing data..........

Please tell me the exact command to run to compare

name, fileid, filename, filegroup, size, maxsize ,growth, usage

the above is the column on the database when I use sp_helpfile on the database

could you please update the script for me to conform to the request

I included the command to run that deals with the error.
restore headeronly from disk='c:\backup\test.bak'

To unravel what is wrong, read the error and follow..
To resolve....
In the current the response from the restore does not match the #bdev table definition.
Which SQL server are you on.
Once you go through this process, you'll be able to in the future modify this or other scripts to achieve your goals.

restore headeronly from disk='c:\backup\test.bak'
BackupName      BackupDescription      BackupType      ExpirationDate      Compressed      Position      DeviceType      UserName      ServerName      DatabaseName      DatabaseVersion      DatabaseCreationDate      BackupSize      FirstLSN      LastLSN      CheckpointLSN      DatabaseBackupLSN      BackupStartDate      BackupFinishDate      SortOrder      CodePage      UnicodeLocaleId      UnicodeComparisonStyle      CompatibilityLevel      SoftwareVendorId      SoftwareVersionMajor      SoftwareVersionMinor      SoftwareVersionBuild      MachineName      Flags      BindingID      RecoveryForkID      Collation      FamilyGUID      HasBulkLoggedData      IsSnapshot      IsReadOnly      IsSingleUser      HasBackupChecksums      IsDamaged      BeginsLogChain      HasIncompleteMetaData      IsForceOffline      IsCopyOnly      FirstRecoveryForkID      ForkPointLSN      RecoveryModel      DifferentialBaseLSN      DifferentialBaseGUID      BackupTypeDescription      BackupSetGUID      CompressedBackupSize      Containment
TEST5-Full Database Backup      NULL      1      NULL      0      1      2      DESKTOP-8TR11K5\RSOETAN      DESKTOP-8TR11K5      TEST      706      2017-07-15 22:58:50.000      2576384      33000000011200064      33000000016000001      33000000011200064      0      2017-07-15 22:59:02.000      2017-07-15 22:59:03.000      0      0      1033      196609      110      4608      11      0      2100      DESKTOP-8TR11K5      512      ACB5CC2B-BA6B-4FB7-A984-EB94F5963A94      7BA95DB9-AFFF-4F4B-A507-F82E74321E0E      Latin1_General_CI_AS      7BA95DB9-AFFF-4F4B-A507-F82E74321E0E      0      0      0      0      0      0      0      0      0      0      7BA95DB9-AFFF-4F4B-A507-F82E74321E0E      NULL      FULL      NULL      NULL      Database      1D6F461F-83AF-4A63-A191-55D2E35656E1      2576384      0

PLease find the attached after running the restore headonly, please update this script for me to match up , new on this ...much apprecated

Add a column, Containment of Type tinyint the definition of table #bdev last column.

 See what happens once this change is done.

Select @@version

You are using SQL server 2012 or newer?


https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql

Includes info/explanation of the #bdev table definition.

Thanks the below was generated : but error with Subquery as seen below:

how do I resolve this  subquery error and also make sure it 's restore as no recovery mode

RESTORE DATABASE TES9 FROM DISK = 'C:\Backup\TES9.bak' WITH MOVE 'TES9' TO 'C:\data\TES9.mdf', MOVE 'TES9_log' TO 'C:\Data\TES9_log.ldf'
Msg 512, Level 16, State 1, Procedure sp_RestoreFromAllFilesInDirectory, Line 131
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
RESTORE DATABASE TES9 FROM DISK = 'C:\Backup\TEST.bak' WITH MOVE 'TEST' TO 'C:\data\TEST.mdf', MOVE 'TEST_log' TO 'C:\Data\TEST_log.ldf'
RESTORE DATABASE TEST2 FROM DISK = 'C:\Backup\TEST2.bak' WITH MOVE 'TEST2' TO 'C:\data\TEST2.mdf', MOVE 'TEST2_log' TO 'C:\Data\TEST2_log.ldf'
RESTORE DATABASE TEST5 FROM DISK = 'C:\Backup\TEST5.bak' WITH MOVE 'TEST5' TO 'C:\data\TEST5.mdf', MOVE 'TEST5_log' TO 'C:\Data\TEST5_log.ldf'
RESTORE DATABASE TEST7 FROM DISK = 'C:\Backup\TEST7.bak' WITH MOVE 'TEST7' TO 'C:\data\TEST7.mdf', MOVE 'TEST7_log' TO 'C:\Data\TEST7_log.ldf'

You only mentioned two files in the location, to test out, limit to attempting a restore of a single bakup file.
This is the command to executed, to see what the results are.
RESTORE DATABASE TES9 FROM DISK = 'C:\Backup\TES9.bak' WITH MOVE 'TES9' TO 'C:\data\TES9.mdf', MOVE 'TES9_log' TO 'C:\Data\TES9_log.ldf'

if you get a similar error, add ,stats=10 to the end of the above line. If that works.
locate Print(@sql) and add above this line
Set @sql=@sql +', STATS = 10;'


Try with only a single DB backup file....

In short, the execution of the command returns more information than expected.

The link provided earlier includes the various restore examples and explanations.

thanks no more errors which part of the code to change to restore as no recovery


RESTORE DATABASE Esther FROM DISK = 'C:\Backup\Esther.bak' WITH MOVE 'Esther' TO 'C:\data\Esther.mdf', MOVE 'Esther_log' TO 'C:\Data\Esther_log.ldf'
RESTORE DATABASE John FROM DISK = 'C:\Backup\John.bak' WITH MOVE 'John' TO 'C:\data\John.mdf', MOVE 'John_log' TO 'C:\Data\John_log.ldf'
RESTORE DATABASE Niyi FROM DISK = 'C:\Backup\Niyi.bak' WITH MOVE 'Niyi' TO 'C:\data\Niyi.mdf', MOVE 'Niyi_log' TO 'C:\Data\Niyi_log.ldf'
RESTORE DATABASE Rich FROM DISK = 'C:\Backup\Rich.bak' WITH MOVE 'Rich' TO 'C:\data\Rich.mdf', MOVE 'Rich_log' TO 'C:\Data\Rich_log.ldf'

Add norecovery after the first with (between with and move) in the @sql variable build.

don't understand , kindly copy that bit that need adding norecovery in your reply and add the norecovery to that bit please

Find the comment "RESTORE DATABASE command"

Set @sql='restore database from disk ..... ' +
@dirfile + 'with move'
Change the with move to with norecovery move

Your paste does not make it copyable on the platform I am using to respond.

still getting below error message


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'move'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'move'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'move'.


--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE ' + @DbName + ' FROM DISK = ''' +
@dirfile + ''' with norecovery move '

RESTORE DATABASE Esther FROM DISK = 'C:\Backup\Esther.bak' with norecovery move'Esther' TO 'C:\data\Esther.mdf', MOVE 'Esther_log' TO 'C:\Data\Esther_log.ldf'
RESTORE DATABASE John FROM DISK = 'C:\Backup\John.bak' with norecovery move'John' TO 'C:\data\John.mdf', MOVE 'John_log' TO 'C:\Data\John_log.ldf'
RESTORE DATABASE Niyi FROM DISK = 'C:\Backup\Niyi.bak' with norecovery move'Niyi' TO 'C:\data\Niyi.mdf', MOVE 'Niyi_log' TO 'C:\Data\Niyi_log.ldf'
RESTORE DATABASE Rich FROM DISK = 'C:\Backup\Rich.bak' with norecovery move'Rich' TO 'C:\data\Rich.mdf', MOVE 'Rich_log' TO 'C:\Data\Rich_log.ldf'

Well deserved the point