January 18, 2011 at 2:19 pm
.ldf - tlogbackups files
.dfb - differential backup files
.bak -
.mdf -
What is the difference bewteen .bak and .mdf files?
thanks
January 18, 2011 at 2:32 pm
sqlserver12345 (1/18/2011)
.ldf - tlogbackups files.dfb - differential backup files
.bak -
.mdf -
What is the difference bewteen .bak and .mdf files?
thanks
:ermm:
ldf = log file, not backup.
bak = the backup of the database.
mdf = the main file for the database. There's also secondary files called ndf.
I'm blocked by websense here but I think this link will answer most of your questions:
http://www.file-extensions.org/microsoft-sql-server-file-extensions
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 2:47 pm
Those are all default file extensions. Since you can "misname" files, and they'll still work, you could have a data file with ".doc" instead of ".mdf", if you really want to. Not a good idea, because some poor fool will try to open your multi-terrabyte database file in MS Word, but that doesn't mean the file extensions are somehow functionally tied down.
Have you tried searching for "sql server file extension bak" in Bing/Google/whatever? You might be surprised at how easy it is to find that data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2011 at 2:49 pm
GSquared (1/18/2011)
...because some poor fool will try to open your multi-terrabyte database file in MS Word...
*sprays coke on the screen* Thanks Gus, you just lightened up my afternoon. :w00t:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 18, 2011 at 2:52 pm
Craig Farrell (1/18/2011)
GSquared (1/18/2011)
...because some poor fool will try to open your multi-terrabyte database file in MS Word...*sprays coke on the screen* Thanks Gus, you just lightened up my afternoon. :w00t:
Something I've heard should be possible and want to try someday (for fun) is to put both the mdf and the ldf into alternate streams of a simple text 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
January 18, 2011 at 2:52 pm
Craig Farrell (1/18/2011)
GSquared (1/18/2011)
...because some poor fool will try to open your multi-terrabyte database file in MS Word...*sprays coke on the screen* Thanks Gus, you just lightened up my afternoon. :w00t:
The horrifying prospect of someone misunderstanding the order to "document the database" suddenly springs to mind. (Yes, I truly am demented.)
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2011 at 3:09 pm
GilaMonster (1/18/2011)
Craig Farrell (1/18/2011)
GSquared (1/18/2011)
...because some poor fool will try to open your multi-terrabyte database file in MS Word...*sprays coke on the screen* Thanks Gus, you just lightened up my afternoon. :w00t:
Something I've heard should be possible and want to try someday (for fun) is to put both the mdf and the ldf into alternate streams of a simple text file.
What about putting a pdf/word/text document in the alternate stream of the mdf/ldf files? Kind of going off of Gus's recommendation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 18, 2011 at 6:29 pm
GSquared (1/18/2011)
The horrifying prospect of someone misunderstanding the order to "document the database" suddenly springs to mind. (Yes, I truly am demented.)You're welcome.
SOM!
That's a good one Gus! I'll have to use that at work some day soon!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 18, 2011 at 7:41 pm
Heh... possibly even more fun... just swap the extensions on the data and log file names. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 19, 2011 at 6:41 am
I've actually seen people use the data file extension for backup and vice versa. Then they complain when they can't restore because they grab the wrong one.
<headdesk>
January 19, 2011 at 9:53 am
GilaMonster (1/18/2011)
Craig Farrell (1/18/2011)
GSquared (1/18/2011)
...because some poor fool will try to open your multi-terrabyte database file in MS Word...*sprays coke on the screen* Thanks Gus, you just lightened up my afternoon. :w00t:
Something I've heard should be possible and want to try someday (for fun) is to put both the mdf and the ldf into alternate streams of a simple text file.
I like it! A multi-Gig txt file that just has "Hello" in the main data stream! Security through obscurity at its best.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 19, 2011 at 9:55 am
Brandie Tarvin (1/19/2011)
I've actually seen people use the data file extension for backup and vice versa. Then they complain when they can't restore because they grab the wrong one.<headdesk>
I keep accidentally leaving the file extensions off of ad hoc backup files. Doesn't hurt the file at all, just makes it hard to tell what it is.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 19, 2011 at 1:03 pm
.ldf - log files
.dfb-diff backup files
.mdb - master database backup...Is .mdf used only for the backup of the master database?
.bak....Does having .bak extension means it is a full back?
How do I verify if my .bak is a full back file?
My database name abc
I have a .bak file and a series of .ldf files generated during the day for every 3 hrs for this database.
Ex:
a.bak 12:00AM
b.ldf 3:00AM
c.ldf 6:00AM
d.ldf 9:00AM
e.ldf 12:00PM
f.ldf 3:00PM
g.ldf 6:00PM
h.ldf 9:00PM
i.ldf 12:00PM
I would like to use these above files to restore to a new database?abcrestore_1
How I do this?
Lets say I take a full backup for my abc database at the end of the day at 12:00PM as a .bak file and it is a single full back file - abcdefghi.bak file.
I use this abcdefghi.bak to restore to a different database as abcdefghi_2 .
I believe these databases abcrestore_1 abcrestore_2 will be the same???
January 19, 2011 at 1:22 pm
sqlserver12345 (1/19/2011)
.dfb-diff backup files
Never heard that one before
.mdb - master database backup...Is .mdf used only for the backup of the master database?
Nor that one. MDB is usually an MS Access database file.
.mdf is the default extension for the primary data file of a database (any database)
.bak....Does having .bak extension means it is a full back?
None of these file extensions are enforced. I could back my database up to a file called MyDBbak.dat and it would still be a valid database backup.
.bak is usually used for both full and differential backups and for all databases, system or user.
How do I verify if my .bak is a full back file?
RESTORE HEADERONLY
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
January 20, 2011 at 6:55 am
There are tables in msdb (one of the system databases) that store the backup data. You can query those to find what backup files you need in order to restore the database.
For example, query:
select *
from msdb.dbo.backupset
where database_name = 'MyDatabase'
order by backup_finish_date desc;
That will give you a list of all of the backups, ordered by most recent to least. From that, you can look at the backup types (Full, Diff, Log) and see what you need in order to restore the database.
Note that this table can have less data than it should, if the "cleanup maintenance history" option has been selected in a maintenance plan.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply