September 20, 2013 at 10:41 pm
Hello,
I am currently working with SQL Server 2008 R2 SP1
I am trying to find out if anyone knows whether the "FILE=" is optional in a database restore for SQL. Currently i've done online restores, offline restores, online page restores etc. I've noticed that this option usually specifies FILE=1 which i am assuming means file 1. Most of the databases i've worked with only have one filegroup.
My other assumption is that you can restore pieces of database backups such as FILEGROUP=1 FILE=2 to restore the second file in filegroup 1. this seems pretty powerful.
My next question is, if this option is left blank what should happen?
I've read the technet article found here:
http://technet.microsoft.com/en-us/library/ms186858(v=sql.105).aspx
as well as the naming conventions here:
http://technet.microsoft.com/en-us/library/ms177563(v=sql.105).aspx
I would just like to know if what im reading and what my understanding of that reading is correct. I just dont think i am understanding correctly what the definition of what is in chevron's <> means.
Thank you,
Tac
September 21, 2013 at 2:51 am
It's specifying the file within the backup, not the file or filegroup. Only necessary if you append multiple backups to the same backup file and want to restore a backup that's not the first in the backup file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2013 at 11:23 am
Hello GilaMonster,
Thank you very much for the explanation. I've seen another DBA do backups that save different files of one backup to different locations at the same time. I think that is what you're explanation is saying. Example of this would be SQLDatabaseBackup10f3.bak, SQLDatabaseBackup2of3.bak, SQLDatabaseBackup3of3.bak.
I love reading your posts, you are one of my favourite posters! Thank you for your expertise.
Tac
September 21, 2013 at 3:10 pm
No, that's the complete opposite of what I'm explaining.
The FILE = is necessary when multiple different backups have been made to the same file. Your example shows one backup to three files, that's not what I'm talking about.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2013 at 6:57 pm
Thank you for your reply!
I had never actually had more than one backup added to one file. I tested it out today and that is very neat.
I cant think of any reasons i would want to do that though.
Anyway thank you for clearing up my understanding.
Tac
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply