Backup Device File Size
-
Hello All,
I have created a backup device that I use to backup daily incremental backups to. That backup device file (device_name.bak) is growing considerably. Is there a way to shrink that file and if so, how will that affect a restore if one becomes necessary.
Currently, I do a weekly full and daily incrementals after that.
Thanks for your help.
Ronnie
-
I would recommend that instead of using a backup device you backup to a unique filename each night. This way you can purge the older backup files off of disk. SQL Server maintenance plans offer a simple and builtin way to do this.
-
I also use files and filenames over devices as well as it's much easier for me to manage and keep track of. Devices are pretty much a throw back to the earlier versions of SQL Server although there may be other uses for them. We typically will have daily full backups and periodic (1 hour to 15 minutes) transaction log backups all using files.
If you're backup device is being appended rather than overwritten, that would explain the growth. And it will keep growing unless your job schedule overwrites old backup copies at some point. I'm curious about your using 'incremental'. Do you not take a full backup of the database at some point? If not, please do. If so, you can overwrite them after a certain point depending on how long you want to go back and how much disk space you have available.
My hovercraft is full of eels.
-
Oops...just saw that you are taking full backups weekly. Please ignore that line and sorry for the quick fingers.
My hovercraft is full of eels.
-
Thanks guys.
So backups to files are better??
I guess I need to make some changes. I've read documents that discuss backups and the importance, but I haven't found a good one that offers the best proven approach. Does anybody have any recommended reading material that focuses on this subject?
Ronne
-
This is a pretty good start: http://www.sqlservercentral.com/columnists/bahmed/backupscenariosforsuccessfulsqlserverrestoresandre.asp
If you Google around some, you can also find some free tutorials and articles on implementing backups from many other SQL Server related sites.
What is nice about the above article is that it provides a basic discussion on the system as well as the user databases and the differing types of available backup strategies. Whichever backup strategy you adopt is going to depend a great deal on your environment and on how much data you can afford to lose. IMHO, probably the most important function of a DBA is to fully think through, plan, and implement a backup strategy that best fits the needs of the users and ensures quick recovery with a minimum of lost data. SQL Server gives you a lot of flexibility with backup and recovery options and you'll need to choose the ones that best fit your needs.
A typical backup scenario for our production servers is weekly full backups of the system databases (which are set to simple recovery mode), daily full backups of user production databases (set to full recovery mode), with hourly transaction log backups (also known as dumps). With SQL Server 2000, Microsoft defaults the system databases to simple recovery mode when it installs and I've found it best to just leave them that way. All of these DB's are backed up to files which are also later backed up to tape. There are a lot of third party tape backup products out there which include a SQL Server agent that allows backups of open databases and these provide you with an extra level of protection.
It probably goes without saying, but it's also very important to test your backup strategy periodically by performing restores and a test or development environment is ideal for this. By doing so, you'll ensure that you're familiar with the steps required to recover a database when you experience a disaster or critical failure. After all, you don't want to be Googling or thumbing through Books On Line when you've got an angry mob of users and managers breathing down your neck waiting for their system to come back on line.
Hope this helps and best of luck with your implementation.
My hovercraft is full of eels.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply