June 24, 2004 at 8:58 am
I'm trying to restore my db (using db's backup file) over
my existing db but I can't.
When I use the RECOVERY Option to Restore my db, an error
occured and ouput msg is:
Server: Msg 3032, Level 16, State 2, Line 38
One or more of the options (recovery) are not supported
for this statement. Review the documentation for
supported options.
But this option is supported from RESTORE DATABASE
procedure.
When I don't use the RECOVERY OPTION the output msg is:
Processed 1176 pages for database 'MYDB',
file 'TEST_Data' on file 1.
RESTORE DATABASE successfully processed 1176 pages in
0.765 seconds (12.593 MB/sec).
Then My db marked as "Loading" but never finishes.
If I restore my old db Using Enterpise Manager (with
force existsing...) it works.
The statement that profiler shows is:
RESTORE DATABASE [MYDB] FROM DISK = N'D:\Microsoft
SQL Server\MSSQL\BACKUP\BACKUP MYDB\FULL\MYDB_2004-
05-12_22-00-01.bak' WITH FILE = 1, NOUNLOAD , STATS =
10, RECOVERY , REPLACE
I just need to restore my old db over my existing. What
do I do wrong?
Script:
Declare @FilePosition as int
Declare @DB as nvarchar(100)
Declare @DBDataFile as nvarchar(100)
Declare @SQLStr as nvarchar(4000)
SET @DB = 'MYDB'
Set @SQLStr = 'Select @DataFile = name from ' + @DB
+ '..sysfiles Where groupid = 1'
exec sp_executesql @SQLStr, N'@DataFile nvarchar(100)
Output', @DBDataFile Output
SELECT @FilePosition = position
FROM msdb..backupset
WHERE database_name = @DB
AND type != 'F'
AND backup_set_id = (SELECT MAX
(backup_set_id)
FROM msdb..backupset
WHERE database_name
= @DB)
RESTORE DATABASE @DB
FILE = @DBDataFile
FROM DISK = 'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
MYDB\FULL\MYDB_2004-05-12_22-00-01.bak'
WITH FILE = @FilePosition, /*RECOVERY, */ REPLACE
--Thanks in advance
June 24, 2004 at 9:48 am
Can you verify the file you are using is the right file ?
Also just try the following
restore database database_name from disk = '' with replace
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply