4

We have several database driven applications using MS SQL Express, Pervasive, MySQL, and some sort of flat file database. PostgreSQL "may" be added later.

We have backup software that is VSS aware. I need to backup the database files off the filesystem with regular Windows-based backup software. We do not have SQL plugins for the backup software.

Do I need to stop the database service before the backup system runs at night, to ensure the database files are in a "consistent" state that can be restored? Or will the VSS snapshot taken by the backup software allow me to reliably restore data 100% of the time?

I simply need a quick & dirty way of copying the data files with my typical, vss aware, backup software. Using the SQL plugin would only provide back ups for MS SQL Express anyway.

Hannah Vernon
  • 70,041
  • 22
  • 171
  • 315
user37109
  • 41
  • 1
  • 2
  • 1
    As it stands this question is way to broad. Each one of those DBMS listed have their own methods of proper backup and recovery processes. –  Apr 15 '14 at 02:14
  • 2
    @ShawnMelton Not necessarily. The question is not how to backup using each product, but whether stopping the services is required/sufficient to capture a consistent state using VSS. Whether this is advisable or not is certainly relevant, but interpreting the question as too broad does not necessarily make it so, IMO. – Paul White Apr 15 '14 at 02:56
  • IMO the question is too broad because it asks about 5 different DBMSes. I think the question itself is fine. It's difficult for one person to give a canonical answer for all of those systems, including one none of us knows anything about. – Jon Seigel Apr 21 '14 at 18:27

1 Answers1

3

In SQL Server you do not backup the database's files directly. You issue a BACKUP command which creates a new file, usually with a .bak extension. You do not need to stop SQL Server to do this. It is this new file which is moved to tape & off site. To restore you will present this .bak file to a RESTORE command. Here's a previous question on the subject and the Microsoft site. I imagine the other RDBMSs would work similarly but I claim no expertise with them. Read the publisher's documentation.

For a flat file system I imagine you would definitely have to stop all write activity to ensure integrity of the data within the files you may hope to restore later. Again, ask the developers.

Community
  • 1
Michael Green
  • 24,839
  • 13
  • 51
  • 96