Auto Backups – Why can't I just copy files?

Home Forums General How do I… ? Auto Backups – Why can't I just copy files?

Viewing 1 post (of 1 total)
  • Author
  • #17167

    As you may already be aware many system backups just take a snapshot (similar to copy/paste) of a whole drive or incremental (copy / paste of only those files that have changed since last backup).

    This method is not suitable for SQL databases.

    There will be no error message when you select the files and press Copy (or Ctrl+C), but when you try to paste them, you’ll get the ‘File is open in SQl Server’ error message. Similarly a backup system will receive and error along the lines of file is in use. SQL databases are LOCKED while the SQL server instance is running with the Database attached and online.

    When setting up your system back it is recommended you avoid backing up the actual database (.mdf and .ldf) paths to avoid backup failure.

    You can achieve a backup via any method below:

    Take the database offline and copy the files (some backup systems allow this functionality but sometimes the sql instance fails to restart and users cannot use the WMS in the morning)
    Stop the SQL Server and copy the files (Manual)
    If you need only the database state, create a full database backup (Manual)
    If you need the database state and all its transactions, create the full database backup and the transaction log backup (Manual)

    SQL provides a couple of utilities to create auto backup of database files (.bak) which may then be copied/backed up via your normal backup procedure that has been put in place by your IT Administrator.

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.