Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
The Dog
Starting Member
3 Posts |
 Posted - 2012-02-10 : 15:13:11
|
| This is my first post so thankyou! I am a developer that has been asked to watch our databases for a school district. Many servers have maintence plans that are not working and our data is not being backed up. My plan is to go through all of them and create a full daily back up of all databases on each server and keep these for 4 days. I am a little confused with the transaction log backups. For the time being I want to just do a full back up and leave it at that, but we are running full recoverymodels on all db. I know we dont have point in time recovery, but can I restore from just the BAK file and no trn files? Thankyou. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-10 : 15:57:46
|
| Yes. Absolutely. But flip 'em all to simple recovery until you're ready to implement log backups. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-10 : 16:40:05
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
The Dog
Starting Member
3 Posts |
Posted - 2012-02-10 : 17:03:25
|
| Thanks all. Am I understanding this right, setting it to simple will allow for the transactin log to be managed by the system, thus never getting to big? Can I switch it over just in the properties of the DB? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-10 : 17:43:47
|
| Yes.But ... if you have Log files and backups you can restore to point-in-time. If not you can only restore to last full backup (and optional subsequent differential backup).The question I would ask is: if your users had to restore to last-night's-backup would they be able to recreate all yesterday's data (or is, for example, all the input paperwork already distributed and filed etc?) Also, how long would it take to catch-up? If you have one data entry person and they do data entry for 50% of their time then that's easy. If you have a department of 10 people, and they pretty much do data entry all day long catch-up is very hard (assuming they have not filed all the originating paperwork, or the database is not logging data typed in directly from phone calls or somesuch).lastly, a log backup may well enable you to restore (with zero data loss) a database lost to file corruption. Its not a guarantee, but it does improve the odds significantly. (You can restore back to the last clean backup, and then restore all the logs made after that. There is a reasonable chance that, because the logs are made using a separately mechanism to the database itself, that the logs will be clean, and thus the restored database will be clean too. Storing the log files on a separate disk system to the data files will improve the chances.)So unless you can easily recreate the lost data if you had to restore back to "last night's backup" I would personally want to have log backups in place "immediately" |
|
|
|
The Dog
Starting Member
3 Posts |
Posted - 2012-02-10 : 17:47:54
|
| Thanks Kristen. So I am doing full backups every night and transaction backups every hour and keeping them for 2 days. When I run a transaction backup the size of the ldf file does not change, isnt it supposed to with the full recovery model after a transaction log backup? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-10 : 18:50:08
|
| Transaction Log Backup just marks the "used" bit as now being available for re-use. It doesn't physically shrink the file because the act of re-growing the file would cause fragmentation etc.If your log files have grown exceptionally large (because log backups were not being taken at some previous time, or someone did a massive deletion of stale data, or somesuch) then you could (manually) do a one-time-shrink to get them to a more reasonable size, but don't shrink them smaller than necessary as they will just grow back again (and fragment etc in the process) - i.e. you shouldn't be using SHRINK often.You might want to increase your log backups to every 15 minutes, instead of hourly. It seems to be the interval of choice between having loads of files, and having the minimum chance of data loss. (Collectively the total log files for the day will be the same size whether you do it once, or every 15 minutes [although there ia some overhead on each file])Have a look at the size of your Log Backup files, if you have some that are much larger, and there is a pattern to them (same time of day, or same day-of-week) then that is your largest transaction load. Its often the routine housekeeping of index rebuilds, or purging stale data, and you might want to look at those scheduled processes to see if they can be improved to generate smaller transactions (which will reduce the MAX size that your LDF file needs to be, and the size of the LOG Backup file). We can't make our big transaction scheduled task any better, so we increase the log backup frequency to every 2 minutes whilst that task is running. |
|
|
|
|
|
|
|
|
Full Recovery Model and Logs