June 21, 2010 at 7:35 am
Hello all,
Hope you can help me with this one. I have a reasonably large backup file (about 15gb) from which I need to retore a single database to a different server. There are maybe 15 different databases within this backup file.
I check out the headers, and I can see the 15 different databases.
This part may be inexperience talking, but I'm describing how it looks to me. I check out the file list, and there are only two files: master and master_log. MASTER is not the database I want to restore.
Can someone please tell me how I can do this? What am I missing here?
Thank You,
Randy
Randy
June 21, 2010 at 8:00 am
Since we can't see what you see it is really hard to help you. I realize you may not be able to post the actual output, but if you could show us something that represents the problem we may be able to provide you with a much better answer rather than a shot in the dark that may be helpful or may not.
June 21, 2010 at 8:14 am
Okay, here it is - let me know if you need any more than this.
restore headeronly from disk='d:\FullBackupAllDB.BAK'
BackupName
master_backup_20100523040035
model_backup_20100523040035
msdb_backup_20100523040035
DB1_backup_20100523040035
DB2_backup_20100523040035
DB3_backup_20100523040035
DB4_backup_20100523040035
DB5_backup_20100523040035
DB6_backup_20100523040035
DB7_backup_20100523040035
DB8_backup_20100523040035
DB9_backup_20100523040035
DB10_backup_20100523040035
DB11_backup_20100523040035
(14 row(s) affected)
restore filelistonly from disk='d:\FullBackupAllDB.BAK'
LogicalName
master
mastlog
(2 row(s) affected)
Lets say I want DB9_backup_20100523040035...
Randy
June 21, 2010 at 9:29 am
Randy-574768 (6/21/2010)
Okay, here it is - let me know if you need any more than this.restore headeronly from disk='d:\FullBackupAllDB.BAK'
BackupName
master_backup_20100523040035
model_backup_20100523040035
msdb_backup_20100523040035
DB1_backup_20100523040035
DB2_backup_20100523040035
DB3_backup_20100523040035
DB4_backup_20100523040035
DB5_backup_20100523040035
DB6_backup_20100523040035
DB7_backup_20100523040035
DB8_backup_20100523040035
DB9_backup_20100523040035
DB10_backup_20100523040035
DB11_backup_20100523040035
(14 row(s) affected)
restore filelistonly from disk='d:\FullBackupAllDB.BAK'
LogicalName
master
mastlog
(2 row(s) affected)
Lets say I want DB9_backup_20100523040035...
To see the filelistonly output for DB9:
restore filelistonly from disk = N'd:\FullBackupAllDB.BAK'
with file = 12;
To restore the database:
restore database DB9
from disk = N'd:\FullBackupAllDB.BAK'
with file = 12;
June 21, 2010 at 9:33 am
You may want to familiarize yourself with Books Online. It is a good basic resource, and if you need clarification, SSC is a good place to ask such questions.
June 21, 2010 at 9:51 am
Lynn,
Thank you for the assistance! I looked everywhere, and even had FILE=11 sitting here in a query screen, but wasn't understanding that that was the answer until I read your post. I thought that was talking about restores involving more than one .bak file.
My apologies for having to ask but I do appreciate the answer.
Randy
June 21, 2010 at 9:55 am
Randy-574768 (6/21/2010)
Lynn,Thank you for the assistance! I looked everywhere, and even had FILE=11 sitting here in a query screen, but wasn't understanding that that was the answer until I read your post. I thought that was talking about restores involving more than one .bak file.
My apologies for having to ask but I do appreciate the answer.
Don't apologize. If you don't understand something, asking questions is what you need to do.
One thing I would recommend, however, is each database should be backed up to its own file. Right now, if the file containing all the backups were to get corrupted and you don't have another copy of the file you lose all the database backups in that file.
June 21, 2010 at 10:02 am
No doubt about it! This is a situation where another organization was moving their DB to our server and sent us the backup like this. I had never seen anything like it in my three years as a dba - all of mine are done as one file per database. So this was a new experience and I have definitely learned from it.
Randy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply