Backup on System defined path

Question

Backup on System defined path

Anamika

SSCertifiable

Points: 6202
More actions
August 23, 2012 at 12:07 pm

#263337

hi,
i've heard that if we take the backup on the following path, that is where the sql server is installed,
\Microsoft SQL Server\MSSQL.2\MSSQL
there is a possibility of losing the backup if SQL crashed / corrupted. the folder may not be accessible if SQL crashed / corrupted - is this true?
Thanks,
Regards,
Ami

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

If the backup succeeds and is valid (i.e. can be successfully restored somewhere), then SQL Server later crashes then your backup will not be affected.

That said, it is a good practice to separate your backups from your SQL Server program, database data and database log files for performance and recoverability purposes. Anything you write to the drive where those files live will compete with SQL Server for I/O resources. If you have a network location to write your backups to consider using it. Or if you have a local disk separate from your other SQL Server files you can use that too.

In any case make sure your backups are valid (test restoring them) and that you can retrieve them in case your current server completely dies and the local disks are inaccessible.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Anamika SSCertifiable Points: 6202 More actions · Answer 2

Thanks for your reply.

but i searched net to add value to that comment. i'm not finding one. is it mentioned in any MS sites or any white paper / blogs available?

Thanks,

Regards,

Ami

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Which comment? I made many.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Anamika SSCertifiable Points: 6202 More actions · Answer 4

That said, it is a good practice to separate your backups from your SQL Server program,

white paper / blogs on the above comment.

-Ami

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

use "Microsoft sql server best practice analysizer" it will suggest you the best practices.

Regards
Durai Nagarajan

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Anamika (8/23/2012)
That said, it is a good practice to separate your backups from your SQL Server program,
white paper / blogs on the above comment.
-Ami

From Best Practice recommendations for SQL Server Database Backups:

1. Make sure you are not storing your backups in the same physical location as the database files. When your physical drive goes bad, you should be able to use the other drive or remote location that stored the backups in order to perform a restore. Keep in mind that you could create several logical volumes or partitions from a same physical disk drive. Carefully study the disk partition and logical colume layouts before choosing a storage location for the backups.

The more isolation you can create amongst the critical portions of your instance (including your program files) the better chance you'll have in recovering from a failed drive or failed server. If you have a fast network drive to write backups to I would use that, else write them to a local disk then copy them to a network location or tape.

Here is some great guidance on the topic: SQL Server Backup Best Practices by Brent Ozar[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato