use master
set nocount on
declare @FileName varchar(255),
@BackupPath varchar(256),
@BackupFile varchar(256),
@NewDatabaseMDFPath varchar(256),
@NewDatabaseLDFPath varchar(256),
@NewDatabaseName varchar(256),
@Log varchar(128),
@Data varchar(128),
@LogFile varchar(256),
@DataFile varchar(256)
select @BackupPath = 'C:\MSSQL\Backup\Test',
@NewDatabaseMDFPath = 'C:\MSSQL\Data',
@NewDatabaseLDFPath = 'C:\MSSQL\Data\Logs'
if object_id('tempdb..#Dir') is not null drop table #Dir
-- для работы в 2005 - запуск xp_cmdshell нужно сначала разрешить!!!
create table #Dir (Output varchar(255) null)
-- Бакапы должны иметь расширение .bak!!!
insert into #Dir
exec('xp_cmdshell ''dir '+@BackupPath+'\*.bak /B''')
delete from #Dir where output not like '%.bak' or output is null
-- теперь в #Dir чистый список бакапов
-- select * from #Dir
DECLARE Files_Cursor CURSOR FORWARD_ONLY FOR
SELECT Output FROM #Dir
OPEN Files_Cursor
FETCH NEXT FROM Files_Cursor
INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
print @FileName
--тут лежит бакап
select @BackupFile = @BackupPath+'\'+@FileName
if not object_id('tempdb..#backup') is null drop table #backup
if not object_id('tempdb..#backupheader') is null drop table #backupheader
create table #backupheader
-- список полей для SQL 2000 смотри в Books Online
-- mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ra-rz_5urd.htm
--список полей для SQL 2005
(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 bit, IsSnapshot bit,
IsReadOnly bit, IsSingleUser bit, HasBackupChecksums bit, IsDamaged bit, BeginsLogChain bit,
HasIncompleteMetaData bit, IsForceOffline bit, IsCopyOnly bit, FirstRecoveryForkID uniqueidentifier,
ForkPointLSN numeric(25,0) NULL, RecoveryModel nvarchar(60), DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier, BackupTypeDescription nvarchar(60), BackupSetGUID uniqueidentifier NULL)
insert into #backupheader
exec('RESTORE HEADERONLY FROM DISK = N'''+@BackupFile+''' WITH FILE = 1 , NOUNLOAD')
--получаем имя базы
select @NewDataBaseName = DatabaseName from #backupheader
-- получаем имена Data и Log дивайсов
create table #backup
-- Список полей для SQL 2000
-- (logicalname varchar(50) null, physicalname varchar(50) null, type char(5) null, filegroupname varchar(50) null, size float null, maxsize float null)
--список полей для SQL 2005
(LogicalName nvarchar(128), PhysicalName nvarchar(260), type char(5) null, filegroupname nvarchar(128) null,
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)
insert into #backup
exec('RESTORE FILELISTONLY FROM DISK = '''+@BackupFile+''' WITH FILE = 1 , NOUNLOAD')
--имя файла составляется из пути + имя базы
select @Data = LogicalName from #backup
select @DataFile = @NewDatabaseMDFPath+'\'+@NewDatabaseName+'.mdf'
select @Log = LogicalName from #backup
select @LogFile = @NewDatabaseLDFPath+'\'+@NewDatabaseName+'.ldf'
print @NewDataBaseName
print @DataFile
print @LogFile
RESTORE DATABASE @NewDataBaseName FROM DISK = @BackupFile WITH FILE=1, NOUNLOAD, STATS=100, RECOVERY, REPLACE,
MOVE @Data TO @DataFile,
MOVE @Log TO @LogFile
FETCH NEXT FROM Files_Cursor
INTO @FileName
END
CLOSE Files_Cursor
DEALLOCATE Files_Cursor |
