Questions on howto properly setup for backup and restorign

Question

Questions on howto properly setup for backup and restorign

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jason Williams-387288 SSCommitted Points: 1740 More actions · Answer 1

Just a quick question:

When I backup transaction logs, should I do it using the backup method within Enterprise Manager? Or should I use a script of some sort?

Secondly, I would like to backup my transaction logs every 30 minutes. Is it best to use a script or the backup within Enterprise manager?

Thanks,

Jason

huseinr SSC Journeyman Points: 80 More actions · Answer 2

Hi Jason,

I am about five months late to this forum, but I am sure your won't mind me answering you question now. First of all, (as the first reply stated) you need to consider how important you data is. Could you live with an hour of lost data? six hours? twelve hours? a day? All in all if you colleagues need to enter a lot of customers into the system the blame will fall on you for not preparing the most appropriate backup and disaster recovery strategy. What you might want to do is to implement a backup strategy as following:

- Full backup scheduled every night at 00:00

- Differential backup every three hours from the start of business, eg. 11:00, 14:00, 17:00

- Transaction Log backup every hour except during the hours when differential backup is being executed.

Let's say that you have a system crash at 16:15.

1. First of all TRY to make a transaction log backup if you have access to the disk (if it wasn't a disk failure).

2. You will restore the full backup from the night before with NORECOVERY option, restore the last differential backup you made (which is 14:00) and keep the database in NORECOVERY mode, restore the transaction log backup from 15:00 with NORECOVERY option, restore the second transaction log which was made at 16:00 with NORECOVERY, but ONLY if you made the backup of the transaction log in step 1, otherwise use RECOVERY option to enable the database and lose only 15 minutes of data entry. If you have the last transaction log restore it with RECOVERY option and with STOPAT by specifying let's say 16:10. This will restore you database until 16:10 which means that you lost only 5 minutes of work.

Backup strategy and disaster recovery are something that you need put down on paper and from time to time test it. Also, make sure you understand the business for which you work and you would get a better idea what needs to be implemented and at which intervals.

Regards,

Husein

Jason Williams-387288 SSCommitted Points: 1740 More actions · Answer 3

I appreciate the help everyone has provided.

As a quick question, what is the proper method for backing up tlogs? Do I use Enterprise manager? Should I use a script?

I am not sure and want to make sure I get it right.

Thanks,

Jason

huseinr SSC Journeyman Points: 80 More actions · Answer 4

There is no proper way to be honest. Everything is a matter of preference. If you can't be bothered to type too much use Enterprise Manager, but personally I prefer the script as I can reuse it later for additional backups.

Jason Williams-387288 SSCommitted Points: 1740 More actions · Answer 5

Thanks, I appreciate it.

I would like to automate this with a script. So two questions:

1.) When I use the script to backup my databases and TLogs, should I use a script that backups all the Databases and tlogs (two different scripts actually) that have been set to full recovery mode? Is it worthwhile backing up all the databases (I only have 1 DB on the box, besides the standard DB's that come by default) as well as the tlogs? Or should I backup individually?

2.) I am not good nor familiar with how to write scripts and SQL querys. Does anyone have a recommendation on how to get started writing queries and scripts in SQL?

Thank you,

Jason