Backup Question

I have some SQL 2000 and SQL 2005 servers as part of my network and I am
including the SQL databases are part of my nightly backup routines. How I do
this is set up SQL jobs to create .bak files early in the evening so that the
nightly backup "sweep" then includes them in the backup since the .mdf and
.ldf are open files. This work great but is it the best way? Does not
Volume Shadow Copy help me work around this issue so that I can backup open
.mdf and .ldf files? If so then how does that work since I'm not familiar
with it.
Basically, what does everyone else do as part of the backup rountines to
make sure SQL Server data gets backed up and what databases (outside of your
application databases) do you backup (master etc.)?
Thanks!
-Richard KHello,
There are a few of ways you can tackle this...
1. Use a third party tool that can back up the database directly to tape
like Backup Exec or Lite Speed etc.
2. Create a maintenance plan to back up the database at a schedule time to
your directory.
3. Online Back ups
4. Etc.
There are lots of good information on how to set up a maintenance plan in
the SQL Books online or check out
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1076630,00.html
I Hope this helps.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K|||Sorry I left out a few things..
If you are doing replication you will want to back up the Back up the
Publisher, Distributor, Subscriber(s) and Master DBs and all your SQL Users.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#ESSAG
Also, depending how important your data is you may want to do Trans Logs in
between full backups.
Thanks,
Christina
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
> Thanks!
> -Richard K|||Richard K wrote:
> I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I do
> this is set up SQL jobs to create .bak files early in the evening so that the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of your
> application databases) do you backup (master etc.)?
I saw Christina's answers but decided to describe what I normally do.
1. The \Data directory is excluded from the file system (FS) backup
2. A management plan (or several plans if you need to do the transaction
log backups for some databases and not for others) puts all the backup
files into \Backup directory and deletes them after N days (depending on
the space capacities).
3. A file system backup writes to tapes those *.bak and *.trn files
created by the maintenance routine.
The timing and specifics depend on the business needs and probability of
disasters.
If the most probable disaster is a data loss caused by the
application/operator (i.e. the SQL Server is up and running but the data
was lost), you better have SQL backups ready and copy them to the tape
even 20+ hours after they are created.
If the most probable disaster is a hardware failure and the backups need
to be restored on a separate SQL Server, then it is better to schedule
the SQL backups prior to the FS or trigger the FS backup at the end of
the SQL backup.|||"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:ABF5A430-0B76-454D-BEE8-F32863C53C11@.microsoft.com...
>I have some SQL 2000 and SQL 2005 servers as part of my network and I am
> including the SQL databases are part of my nightly backup routines. How I
> do
> this is set up SQL jobs to create .bak files early in the evening so that
> the
> nightly backup "sweep" then includes them in the backup since the .mdf and
> .ldf are open files. This work great but is it the best way? Does not
> Volume Shadow Copy help me work around this issue so that I can backup
> open
> .mdf and .ldf files? If so then how does that work since I'm not familiar
> with it.
>
Don't back up the .MDF and .LDF files.
Even with volume shadow copy you won't get a consistent backup.
Imagine this scenario. You backup MyDB.MDF at 11:00 PM.
By the time you get around to backing up MyDB.LDF at 11:15 PM, new
transactions have been completed in MyDB and some new ones have been
started, but not completed.
Now things crash.
You restore MyDB.MDF. Ok, all's good and fine. You restore MyDB.LDF.
Now you try to start up the DB. It can't. Why? Because MyDB.LDF has open
transactions that don't even exist in MyDB.MDF yet and MyDBF has open
transactions that MyDB.LDF things are completed.
So, yes, simply backup to disk as you seem to want to do. Simply figure out
how long it takes to do a full backup.
Generally I did full backups of my user databases 3 nights a week. And then
transaction log backups every 15 minutes.
WORST case scenario for recovery was to restore the most recent full backup
and then 288 or so transaction log backups.
Now, if I'm then backing them up to tape, well I simply make sure I keep at
least 3 days worth of tapes around.
Generally though I like to backup to a snap server or other NAS device and
keep them there for a week (with tape backups as desired). This makes
recovery even faster.
> Basically, what does everyone else do as part of the backup rountines to
> make sure SQL Server data gets backed up and what databases (outside of
> your
> application databases) do you backup (master etc.)?
I backed up the system databases daily since they're small and generally can
only have simple recovery mode, so to capture changes, I wanted to make sure
we had a fairly recent copy.
> Thanks!
> -Richard K
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html