May 17, 2009 at 3:57 pm
Okay finally I was able to rebuild the system databases and then restore all the databases from my backup.
Now my questions is would it be feasible for me to stop the MSSQLSERVER service before my backup runs and then start it back up after the backup is finished because that way it will copy the master, msdb, temp, etc all those database on the tape, so next time I do a disaster recovery I will just copy it from my tape and everything will be ready to go. Any ideas?
May 17, 2009 at 7:51 pm
I have never seen a database backup agent that didn't cause problems.
As a rule of thumb, never use database backup agents from backup software. It is much better and safer to get the RDBMS to perform a backup to file and then use a File Agent from your backup software to collect that.
This goes for Oracle, Sybase, DB2, SQL Server, Raima, MySQL, PostgreSQL, etc...
Create a maintenance plan to backup your system databases as desired, get the SQL Agent to run it, dump it to a directory and have BackupExec backup the .bak files. Configure BackupExec to exlcude your Data and Log directories
--
Andrew Hatfield
May 18, 2009 at 1:39 am
I do have a maintenance plan to back up all the system databases and other databases. The problem I ran into was that when the original server was brought up the location from where it was installed was probably different, so when I ran the rebuild of the databases it would complain that one of the sqlrun.msi is not valid. Reason it was complaining is because in the registry it was looking for the original path from where SQL server was installed. So it was would be easier if I stopped the MSSQLSERVER service and then let Backupexec run and then start the service.
I will try it out and let you know how that works out.
One other question I had. Do I need to backup differential log? I am doing transactional logs right now. I am doing every hour for transactional log.
Thanks for your help Andrew.
May 18, 2009 at 4:43 pm
stonecold560 (5/18/2009)
One other question I had. Do I need to backup differential log? I am doing transactional logs right now. I am doing every hour for transactional log.Thanks for your help Andrew.
You can backup your transaction logs via SQL. Typically they are saved with an extension of .trn
If you wish, you can then get your File Agent to back those up.
It all depends on what your recovery window is, what is the maximum acceptable time without data or for data loss.
Personally, I would put critical application databases (and msdb) in Full Recovery mode, perform a transaction backup every 15 - 60 minutes (vary for your requirements and activity) with a full every day or week. Again, vary for your requirements.
You can then either perform an incremental or differential File Agent backup during the week and a full at the end, or inc / diff every hour (slightly offset from your sql transaction backup) and a full nightly.
Happy to help
--
Andrew Hatfield
May 18, 2009 at 5:55 pm
Andrew,
What exactly does differential backup do for me? If I do transactional every hour then do I need to differential backup?
Also, when I restore backups .bak should I pick the option RESTORE WITH RECOVERY, RESTORE WITH NORECOVERY, RESTORE WITH STANDBY?
May 19, 2009 at 9:21 pm
A Full Backup backs up everything you point to
A Differential Backup backs up everything you point to that has changed since the last Full Backup
An Incremental Backup backs up everything you point to that has changed since the last backup of any kind (Full or Differential)
In smaller shops the tendency is to perform Diffs daily with a weekly Full. In larger environments often Diffs are replaced with Incrementals. In some software (namely CommVault), you perform one Full backup at the start of time and from thence on all are incrementals. CommVault will then create synthetic fulls by combining incrementals into fulls for you. Nice stuff.
The option of Restore with Recovery, No Recovery, Standby depends on what you need to do post a restore. If you have the full backup, you've restored it successfully and you just want to use it - Restore with Recovery.
--
Andrew Hatfield
May 20, 2009 at 1:36 am
Alright this makes more sense now. So if I understand right I have full backup running every night and I am going to start doing transactional every hour, so that way if i need to restore I can restore from full backup and then restore the transactional log on top of that. Does that sound right? Thanks for all the help.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply