| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2014-09-09 : 04:03:11
|
| I have a db that is constantly used and I don't want it to slow down (though it is used less during the night)what's the most suggested as far as backing up the db and log files?full backups? transaction log backupswhat do you suggest? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-09 : 08:29:18
|
| If it is an OLTP database (i.e., lot of writes and data modifications), do a full-backup nightly and frequent transaction log backups (15 or 30 minute intervals).If it is mostly read-only with only a few writes you could expand the intervals of full backups and transaction log backups.Of course, these are wild guesses without knowing anything about your environment or any of the details of your database, so I might be way off. But, you could start with that, evaluate the impact and then fine-tune accordingly. |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2014-09-09 : 09:10:26
|
| it is a lot of writes and a very large dbso it's best to do a full backup each night snd transaction logs every 15 minwill this tax the server |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-09 : 11:15:25
|
| SQL Backups do not lock any tables or interfere with the normal operation of the database. It will of course add some load to the server, but only to the extent that it requires to read from disk and write to disk. So the disk and i/o will experience some additional load. I don't know how to gauge that impact unless I have a lot more information about your hardware set up and the usage patterns.If you have UAT environments, evaluate the impact there, even if the client load is not comparable. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2014-09-09 : 12:29:41
|
| is it best to do a mantanace plan for backup ?or a specific script? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-09-09 : 12:36:50
|
| You could write a script or search for one on the web that will do backups. But perhaps the easiest and safest is to create a maintenance plan. In SSMS object explorer, find the Maintenance Plans node under Servername -> Management, right-click and select maintenance plan wizard. It is very user friendly and will walk you through the steps and will create a SQL Agent job for you. Create two plans, one for full backups and another for transaction log backups. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2014-09-09 : 12:50:15
|
| thanks for your help -- and once I have a backupif I want to restore it to a new database -- is that a probelm (I remember once getting an error - that it was a different backup set) and this is important to me |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-09-09 : 13:00:54
|
quote:
Originally posted by esthera
thanks for your help -- and once I have a backupif I want to restore it to a new database -- is that a probelm (I remember once getting an error - that it was a different backup set) and this is important to me
You likely just needed to use the WITH REPLACE option of RESTORE DATABASE.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-09-11 : 11:45:48
|
quote:
Originally posted by esthera
I have a db that is constantly used and I don't want it to slow down (though it is used less during the night)what's the most suggested as far as backing up the db and log files?full backups? transaction log backupswhat do you suggest?
What server load has directly to do with backup ? . You must tell us your RPO and RTO before asking us about backup policy. How much data loss is acceptable. Is DR in place ? If yes what it is ? Log shipping, mirroring, cluster ?Hope this helpsRegardsShankyhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
|
best backup routine