SQL Server Performance Forum – Threads Archive

Need help with Restore using file and filegroup

I made a full backup of my database using:
BACKUP DATABASE dbname
FILE = ‘primary_data_file’,
FILEGROUP = ‘PRIMARY’,
FILE = ‘secondary_data_file’,
FILEGROUP = ‘FG1’
TO backup_device
WITH INIT I then deleted 10 records from a table which is stored on the "secondary_data_file" on filegroup "FG1". I ran RESTORE HEADERONLY FROM backup_device and my backup had Position = 1. I then attemped to restore the database in anticipation of recovering the 10 deleted records. I used the following restore statement:
RESTORE DATABASE dbname
FILE = ‘primary_data_file’,
FILEGROUP = ‘PRIMARY’,
FILE = ‘secondary_data_file’,
FILEGROUP = ‘FG1’
FROM backup_device
WITH FILE = 1 A message returned saying:
The tail of the log for database ‘dbname’ has not been backed up.
Back up the log and rerun the RESTORE statement specifying the FILE clause. I proceeded to backup the log using the following statement:
BACKUP LOG dbname
TO backup_device
WITH NO_TRUNCATE I ran RESTORE HEADERONLY FROM backup_device and my full backup had Position = 1, log backup had Position = 2. Following the backup of the log I ran the following restore statement with the Recovery option specified:
RESTORE DATABASE dbname
FILE = ‘primary_data_file’,
FILEGROUP = ‘PRIMARY’,
FILE = ‘secondary_data_file’,
FILEGROUP = ‘FG1’
FROM backup_device
WITH RECOVERY, FILE = 1 The following error returned:
Server: Msg 3032, Level 16, State 2, Line 1
One or more of the options (recovery) are not supported for this statement. Review the documentation for supported options. I then ran the following restore statement:
RESTORE DATABASE dbname
FILE = ‘primary_data_file’,
FILEGROUP = ‘PRIMARY’,
FILE = ‘secondary_data_file’,
FILEGROUP = ‘FG1’
FROM backup_device
WITH FILE = 1 The database successfully restored, but the database remained in a "Loading" state.
I proceeded to restore the log KNOWING that the 10 deleted records would not be recovered.
RESTORE LOG dbname
FROM backup_device
WITH FILE = 2 How do I run the following restore statement so the database is fully recovered with the 10 deleted records and is not in a "Loading" state?
RESTORE DATABASE dbname
FILE = ‘primary_data_file’,
FILEGROUP = ‘PRIMARY’,
FILE = ‘secondary_data_file’,
FILEGROUP = ‘FG1’
FROM backup_device
WITH FILE = 1

I have the same problem. Please help!

Aftet the restore is completed you can run
RESTORE DATABASE dbname WITH RECOVERY
that will keep database out of loading state. The linkhttp://www.developersdex.com/sql/message.asp?p=580&r=4302501 for your information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>