Bugriy
Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
econ2
Цитата:
-- список полей для 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)
|
в 2000м SQL нет полей начиная с FamilyGUID uniqueidentifier.
Ошибка теперь такая:
Analitica.bak
Analitica
G:\DATA\Analitica.mdf
H:\LOGS\Analitica.ldf
Server: Msg 5184, Level 16, State 2, Line 85
Cannot use file 'f:\mssqldata\Analitica.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Server: Msg 3156, Level 16, State 1, Line 85
File 'Analitica_Data' cannot be restored to 'f:\mssqldata\Analitica.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 85
RESTORE DATABASE is terminating abnormally.
f:\mssqldata\ - это путь, по которому лежали базы на старом сервере, на новом - это кворумный диск, к нему нет доступа у SQL.
Какая-то путаница с путями выходит.
Вот этот кусок не правильный:
select @Data = LogicalName from #backup
select @DataFile = @NewDatabaseMDFPath+'\'+@NewDatabaseName+'.mdf'
select @Log = LogicalName from #backup
select @LogFile = @NewDatabaseLDFPath+'\'+@NewDatabaseName+'.ldf'
У меня не совпадают имена лога и базы, а тут они одному значению приравниваются. @DATA<>@LOG
Добавлено:
Исправленный скрипт:
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 = 'G:\SQLBackUp',
@NewDatabaseMDFPath = 'G:\DATA',
@NewDatabaseLDFPath = 'H:\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
(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))
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)
insert into #backup
exec('RESTORE FILELISTONLY FROM DISK = '''+@BackupFile+''' WITH FILE = 1 , NOUNLOAD')
--SELECT * FROM #backup
--имя файла составляется из пути + имя базы
select @Data = LogicalName from #backup WHERE type = 'D'
select @DataFile = @NewDatabaseMDFPath+'\'+@NewDatabaseName+'.mdf'
select @Log = LogicalName from #backup WHERE type = 'L'
select @LogFile = @NewDatabaseLDFPath+'\'+@NewDatabaseName+'.ldf'
print @NewDataBaseName
print @BackupFile
print @Data
print @DataFile
print @Log
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 |
Всего записей: 226 | Зарегистр. 24-08-2005 | Отправлено: 11:54 24-07-2007 | Исправлено: Bugriy, 13:25 24-07-2007 |
|
