Saturday, February 25, 2012

msdb trascation logging mode

Hi,
I have a production SQL 2000 database with transaction logging turned on
('Full' recovery mode). I backup the transaction log every 15 minutes. In
order to use the point-in-time recovery option, do I also need to have the
'Full' recovery model set on the msdb? I ask this because I assume the msdb
holds information about the transaction log backup taken on the production
database. Without this information, I suspect point-in-time recover will not
be possible. Is my assumption correct?
Regards,
James G.Hi
MSDB is not needed to do point in time recovery. MSDB is just used to keep
the backup information so that Enterprise Manager UI can show you what
backups are available. It is a nice to have.
Point in time recovery needs the database dump and all the transaction log
dumps after that to the time you want to restore to.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"James Goodwill" wrote:
> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>|||Hi James
The MSDB doesn't have to be in full recovery mode. In fact the MSDB
has no role when you run the restore. If the MSDB would have any role
in the restore then it would be impossible to restore the database on a
different server. All the data that is needed for the point in time
restore is stored in the log backup it self.
Adi|||Hi,
'Full' recovery model set on the msdb?
Ans:- NO NEED
I suspect point-in-time recover will not be possible. Is my assumption
correct?
No. You can do it with out a MSDB tranasction log backup.
--
MSDB database holds the backup history information. FOR each backup SQL
Server logs an entry into BACKUPSET table in MSDB database.
This is just an information only and will be useful if you use Enterprise
manager to do point in time recovery. Even if you
do not have tis information you can do a recovery based on Transaction log
file date and time. For a time based recovery:-
1. Restore the Full database backup first with Norecovery
2. Restore the logbackup file in sequence based on date and time with
Norecovery till last file
3. Restore the last transaction log backup with RECOVERY and STOPAT option.
Note:-
I am taking the MSDB full database backup daily once only.
Thanks
Hari
SQL Server MVP
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:DmmHe.10993$SO4.8738@.newsfe4-win.ntli.net...
> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the
> msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will
> not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>

No comments:

Post a Comment