Tuesday, February 14, 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 fil
e
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 lo
g
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 a
n
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 throug
h a
> maintenance job in enterprise manager to the BACKUP directory in the SQL f
ile
> 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:
[vbcol=seagreen]
> 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 fro
m
> 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:
>|||Your plans will fail in an ungraceful manner if you have database in both fu
ll 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...[vbcol=seagreen]
>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:
>

No comments:

Post a Comment