Script to archive offline databases

Below is the script 1 that can be used to process the offline databases:

  • Bring database online
  • Backup the database
  • Detach the database
  • Move the MDF and LDF file(s)

The first part to bring the databases online, back them up, and detach them, is:

Please ensure you take a copy of the data in TempFilesToMove using the statement below to make sure you have a list of where the files are:

The second part to move the MDF and LDF files, is:

If the service account that runs SQL Server doesn’t have necessary permissions to the archive location, then you may get Access Denied messages when you run the second part. That’s why I suggest taking a copy of the TempFilesToMove table, just in case you need to run it manually.

  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

CJayMead's SQL Server, Endeca, MS Office Help © 2018