September 17, 2010 at 10:57 am
I took a full database backup using the GUI:
Right click my database -> Tasks -> Back up
I chose a 'Full' backup but didn't pay attention to the file name being generated, only to the location. It turned out the file extension of the backup set was a .trn, which confused me greatly. The file size was consistent with a full backup and when I tested the restore, it showed both the .bak and .trn files in the restore list.
Questions:
1. Is the extension defaulting to .trn normal behavior?
2. Do you change your extension to .bus or something more descriptive when doing manual, full backups?
Up to this point, I've done all my backups through the scheduled tasks provided by the Maintenance Plans, which generates .bak and .trn files.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
September 17, 2010 at 12:39 pm
I think the default is blank, but it seems to "remember" from previous backups. I've found that sometimes the destination and file name is already there and I need to remove it and re-add it. Sometimes it's blank.
September 17, 2010 at 12:48 pm
Dan H. (9/17/2010)1. Is the extension defaulting to .trn normal behavior?
It usually remembers the last backup filename used to do a manual backup (log or full).
Dan H. (9/17/2010)2. Do you change your extension to .bus or something more descriptive when doing manual, full backups?
You can keep the standard BAK and TRN when you do manual backups. If you wanted to differentiate between a manual full and a scheduled full you could change the extension so they stand out. I like that idea myself, not that I remember to do it though:-D
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 17, 2010 at 2:31 pm
there is nothing like default
it remembers the last back up you did on that database whether it was a .bak or .trn
You can find out whcih is correct one by seeing the size of the .bak or .trn
Usually .bak backup with date specified is a good idea to implement.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
September 17, 2010 at 2:48 pm
The GUI has issues, and "remembering" previous files is one.
I would stick with .bak and .trn, just so that no one is confused.
September 17, 2010 at 3:13 pm
The backupset contained both the database and transaction log backup. It was taken in the morning when I realized that the job had failed (due to low disk space).
Why did it have both? I'm also unable to restore this to my test server, under a different name.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
September 20, 2010 at 7:20 am
what are the sizes of the bak and trn files of the backupset
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
September 20, 2010 at 7:44 am
SKYBVI (9/20/2010)
what are the sizes of the bak and trn files of the backupsetRegards,
Sushant
You want to know backup file sizes?
This link should be a good begining.
http://www.sqlservercentral.com/scripts/Miscellaneous/30338/%5B/url%5D
September 20, 2010 at 9:55 am
The file sizes were consistent with previous backups:
30 GB mdf
250 MB ldf
I'm just curious why a manual, full backup contained both files. Also, why can't I restore to my test server under a different database name?
I tried creating an empty database with the same name, still unable to restore.
I have good backups now, since last week, but am confused with this whole scenario.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
September 20, 2010 at 10:30 am
@ sqldch
Generally, if you had selected the right backup, it
shouldnt get you both.
What sort of error you get when you try to restore the backup on other server.
You can try again doing backup same as you did earlier and see if it again creates both files or not?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
September 20, 2010 at 10:36 am
SKYBVI (9/20/2010 @ 08:20 AM)what are the sizes of the bak and trn files of the backupset
Not sure why this matters?
SQLDCH (9/20/2010)I'm just curious why a manual, full backup contained both files.
I just did a test on this one with using the GUI and doing a backup to make sure here. I first did a FULL backup and then left the same name but on the second backup changed it to be a Transaction Log backup. Then I went to do a restore and selected "From device", picking the "one" file I had created (since I used the same name and everything). When the GUI loaded that file in to restore with it shows both a FULL and Tlog backup.
Reason: This is what happens when you use the GUI becuase it creates a Backup set by default. So I can do backups on a database multiple times over a period of time, whether full, differential, or tlog backups. They would all be within one file, think like a tape drive sort of. You can turn this off by going to options in the backup wizard and check the option "Overwrite all existing backup sets". This will cause it to always be one file with "one backup" in it, full or tlog. Caveat to this is you are going to overwrite what was there if you use the same file name.
A better way (IMO) and quicker way is to use T-SQL code on your backups. This ensures you have control over what the bakcup is actually doing. There are numerous scripts on SSC and MSSQLTips.com that can get you started on this one.
SQLDCH (9/20/2010)Also, why can't I restore to my test server under a different database name?
This is another story and again can be easier using T-SQL, but when you do the GUI restore try this out...
1) On the "To database:" type in a new database name, then select the backup file on the "From device".
2) make sure you hit that check box on the "restore" column. then under "Options" where it list the "Restore the database files as" on the "Restore As" column make sure this points to the write path and it should show the MDF and LDF name you typed in the "To database:".
3) I tend to just out of habit also check "Overwrite the existing database", but doing this method is actually going to create a new database for you.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 20, 2010 at 10:37 am
if you remember that backup you have taken is full then you can chage the extension to .bak instead of .trn.
----------
Ashish
September 20, 2010 at 10:40 am
ashish.kuriyal (9/20/2010)
if you remember that backup you have taken is full then you can chage the extension to .bak instead of .trn.
The file extension makes no difference. BAK and TRN are just standard/associated file extensions with SQL Server backups.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 20, 2010 at 10:43 am
The file extension makes no difference. BAK and TRN are just standard/associated file extensions with SQL Server backups.
yes true but you also need to be make file more understandable for future use.
----------
Ashish
September 20, 2010 at 10:57 am
Thanks for the replies, I really appreciate your time.
T-SQL is the key here, I think. Letting the GUI try and figure me out is a practice in frustration.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply