October 23, 2007 at 5:28 am
Having a problem with a Backup.
An Application that is fully utilised throughout the working day then is required to routinely 'Recalc' throughout the night (16:00 to the following morning) does not have a schedulable 'quiet period' to backup, creating a 'CheckPoint'.
Currently our only solution is to do the 'Full' backup at 02:00 each morning followed by a transaction log at 08:00 hoping that the transaction logs are 'truncated' at that time.
In some instances this has failed and the transaction logs size have stopped the 'Full' backup.
Is there a better solution to this problem.
NB. Our backup policy would normally not be set to 'SIMPLE'...
October 23, 2007 at 5:51 am
My way of creating back-up is to run through the script file by mean of Data publishing wizard. That has the advantage of having both data & schema.
This will be dumped into a tape cartridge.
Next, shrink the relevant databases and do the full back-up then dump into cartridge by mentioning the dates.
If the server getting crashed, you have the script and tape back-up so that with any of the means, the database can become operational within the short span of time.
October 23, 2007 at 9:06 pm
You shrink your database every time you're going to do a backup? That's going to cause horrible fragmentation issues with your indexes.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 23, 2007 at 9:19 pm
This is SQL Server. You don't need a quiet period to run the backup. It won't have an issue with the data being changed.
Your process might be in the middle of calculating and no idea if you can track where it is and restart it after a restore, but you don't need a quiet period.
I'd also look at running the backup towards the end of the business time. Things might slow a little, but then you'd have another solid backup. Maybe differentials run during the day sometime would help?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 23, 2007 at 9:42 pm
Further to what Paul said, in a production environment, you should be setting the database file sizes to what they need to be so that they do not need to grow. If they need to grow, you users will stop while that happens.
By shrinking your database, you are effectively forcing SQL to need to increase the size of the database in the near future.
You need to determine the size of your database based on the current size and the expected growth in the future. You may need to monitor growth for a while to get an accurate estimation of the growth.
Then as your database grows, you should schedule increases in the size of the data files.
I would also recommend that you allow the database to automatically grow just in case something abnormal happens.
October 23, 2007 at 9:47 pm
Yeah, I should have added what happycat59 said - mine was just the 'wow' quick response 🙂
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 28, 2007 at 1:54 pm
Check the below forum for why not to shrink the db
http://qa.sqlservercentral.com/Forums/Topic412213-169-1.aspx
Regards..Vidhya Sagar
SQL-Articles
October 29, 2007 at 11:35 am
You should be able to do both full and transactional backups with the database still remaining online and available. If you move to SQL Server 2005 Enterprise, you can even re-index online, Yeah SQL Server!
I'm responsible for a SQL db that is the back end for a 24/7 website, the only time I have to stop the website is when I do my re-indexing, every Sunday. (I have SQL Server 2005 Standard Edition.) 🙁
October 29, 2007 at 11:49 am
Jim Dillon (10/29/2007)
You should be able to do both full and transactional backups with the database still remaining online and available. If you move to SQL Server 2005 Enterprise, you can even re-index online, Yeah SQL Server!I'm responsible for a SQL db that is the back end for a 24/7 website, the only time I have to stop the website is when I do my re-indexing, every Sunday. (I have SQL Server 2005 Standard Edition.) 🙁
Why not use ALTER INDEX ... REORGANIZE? Your situation is exactly why I wrote DBCC INDEXDEFRAG in the first place for 2000.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply