January 18, 2008 at 12:36 pm
Hi,
I'm a web developer and defacto DBA for a small marketing company.
So I get a BAK file from a client to restore to my Sql Server 2000 database.
First problem was I didn't know the names of any files/filegroups in the backup.
After I found FILELISTONLY, I came up with the code below:
EDIT: Had wrong code in here:
-- OLD: RESTORE LOG my_new_database
RESTORE DATABASE my_new_database
FILEGROUP = 'PRIMARY'
FROM
DISK = 'C:\My Projects\ACTIVE\CLIENT\imglib.bak'
WITH
MOVE 'client_imagelibrary_Data' TO 'C:\My Projects\ACTIVE\client_imagelibrary_Data.mdf',
MOVE 'client_imagelibrary_Log' TO 'C:\My Projects\ACTIVE\client_imagelibrary_Log.ldf'
And it works......sorta. The MDF file is restored to the specified directory
* EDIT: "but there's no sign of the LDF file, and I keep getting an error when I try to restore the LOG file:
RESTORE LOG my_new_database
FILEGROUP = 'PRIMARY'
FROM
DISK = 'C:\My Projects\ACTIVE\CLIENT\imglib.bak'
Error: Line 2: Incorrect syntax near 'FILEGROUP'.
If I remove the FILEGROUP line, I get:
The backup set in file 'C:\My Projects\ACTIVE\CLIENT\imglib.bak' was created by BACKUP DATABASE...FILE= and cannot be used for this restore operation.
FILELIST doesn't list a LOG file in the backup.
It's supposedly a full backup of the database, and I've been running a full restore.
Enterprise Manager constantly says the database is LOADING, but never progresses beyond that.
What am I doing wrong?
January 19, 2008 at 5:50 pm
Before applying the last log file, we need to have the clause, WITH NORECOVERY clause.
RESTORE LOG MyNwind FROM MyNwind_log1 WITH NORECOVERY
While applying the last log, we may have,
RESTORE LOG MyNwind FROM MyNwind_log3 WITH RECOVERY
But this time, the clause, WITH RECOVERY, is optional.
Hopefully, it helps.
January 20, 2008 at 9:18 pm
Hi, and thanks for replying
I won't be able to try this until Tuesday,
and the client has already agreed to send us the original LDF/MDF files,
but I wanted to start talking about it anyway.
Where did the 'MyNwind_log1' file come from?
Was it stored within the BAK file?
Or was a new log generated with the RESTORE DATABASE command?
Running FILELIST on the BAK file did not show the name of a log file.
Also, I kept an eye on all involved directories, and didn't see a new LDF file being generated.
January 20, 2008 at 11:40 pm
With restores like that I usually cheat and use the GUI tools. They show you the list of backups within the backup file (as you can have >1 in the one file). You can tell it easily where to put the files and, since you know how many backups there are and of what type you can easily know whether or not to use NORECOVERY or not.
Why make life difficult? 🙂
Of course, if it was your own data and not a one-off thing you should create some sort of restore script so that during disaster recovery (or testing disaster recovery!) you have set code that you run so nothing's overlooked. 😀
January 22, 2008 at 5:35 am
I think SQL believes you are trying to do a partial restore.
Take out the line filegroup = 'primary' from your code and it should work fine
---------------------------------------------------------------------
January 22, 2008 at 9:31 am
SQL ORACLE:
Still haven't tried your idea yet (it'll probably be this afternoon), but I'm unsure as to where you got the log file from.
Ian:
Oh yeah, easier is definitely better in this case.
Enterprise Manager was the first thing I tried.
I used:
- Restore Backup Set
-- Database Complete
I also checked under "Options".
Under logical file name, I only saw the MDF file listed.
Tried it again anyway, and got this error:
"The backup set in file 'C:\My Projects\ACTIVE\CLIENT\imglib.bak' was created by BACKUP DATABASE...FILE= and cannot be used for this restore operation."
George:
Same thing. If I remove the "FILEGROUP" line, I get that same error:
"The backup set in file 'C:\My Projects\ACTIVE\CLIENT\imglib.bak' was created by BACKUP DATABASE...FILE= and cannot be used for this restore operation."
I'm leaning towards the idea that the transaction log needs to be included with the BAK file in order to do a restore.
Looks like I'm getting some education in DB adminning 🙂
January 22, 2008 at 10:08 am
you are right, you have been sent a partial backup of the .mdf file only. to restore you also need a backup of the log file.
I would get back to whoever sent it to you and tell them you want a FULL database backup
backup database dbname to disk = ' filename'
---------------------------------------------------------------------
January 22, 2008 at 11:23 am
That was a filegroup backup, restore it using a filegroup restore. They sent you that part, probably because the data you need is all in that filegroup. If you need the changes after that backup was created, you'll need to restore the database with norecovery option (that's why the "loading" status), and you need the tlog backups. Otherwise, you can just restore the filegroup with recovery and you're set,.no need for the tlog backups.
_____________
Donn Policarpio
January 23, 2008 at 3:01 pm
Don:
I tried your idea of using a filegroup restore with RECOVERY with the code below:
RESTORE DATABASE client_imagelibrary_Data
FILEGROUP = 'PRIMARY'
FROM
DISK = 'C:\My Projects\ACTIVE\client\imglib.bak'
WITH
RECOVERY
And got:
"One or more of the options (recovery) are not supported for this statement."
I also found an MS support page here that supports George's statement:
http://support.microsoft.com/kb/281122
Seems as if you need not just the transaction logs, but a copy of the full database as well, if you're trying to restore from filegroups. Without a full copy, you'll need to restore from backups of every filegroup in the DB.
In case you can't tell, this problem really ticked me off and I had to get it nailed down.
I'll still be checking this thread for replies.
Thanks everyone
January 24, 2008 at 3:39 am
Ray, basiscally you are correct.
You will never be able to recover (as oppose to restore) your databse because it is a FILE backup from a different database, so there is no way it can be brought to a consistent state without a log backup as well. The restore from file with recovery is for restoring back to the same database.
From the previous posts it would seem the backup was with the FILE option rather than filegroup so there is only one file in this filegroup and it is the primary filegroup, in fact it looks like a standard database with one .mdf file and one log (.ldf) file, so I cannot see why a fILE backup was sent.
Only two reasons you might do this, a VLDB with many files, or information which needs to be kept securely seperate in the files, either of these two scenarios fit?
I would definitely ask your client (politely) to stop faffing about and send a FULL backup. too much time has passed to send a corresponding log only backup now.
The full backup will be the same size as the file backup if the database consists of only one datafile.
regards
george
---------------------------------------------------------------------
January 24, 2008 at 8:34 am
Man, can't believe I said to recover without tlog, that completely won't happen! I stand to be corrected..
Tho it's true that filegroup/file backup on occassion gets implemented on vldbs, it is also one way of restoring the data you only need over an existing db.
I completely agree with george, you should have the log backups by this time. 😛
_____________
Donn Policarpio
January 29, 2008 at 8:31 pm
Ray:
If you have full backup of a database, the SQL Server system may create a log file for you.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply