sql 2008 backups

Hello,

I'm pretty new to sql and have a few questions. If I schedule a full backup to run on a database every-night, why do I also need to backup the transaction logs.

What is a transaction log? and why is it important that it also gets backup-ed with the database.
I read on a forum that a transaction log should be backed up every 10 mins or so, is that true?

also, my backups go into a "backup folder" on my c drive. They are not overwritten just added to that folder. Is it safe for me to manually delete database and transaction-log backups that are older then a week or so, or is there a more proper way of doing this then just hitting delete?
I'm using sql2008
MrMayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

strivoliCommented:
If you run a full backup there's no need to run a transaction log backup too. At least not at the same time.
Usually full backups are daily and transaction backups are set every X minutes or Y hours depending how much data you want to retain in case of failure.
The most common scenario (for SMBs) is one daily full and one transaction at midday.

Set a cleanup job to delete old backups.
0
MrMayAuthor Commented:
strivoli, how do I create a cleanup job on sql 2008?
0
strivoliCommented:
Management->Maintenance Plans->New Maintenance Plan...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
>> What is a transaction log? <<

It's a critical part of how the db works.  SQL Server (SQL) works based on "transactions".  The rule is that:

All or none of any given transaction must be applied to the db(s) involved.

SQL uses transaction log(s) to guarantee this.

You can explicitly create transactions, or SQL can implicitly create them.  Either way, all SQL db modification activity is done as part of an all-or-none transaction.

For example:
BEGIN TRANSACTION
INSERT INTO dbo.table1 ...
DELETE FROM dbo.table2 ...
COMMIT TRANSACTION

SQL guarantees that either all INSERTs and DELETEs are done or that none are.


>> transaction log should be backed up every 10 mins or so <<

It depends.  All modification activity is being written to the log.  In Full recovery mode, which is by default used by most companies, log space cannot be re-used (written over) until it has been backed up.  Thus, to keep the log from continuing to grow, you must back it up.  How often you have to do that depends on much space you allocate to the log and the amount of logging activity (i.e. modifications, not SELECTs) you have.  

[Btw, logically then, some dbs could have a completely different log backup schedule than others (this is true despite any claims to the contrary on any web site.  For example, if a given db is *never* modified, its log *never* needs backed up).]


Below is more description on tlogs and their functions from SQL Books Online:
"
Transaction Logs support the following operations:
Recovery of Individual Transactions
Recovery of all Incomplete Transactions When SQL Server Is Started
Rolling a Restored Database, File, Filegroup, or Page Forward to the Point of Failure

[more details on those]
Recovery of Individual Transactions
If an application issues a ROLLBACK statement, or if the Database Engine detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction.

Recovery of all Incomplete Transactions When SQL Server Is Started
If a server that is running SQL Server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. When an instance of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log which may not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to make sure the integrity of the database is preserved.

Rolling a Restored Database, File, Filegroup, or Page Forward to the Point of Failure
After a hardware loss or disk failure affecting the database files, you can restore the database to the point of failure. You first restore the last full database backup and the last differential database backup, and then restore the subsequent sequence of the transaction log backups to the point of failure. As you restore each log backup, the Database Engine reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, the Database Engine then uses the log information to roll back all transactions that were not complete at that point.
"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DcpKingCommented:
A small addition to what Scott wrote:
When SQL Server is told (by you, for example, in code) then if the instruction will change the content of the database (select statements don't count, obviously!), then it writes what it's going to do into the Transaction Log. Then it tries to do it. When successful it marks the transaction complete in the log and goes on to the next thing to do.

SQL Server can run in one of three modes - Full, Simple, or Bulk Entry. You almost certainly are in one of the first two. In Properties for the database look at the top of the Options page for this.

If the Transaction Log gets filled up and it is in Simple mode then it will simply start re-using the log from the beginning again. If it is in Full mode then it will ask Windows for more log file, wait, get it, and continue writing log entries. As you can imagine, it will eventually get to the point where it fills the disc ... and stops. At this point your SQL Server will stop making changes to any databases with log files on the disc!

When you do a data backup you get the data (from the .mdf file). Then do a Transaction Log backup to get the data from the .ldf file. As that happens the entries in the transaction log are marked as backed up, and are made available for reuse. That way your log file doesn't grow and grow ... !

So, you should certainly run a Transaction Log backup after running a Full backup. In addition to this you can also run Transaction Log backups at other times during the day, because after a disaster when you restore the data from most recent data backup you can also restore more information by restoring the subsequent Transaction Log backups, one by one, which will perform the same activities as changed the original data. How many /how often you take these backups depends on what your boss wants - if (s)he wants to lose no more than 1 hour of data then you must run them hourly ...

Hope this helps

Mike
0
MrMayAuthor Commented:
Thank you everyone for your explanations.... It helped out a lot.
I do have another question. On my sql server I have multiple databases.   a2010, a2011, 2012, a2013.  These are all payroll/employee databases. There is obviously no more changes or any work done with the databases from previous years, just the a2013 that is currently in production. Do I still have to do weekly backups and transaction log backups on those older databases or should I just stick to the a2013.   I have a backup of the old databases (that I took at the beginning of the year in January), but a few days I was advised that I should include my weekly backups on those old databases as well. should I? Is there a point?
0
DcpKingCommented:
If there is no more work to be done on these databases then you should be able to consider them read-only. If so, make them read-only ! See attached screen-grab for where.

As for backups, there's no need to make frequent backups because the information never changes. Still, make one every so often - maybe 6 months or so - and then take the previous one out and test it to make sure that it can be restored and is not just a heap of random bits in a file!

hth

Mike
DBReadOnly.png
0
Scott PletcherSenior DBACommented:
As I discussed indirectly before, you certainly do not need to do tranlog backups on the historical/inactive dbs.  If you can't make them read only, then you could do a differential db backup every 24 hours, and a full backup every week or two or the longest period of time you feel comfortable with.
0
MrMayAuthor Commented:
ScottPletcher, what's the point/benefit of doing a differential backup and a full weekly backup on historical databases?
0
Scott PletcherSenior DBACommented:
If they are not read only, technically data could be modified.  If it is, you need to capture that.

The reason for a differential is that it will be tiny if no changes occurred.  

Weekly might be too often, it depends on your comfort level, and the size of the dbs.  If they're large dbs, and you can't compress them, then you probably want to back them up less often.

But the backup doesn't have much overhead and almost no affect on queries that only read the db, so it doesn't really hurt to make the backup.  It's basically a "just-in-case" backup.

Also, you could compare a newer backup with an older backup and verify that NO changes have occurred.  If you see changes, that could be an audit point for someone to look into.  Maybe there's a one-in-a-million chance someone could be changing historical data for nefarious reasons.

Actually, it's probably just because I'm a DBA, and we always like to have reasonably current backups :-) .
0
MrMayAuthor Commented:
Can a database that is "read-only" be changed back to "read-write" in the future if the need comes up?

My current transaction log files are over 1GB in size... I need to shrink them down and then do a full backup and then backup the transaction logs every 10 mins or so to make sure they stay small (few kb in size not gbs) right?
0
strivoliCommented:
Yes.
Yes. You might need to run more backup and shrink cycles.
0
Scott PletcherSenior DBACommented:
Yes, you can easily a database from read-only to read-write and vice versa.

ALTER DATABASE <db_name> SET READ_ONLY --SET READ_WRITE


The historical db logs may be 1GB because they grew during those earlier years.  Remember, the log file will never shrink on its own, so by default it will remain the largest size it ever was.  Shrink the historical db logs down now to a very small size and see if they grow again.
0
MrMayAuthor Commented:
what about my current production database... the transactionlog file size is also over a GB in size. Do I shrink it down first and then do a full backup or do a full backup and then shrink it down?
0
Scott PletcherSenior DBACommented:
I would shrink it down, immediately re-allocate it as 1GB, then do a full backup.

Before attempting to run the commands below, verify the logical log file name and change it if needed to match your actual file name.

USE [a2013]
-- run this command by itself first to get the logical name of the log file
EXEC sp_helpfile



DBCC SHRINKFILE (  [a2013_log] )

ALTER DATABASE [a2013]
MODIFY FILE ( NAME = [a2013_log], SIZE = 1GB, FILEGROWTH = 80MB )

EXEC sp_helpfile --verify new log size
0
MrMayAuthor Commented:
ok... thank you.
If I have a Maintenance plan to backup all my databases, as you can see in the attached jpeg, where can I set it so the backups are overwritten not added.  
I'm assuming that I can switch to the "backup databases across one or more files" and the specify the direcotry?  But in this case I need to specify a backup name. So if I'm backing up all my databases, will this not overwrite each backup with the same name? or will it group them all into one backup?    
If I keep adding (appending) databases to the same directory I will easily run out of space on my hard drive
sql.JPG
0
Scott PletcherSenior DBACommented:
In the maintenance plan, instead of "All Databases", you can select specific db/dbs, if you want to exclude certain dbs from being backed up.

Other than that, sorry, I can't help you with maintenance plans.  I have almost no experience with them -- DBAs don't use mps :-).
0
DcpKingCommented:
"If I keep adding (appending) databases to the same directory I will easily run out of space on my hard drive"

One reason for taking a backup copy is to try to avoid the problems caused by a disc failure. You should do the backup and then move the resulting backup files off-machine and ideally off-site (what happens to all the data if the computer room goes up in flames?!).
0
MrMayAuthor Commented:
I do that. I have a backup that runs every night that backups that "backup" folder to tape. But every night a new backup job is added to that folder. I need some sort of procedure that will delete the older backup jobs instead of appending them to the folder.
0
MrMayAuthor Commented:
If I made my databases "read only" do I still need to do a transaction log backup?  will those log files grow?
0
strivoliCommented:
1st post: use/create a cleanup task.
2nd post: if it is read only there are no transactions. As a result the transaction log will not grow and there's no need to back it up and/or shrink it.
0
DcpKingCommented:
So long as the database remains in Read-only mode then no, there should be no transactions written to the log. Only operations that will change the database are written to the transaction log. So back them all up, shrink their log files, and set them read-only. Then check permissions to ensure that nobody else can make then read-write again - deny permissions by going into Login Properties of the users and denying them the ability to alter the database.

hth

Mike
0
MrMayAuthor Commented:
Ok everyone. I have  a small problem.   Yesterday, I shrunk down my transaction log file down to about 1 meg and did my full backup at about 10pm. I scheduled to transaction log backup to start backing up everyone 30mins starting at 8am this morning. When I came into work (about 20mins ago) I noticed that the transaction log files have grown back to over a gig in size. Why did that happen? No one is at the office making changes to the sql. What causes them to grow so fast and so much?
Now, if my transaction log backup starts at 8am, should or will that bring my log file back down to 1 mb is size?  
Is there some sort of maintenance plan that I can setup to run each night to shrink that log file back down to size?
0
DcpKingCommented:
Do you mean all the transaction logs or just the one on the database containing 2013 data?

If all, then your assertion that the others contain old data that is never changed is incorrect and you need to check your information sources!

If just the 2013 one, then (I'm assuming it's in Full recovery mode, not Simple) you obviously have a job sometime overnight that does a whole heap of changes to the database! You hadn't started doing backups at the time so it just grew as it needed.

Shrinking the t-log file to 1 MB is, honestly, somewhat unrealistic - you need to see what's actually going on and size it accordingly. For example, if you are going to create and store a t-log backup every 30 minutes then the size you'll need its the size needed for the busiest 40 mins of the day.You do not want to shrink it at all (that way leads to total fragmentation of the log file because, just like all other files, its space is allocated by the file system, and having it in millions of little pieces means your system has to do millions of little reads and writes).

So forget about having the t-log at 1 MB. Shrink it to, maybe, 250 MB and see how that goes while you're backing it up every 30 mins. BTW, the whole logfile is, itself, broken up into lots of little log files; see here for a good explanation.

hth

Mike
0
MrMayAuthor Commented:
I have taken my older databases, such as the once for 2010 and older and made them into readonly. The transaction log does not grow for those. It sits at 1024KB. The 2012, and 2013 (current production database) logs grow to over 4GBs.  
And yes I am in full recover mode.   I shrunk the database's transaction log by left clicking and going into tasks --> shrink --> files.
see attached.
sql1.JPG
0
DcpKingCommented:
You say (well, imply) that it was just 1 MB in size at 10 pm last night, so sometime in those intervening 9 or 10 hours your system experienced a lot of activity. So looking at your image I see that while your t-log was taking up 3398 MB it was only using 3378 MB of that. Check out the sizes again with this:
DBCC SQLPERF(logspace)

Open in new window

which will show you the size and used amount for each DB. Then open a query window on the 2013 database and run
DBCC logfile

Open in new window

which will give you a map of what's inside the log file.

I've attached a screen grab of one of mine which was recently backed up and has an 11 MB t-log. You can see that just one of the logical log files that make up the t-log is in use. As the day goes on more will be used until it reaches the bottom, when it'll loop around and start off at the top again. If it catches up with the already-used part before we back it up again then it'll start asking for extensions. If not then tonight it'll be backed up and have a t-log backup done, which will mark almost all the logs as being safely backed up and therefore re-usable.

This sort of use is what you should be aiming at - not trying to get to having a teeny log file! This way you have a log file that is always sufficient in size for what your users throw at it. If your users do enough in 30 minutes to end up with a 2 GB transaction log, say, then that's what you'll end up having. That's just life, but then you'll be pretty sure that your file will stay at 2 GB and not keep growing and growing!

hth

Mike
LogFilePic.PNG
0
MrMayAuthor Commented:
DcpKing, have a look at the attached pics.
As you can see my 2013 database is only using 1% of its log space. Yes the log size is over 4GB. From pic2, you can see that sql is being told to start the initial size of the log file at over 4GB. I did not create this database so I'm just trying to understand how this works.
Since only 1% of the log file is being used, is there a point of having such a large log file? Why does it grow so much if only 1% of it is used?
pic1.JPG
pic2.JPG
0
DcpKingCommented:
Well, think of the log file as a loop of places where SQL Server can enter info about what it's about to do. Each of these places is actually a "virtual log file", but don't worry much about that - each place is either able to take more data or not.  If you look at the output of "DBCC logfile" command when in a query window attached to your 2013 database you'll see it laid out, top to bottom (I think I attached one in the last message).

You'll see a lot of entries labelled "0", meaning "reusable" and just a few labelled "2" (in use" or "used" - not available, anyway).

When you do a log backup the system starts at the oldest used entry and copies going forward from there, leaving "available" entries behind it. Ideally it'll stop when it gets to the beginning of the entry currently in use. This progression includes looping around the "end" of the file!

When actually working, SQL Server will start wherever it currently is and work forwards until it gets to the end of the file, and then loop around to the beginning, leaving a trail of "used" blocks. When it catches up with itself (i.e. it hits a virtual log file that's in use) it'll have to ask the file system for more space. In your case, it'll ask for 450MB (10% of your file size). The file system will allocate that from whatever it has available - probably a whole load of little pieces from all over the disc) and the work continues with the log being filled as it goes. (for another explanation, if I'm confusing you, see here)

So, if you start off with 1 MB, say, it'll grow that by 10% (100KB) and then by 10% (101KB), and so on, and you say it did this all the way up to 4.5 GB between 10 pm and 8 am.

The reason you have such a large log file is that between 10 and 8 you never backed up any of the used virtual log files, so the only way it could log what was going on was to keep expanding the file. If you had made a log backup at, say, 4 am, it might only have grown to 2 GB. I don't know precisely, because I don't know if your overnight load was steady or all in one burst. If it all happened between 1 am and 2 am then no amount of backups after 2 am would help, but doing backups at 1 am, 1:15 am, 1:30 am, 1:45 am, and 2 am might have kept it down to 750 MB.

So, try scheduling t-log backups every hour and also a run of DBCC SQLPERF(logspace) every hour too (one just before and one just after the backup). That way you will be able to see how the fill is progressing, and when you want to schedule your log backups.

hth

Mike

PS this might help you understand things too.
0
Anthony PerkinsCommented:
And yes I am in full recover mode.  
If your database is Read Only there is no point in using Full Recovery Model.
0
Sagir87Commented:
Q. If I schedule a full backup to run on a database every-night, why do I also need to backup the transaction logs ?

Ans: You need to take transaction log backup because it will restore your database point in time even there is a system failure, the transaction log might be required to bring your database back to a consistent state.

If you backup as mentioned above you will have transaction log backup size smaller.

Q. What is a transaction log? and why is it important that it also gets backup-ed with the database.

Ans: Please go through this link to understand in details:

http://technet.microsoft.com/en-us/library/ms190925.aspx

Q. I read on a forum that a transaction log should be backed up every 10 mins or so, is that true?
Ans: Yes its true;

Best Practices Strategy:

Full Backup: Daily
Differential backup: Every 1 hour
Transaction log backup: Every 5 or 10 mins

Q. also, my backups go into a "backup folder" on my c drive. They are not overwritten just added to that folder. Is it safe for me to manually delete database and transaction-log backups that are older then a week or so, or is there a more proper way of doing this then just hitting delete?

Ans: Please go through this link to create maintenance plan in details:

http://technet.microsoft.com/en-us/library/ms191002.aspx
0
Anthony PerkinsCommented:
Some corrections here:
You need to take transaction log backup because it will restore your database point in time even there is a system failure
A Transaction Log Backup does not  "restore your database point in time".  Using Full Recovery Model and doing frequent Transaction Log Backups will allow you to do point-in-time restores.

I read on a forum that a transaction log should be backed up every 10 mins or so, is that true?
Ans: Yes its true;

This is misguided to say the least.  No one can state with what frequency you should do Transaction Log Backups.  It depends entirely on size, transactions and to some extend SLAs.  To be quite blunt, anyone who quotes a fixed time be it 10 minutes or 3 hours without knowing your specific situation, has not got a clue what they are talking about and to be quite frank it is dangerous to blindly follow that advice.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.