Thursday, February 16, 2012

Backup

I'm not sure if I'm doing this correctly to ensure recovery in case of a
disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
attached to it. What I'm doing is during the night I run a backup through a
maintenance job in enterprise manager to the BACKUP directory in the SQL file
structure. On another server that has a tape drive I'm backing up this
BACKUP directory to tape. Should I be backing up anything else to ensure
disaster recovery.In summary, ensure that you are backing-up the system databases (there is an
option in the maintenance plan wizard to do this) as well as your user
databases. Make the maintenance plan for the system databases separate from
your plans for the user databases. Also ensure that if you are doing tran log
backups on the databases that have full recovery model, that you have the
backups for the simple model databases in a different plan or you will get an
error in the tran log backup job (even though the tran log backups will
succeed).
If you can backup to a remote server using UNC paths if your mssqlserver
service uses a service account (that has the necessary rights to the file
system on the remote server), that would be preferable. If you cannot
however, then your current backup strategy is adequate.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"George Schneider" wrote:
> I'm not sure if I'm doing this correctly to ensure recovery in case of a
> disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
> attached to it. What I'm doing is during the night I run a backup through a
> maintenance job in enterprise manager to the BACKUP directory in the SQL file
> structure. On another server that has a tape drive I'm backing up this
> BACKUP directory to tape. Should I be backing up anything else to ensure
> disaster recovery.
>|||I currently have all databses under on Maintenance plan. Why is it
neccessary to seperate the system and user db's into two?
"AndyP" wrote:
> In summary, ensure that you are backing-up the system databases (there is an
> option in the maintenance plan wizard to do this) as well as your user
> databases. Make the maintenance plan for the system databases separate from
> your plans for the user databases. Also ensure that if you are doing tran log
> backups on the databases that have full recovery model, that you have the
> backups for the simple model databases in a different plan or you will get an
> error in the tran log backup job (even though the tran log backups will
> succeed).
> If you can backup to a remote server using UNC paths if your mssqlserver
> service uses a service account (that has the necessary rights to the file
> system on the remote server), that would be preferable. If you cannot
> however, then your current backup strategy is adequate.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "George Schneider" wrote:
> > I'm not sure if I'm doing this correctly to ensure recovery in case of a
> > disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
> > attached to it. What I'm doing is during the night I run a backup through a
> > maintenance job in enterprise manager to the BACKUP directory in the SQL file
> > structure. On another server that has a tape drive I'm backing up this
> > BACKUP directory to tape. Should I be backing up anything else to ensure
> > disaster recovery.
> >
> >|||Your plans will fail in an ungraceful manner if you have database in both full and simple recovery
mode and you try to do log backups in such a plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:0C3B4830-8855-45BF-8CC4-84C27E5EF174@.microsoft.com...
>I currently have all databses under on Maintenance plan. Why is it
> neccessary to seperate the system and user db's into two?
> "AndyP" wrote:
>> In summary, ensure that you are backing-up the system databases (there is an
>> option in the maintenance plan wizard to do this) as well as your user
>> databases. Make the maintenance plan for the system databases separate from
>> your plans for the user databases. Also ensure that if you are doing tran log
>> backups on the databases that have full recovery model, that you have the
>> backups for the simple model databases in a different plan or you will get an
>> error in the tran log backup job (even though the tran log backups will
>> succeed).
>> If you can backup to a remote server using UNC paths if your mssqlserver
>> service uses a service account (that has the necessary rights to the file
>> system on the remote server), that would be preferable. If you cannot
>> however, then your current backup strategy is adequate.
>>
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "George Schneider" wrote:
>> > I'm not sure if I'm doing this correctly to ensure recovery in case of a
>> > disaster. I have a SQL 2000 Sp4 server, this server has not tape drive
>> > attached to it. What I'm doing is during the night I run a backup through a
>> > maintenance job in enterprise manager to the BACKUP directory in the SQL file
>> > structure. On another server that has a tape drive I'm backing up this
>> > BACKUP directory to tape. Should I be backing up anything else to ensure
>> > disaster recovery.
>> >
>> >

No comments:

Post a Comment