hfgord
شنبه 17 مرداد 1388, 10:23 صبح
با سلام
من يك اسكريپت كپي يك پايگاه داده رو از يكي از سايتهاي خارجي گرفته بودم و بر روي sql 2000 كار ميكرد. اما از موقعي كه sql 2005 رو نصب كردم ديگه اين كد درست كار نميكنه
فكر ميكنم مشكلش در قسمت RESTORE HEADERONLY FROM DISK = @BackupFile باشه
اينم اسكريپت :
Use Master
DECLARE @DB varchar(200)
SET @DB = 'Frooshs_3_87'
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'c:\backup_387.dat'
DECLARE @TestDB varchar(200)
SET @TestDB = 'Frooshs_3_88'
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'c:\Frooshs_3_88'
DECLARE @query varchar(2000)
DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '_Data.mdf'
DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + '_Log.ldf'
IF @DB IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
END
IF EXISTS(SELECT * FROM sys.databases WHERE name = @TestDB)
BEGIN
SET @query = 'DROP DATABASE ' + @TestDB
EXEC (@query)
END
RESTORE HEADERONLY FROM DISK = @BackupFile;
print @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT
print 'row count = ' + cast(@file as char(5));
DECLARE @Data varchar(500)
DECLARE @Log varchar(500)
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
type varchar(10),
FilegroupName varchar(200),
size int,
maxsize bigint
--------------------new code------------------
,FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint ,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier ,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
------------------------------------------------
)
INSERT #restoretemp EXEC (@query)
SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
PRINT @Data
PRINT @Log
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp
IF @File > 0
BEGIN
print 'yes'
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''')+
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE '+
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
EXEC (@query)
END
آيا كسي از دوستان ميتونه اين مشكل رو حل كنه؟
من يك اسكريپت كپي يك پايگاه داده رو از يكي از سايتهاي خارجي گرفته بودم و بر روي sql 2000 كار ميكرد. اما از موقعي كه sql 2005 رو نصب كردم ديگه اين كد درست كار نميكنه
فكر ميكنم مشكلش در قسمت RESTORE HEADERONLY FROM DISK = @BackupFile باشه
اينم اسكريپت :
Use Master
DECLARE @DB varchar(200)
SET @DB = 'Frooshs_3_87'
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'c:\backup_387.dat'
DECLARE @TestDB varchar(200)
SET @TestDB = 'Frooshs_3_88'
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'c:\Frooshs_3_88'
DECLARE @query varchar(2000)
DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '_Data.mdf'
DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreFile + '_Log.ldf'
IF @DB IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
END
IF EXISTS(SELECT * FROM sys.databases WHERE name = @TestDB)
BEGIN
SET @query = 'DROP DATABASE ' + @TestDB
EXEC (@query)
END
RESTORE HEADERONLY FROM DISK = @BackupFile;
print @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT
print 'row count = ' + cast(@file as char(5));
DECLARE @Data varchar(500)
DECLARE @Log varchar(500)
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
type varchar(10),
FilegroupName varchar(200),
size int,
maxsize bigint
--------------------new code------------------
,FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint ,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier ,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit
------------------------------------------------
)
INSERT #restoretemp EXEC (@query)
SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
PRINT @Data
PRINT @Log
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp
IF @File > 0
BEGIN
print 'yes'
SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''')+
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE '+
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
EXEC (@query)
END
آيا كسي از دوستان ميتونه اين مشكل رو حل كنه؟