Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 bak and Trn file
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 11/26/2012 :  04:57:06  Show Profile  Reply with Quote
I want to have backup from a Database, so i did as following
1- I made a full backup from database
2- I make a differential backup every week from database
3- I make a Transaction log backup every every 6 hours, because my database is not so big

my first question is that is the above plan correct?
second question is that when i create a full database backup a file named database.bak is created in a path and i want to make Transaction log Backup, I find the same database.bak in "destionation backup to" which is the bak file that was created in Full Database backup. and again there is just that previous database.bak file in the it correct? shouldn't any *.trn file be created in the path when i create a transaction log backup?

Posting Yak Master

220 Posts

Posted - 11/26/2012 :  15:23:13  Show Profile  Reply with Quote
If your database is not that big/large as you state in your post, why do differential or transactional backups at all? If its not that big and is not modified frequently why not just stick to full backups only?
Go to Top of Page

Starting Member

16 Posts

Posted - 11/30/2012 :  06:15:25  Show Profile  Reply with Quote
About first question:
You are the best person to judge about backup strategy. You can say about your environment. Still I would recommend for daily differential and hourly T-Log backup.
this will easy for recovery time to manage easily to work with minimum number of files. Also place a schedule for Full backup Monthly or Weekly if the DB size is not big.

About second Question:
You can change the Backup file name and its extension. Some Practices, which I have noticed, is, for Full backup, DBA will give as .bak and for Differential as .DIF and for Transaction-Log .TRN. You can name it as you want and even extension doesn't matter at all. May be you are looking the name in the Interface\GUI, please remove the filename with 'Remove' button and you can add new name\extension there.
Go to Top of Page

Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 11/30/2012 :  07:06:51  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Diferential backup every week is very odd. If the db is not big then do fulls every time.
I would generally do at least a full weekly and diff daily whatever the database. If you lose a backup all the diffs following are useless and you have to use all the tr logs up to the previous valid full/diff combination - too much can go wrong with your scenario and it means going back a long way when you make changes.

TR log backups are not really to do with the size of the database but how frequently updates are made and how many changes you can afford to lose.

I would timestamp the backup files

Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000