August 27, 2010 at 9:24 pm
Like title says, wondering if anyone has any good info on how backing up a database works, or how to do it, when to do it, when to rebuild indexes.
Most importantly, we are migrating a users database data into an sql server database, and then building and modifying the database as we go, I don't have any backups set, and the transaction log is huge! way larger than the actual database. Can I safely shrink the trans log somehow before we hand this database over to the client?? Will that mess up the ability to do backups?
Any help is greatly appreciated!
August 27, 2010 at 10:03 pm
One article to read is the last article I reference below in my signature block regarding Manging Transaction Logs.
August 28, 2010 at 12:56 am
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 28, 2010 at 12:46 pm
Sql Server books Online is a good place to start
look up backup database and dbcc shrinkfile.
I would say in this case you must shrink the log file to about 20% of the data file size before handing it over to the client. they won't thank you for landing them with a huge log file to start with. Set the database to simple recovery mode, then run dbcc shrinkfile, then put back in full recovery mode, then take a full backup.
shrinking a file does not affect backup ability, setting a database to simple mode does, the next backup must be a full backup.
I strongly suggest you read the article on managing the logs Lynn referred you to so you understand the implications and pitfalls.
This is a situation though where shrinking the log is acceptable.
---------------------------------------------------------------------
August 29, 2010 at 10:09 am
Thanks for the links, I'm slowly understanding this stuff. Though I do find books online to be a rather difficult source of information to understand. I would rather get a lay persons view first before trying to read those articles.
August 29, 2010 at 10:26 am
The simple view is:
- Full backups copy all your data, permissions, schema, from the database to a file that you can use to restore them. They don't handle logins, so as part of a move/DR, you need to script logins
- The t-log uses space as you make changes to the db. Without a way to clear this, the log continues to grow.
- Full backups DO NOT clear the transaction log, or back it up
- Log backups backup the changes you've made, and in a restore situation, reapply those changes to your restored database (restored from the full). Log backups mark the space in the log that was backed up in the t-log backup as free. It sounds funny, but if it copy copied out in the log backup, that space can be reused.
- The log size depends on # of transactions and frequency of log backups. Unless you have a good system running to compare this to, it's hard to guess the right size and frequency of backups you need. Frequency should be driven by tolerance for data loss.
- DO NOT shrink the data files on a regular basis. A one time thing is fine, but you need to leave empty space in there for data growth and maintenance operations.
- In general you do not want to shrink the log regularly either. It's a waste of resources. Find out the size you need based on your log backup schedule and activity, set a pad, and then set the log there. If it's grown crazily, you can shrink it, but make sure you shrink to a reasonable size that fits your environment based on backup schedules.
August 30, 2010 at 7:54 am
I'd also suggest reading Books Online. It really is some of the most complete documentation available. But, some additional places to look include, this article that I wrote quite a while ago for SSC. It covers how to restore to a point in time[/url], so it shows how to do the backups as well. Also, I wrote the chapter on backups in this book. It's intended as a very introductory level overview, so it might provide what you need. But Books Online is still the better resource.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 31, 2010 at 7:31 am
Not going into the depth that Steve, Grant, Lynn et al went into above (they already covered the background info) ...
The strategy we use as a standard (modified if necessary, based on requirements), is
But, the most important comment is DO BACKUPS. A simple server reboot (for example) can go drastically south if the server doesn't come back up, and you don't have database backups somewhere off server to recover from. Your manager will be really happy if you can recover from a disaster, but be really unhappy if you can't.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply