drop table #tblCmd
create Table #tblCmd(cmd varchar(1000))
declare @tblNames Table(dbname varchar(100))
insert @tblNames(dbname) values ('Berprogramme')
insert @tblNames(dbname) values ('BU')
insert @tblNames(dbname) values ('CSAngebot')
insert @tblNames(dbname) values ('Doc2CADIM')
insert @tblNames(dbname) values ('EN_CALC_DATA')
insert @tblNames(dbname) values ('Fehlermeldungen')
insert @tblNames(dbname) values ('Hardwaredatenbank')
insert @tblNames(dbname) values ('Installationsdatenbank')
insert @tblNames(dbname) values ('IPC_40')
insert @tblNames(dbname) values ('IPC30')
insert @tblNames(dbname) values ('IPC40')
insert @tblNames(dbname) values ('KAPA')
insert @tblNames(dbname) values ('KM3')
insert @tblNames(dbname) values ('KMG')
insert @tblNames(dbname) values ('Laeufer_2')
insert @tblNames(dbname) values ('Laeufer_3')
insert @tblNames(dbname) values ('Laeufer')
insert @tblNames(dbname) values ('Laeufer_SEDL')
insert @tblNames(dbname) values ('Laeufer2')
insert @tblNames(dbname) values ('Masbe')
insert @tblNames(dbname) values ('MKS_DB')
insert @tblNames(dbname) values ('ML2')
insert @tblNames(dbname) values ('MWDB')
insert @tblNames(dbname) values ('NLO')
insert @tblNames(dbname) values ('Nuttenschnitte')
insert @tblNames(dbname) values ('PCS')
insert @tblNames(dbname) values ('Pruefunterlagen')
insert @tblNames(dbname) values ('Q_DATEN')
insert @tblNames(dbname) values ('Rohrstecksystem')
insert @tblNames(dbname) values ('Schleich_MTC3')
insert @tblNames(dbname) values ('Serverdatenbank')
insert @tblNames(dbname) values ('SICAP')
insert @tblNames(dbname) values ('SICAP_RCP')
insert @tblNames(dbname) values ('Sich')
insert @tblNames(dbname) values ('SiCool')
insert @tblNames(dbname) values ('SIMATIC')
insert @tblNames(dbname) values ('SiMoDat')
insert @tblNames(dbname) values ('SiMoDat_Neu')
insert @tblNames(dbname) values ('SLS')
insert @tblNames(dbname) values ('SMART')
insert @tblNames(dbname) values ('Softwaredaten')
insert @tblNames(dbname) values ('Spezialwerkzeuge')
insert @tblNames(dbname) values ('SSDDaten')
insert @tblNames(dbname) values ('Tangensdelta')
insert @tblNames(dbname) values ('TMO')
insert @tblNames(dbname) values ('TR_ANGEBOT')
insert @tblNames(dbname) values ('Wartungsdatenbank')
insert @tblNames(dbname) values ('Werkverkehr')
--
DECLARE @cmd nvarchar(1000)
declare @help varchar(1000)
declare @Folder varchar(100)
declare @FileName varchar(150)
DECLARE @FilesInAFolder TABLE (FileNamesWithFolder VARCHAR(500))
declare @bkup_filelist Table(
LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,FileType char(1)
,FileGroupName nvarchar(128)
,FileSize numeric(20,0)
,FileMaxSize numeric(20,0)
,FileID bigint
,CreateLSN numeric(25,0)
,DropLSN numeric(25,0) NULL
,UniqueID uniqueidentifier
,ReadOnlyLSN numeric(25,0)
,ReadWriteLSN numeric(25,0)
,BackupSizeInBytes bigint
,SourceBlockSize int
,FileGroupID int
,LogGroupGUID uniqueidentifier NULL
,DifferentialBaseLSN numeric(25,0)
,DifferentialbaseGUID uniqueidentifier
,IsReadOnly bit
,IsPresent bit
)
declare @LogicalName varchar(200)
declare @PhysicalName varchar(200)
declare @FileType varchar(1)
declare @dbName varchar(100)
set @Folder = 'M:\Backup\SQL_backup\'
set @cmd = 'dir M:\Backup\SQL_backup\*.bak /b /s'
INSERT INTO @FilesInAFolder
EXEC MASTER..xp_cmdshell @cmd;
declare outCur cursor for
select dbname from @tblNames
open outCur
fetch next from outCur into @dbName
while @@Fetch_Status = 0
begin
declare myCur cursor for
select FileNamesWithFolder from @FilesInAFolder
open myCur
fetch next from myCur into @FileName
while @@Fetch_Status = 0
begin
delete @bkup_filelist
set @cmd = 'RESTORE FILELISTONLY FROM DISK = ''' + @FileName + ''''
INSERT INTO @bkup_filelist
( LogicalName, PhysicalName, FileType, FileGroupName, FileSize, FileMaxSize, FileID
,CreateLSN, DropLSN, UniqueID, ReadOnlyLSN, ReadWriteLSN
,BackupSizeInBytes, SourceBlockSize, FileGroupID, LogGroupGUID
,DifferentialBaseLSN, DifferentialbaseGUID, IsReadOnly, IsPresent )
EXEC (@cmd)
declare inCur cursor for
select LogicalName, PhysicalName, FileType from @bkup_filelist
open inCur
fetch next from inCur into @LogicalName, @PhysicalName, @FileType
while @@Fetch_Status = 0
begin
if @FileType = 'D'
if RIGHT(@PhysicalName,3)='mdf'
begin
set @cmd = 'Restore database ' + @dbName + ' from disk = ''''' + @FileName +''''''
set @cmd = @cmd + ' with move ''''' + @LogicalName + '''''' + ' to ''''' + 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\' + @LogicalName + '.mdf'''','
end
else
begin
set @cmd = @cmd + ' move ''''' + @LogicalName + '''''' + ' to ''''' + 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\' + @LogicalName + '.ndf'''','
end
if @FileType = 'L'
set @cmd = @cmd + ' move ''''' + @LogicalName + '''''' + ' to ''''' + 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\tp\' + @LogicalName + '.ldf'''''
fetch next from inCur into @LogicalName, @PhysicalName, @FileType
end
set @help='insert into #tblCmd (cmd) values ('''+ @cmd + ''')'
exec (@help)
close inCur
deallocate inCur
fetch next from myCur into @FileName
end
close myCur;
deallocate myCur;
fetch next from outCur into @dbName
end
close outCur
deallocate outCur
select * from #tblCmd
Hallo Forum,
ich komme mit verschachtelten Cursors nicht klar.
Die Idee:
1. ich erstelle eine Tabelle mit Datenbanken, die ich wiederherstellen möchte (@tblNames)
2. Aus einem Verzeichnis lese ich alle entsprechenden Backupdateien-Namen ein (@FilesInAFolder)
3. Mit "restore filelistonly..." lese ich alle logischen/physikalischen Dateien einer jeweiligen DB ein(@bkup_filelist)
4. ich erstelle ein String mit "restore database..." und kopiere es in die Tabelle (#tblCmd).
5. ich würde dann die Tabelle #tblCmd abarbeiten und alle Restores machen (ich will zuerst alle Restore-Strings in der Tabele haben, um zu überprüfen, ob der physikalische DB-Name mit den Backupdateien übereinstimmt).
Und hier liegt der Hund begraben, anstatt in der #tblCmd zu haben:
"Restore
database Berprogramme
from
disk
=
'M:\Backup\SQL_backup\Berprogramme_db_201205172230.BAK'
with
move
'Berprogramme_Data'
to
'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\Berprogramme_Data.mdf',
move
'Berprogramme_Log'
to
'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\tp\Berprogramme_Log.ldf'"
(es gibt 48 DB zu wiederhestellen)
bekomme ich 2304 Einträge, wo pro DB-Namen alle Restore-Befehle vorkommen, z. B.
"Restore database Berprogramme from disk = 'M:\Backup\SQL_backup\Berprogramme_db_201205172230.BAK' with move 'Berprogramme_dat' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\Berprogramme_dat.mdf', move 'Berprogramme_1_Data' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\Berprogramme_1_Data.ndf',
move 'Berprogramme_log' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\tp\Berprogramme_log.ldf'"
"Restore database Berprogramme from disk = 'M:\Backup\SQL_backup\BU_db_201205172230.BAK' with move 'BU_Data' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\BU_Data.mdf', move 'BU_Log' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\tp\BU_Log.ldf'
Restore database Berprogramme from disk = 'M:\Backup\SQL_backup\CSAngebot_db_201205172230.BAK' with move 'CSAngebot_Data' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\CSAngebot_Data.mdf', move 'CSAngebot_Log' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\tp\CSAngebot_Log.ldf'"
"Restore database Berprogramme from disk = 'M:\Backup\SQL_backup\Doc2CADIM_db_201205172230.BAK' with move 'Doc2CADIM_Data' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\Doc2CADIM_Data.mdf', move 'Doc2CADIM_1Data' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\Doc2CADIM_1Data.ndf',
move 'Doc2CADIM_Log' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\tp\Doc2CADIM_Log.ldf'"
"Restore database Berprogramme from disk = 'M:\Backup\SQL_backup\EN_CALC_DATA_db_201205172230.BAK' with move 'EN_CALC_DATA_Data' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\EN_CALC_DATA_Data.mdf', move 'EN_CALC_DATA_Log' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\tp\EN_CALC_DATA_Log.ldf'"
"Restore database Berprogramme from disk = 'M:\Backup\SQL_backup\Fehlermeldungen_db_201205172230.BAK' with move 'Fehlermeldungen_dat' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\Data\Fehlermeldungen_dat.mdf', move 'Fehlermeldungen_log' to 'M:\MSSQL_MSSQLSERVER\MSSQL.1\MSSQL\tp\Fehlermeldungen_log.ldf'"
Es ist klar, dass das Problem in der Cursor-Verschachtelung liegt, ich finde es aber nicht.
Der T-SQL Code ist oben.
Vielen Dank für Eure Hilfe
P.
