Skip navigation

Backing Up and Restoring Files and Filegroups

SQL Server 2000 and 7.0 let you back up and restore individual files or filegroups. This feature can be useful in environments with very large databases (VLDBs). You can back up just one file or filegroup each day so that you don't have to back up the entire database every time you make a backup. Being able to restore files or filegroups can be useful when you have an isolated media failure—say on just one drive—and restoring the entire database would take too long. With SQL Server 2000, you can also make differential backups of individual files or filegroups. Here are a few details to remember when you're backing up and restoring files and filegroups:

  • You can back up individual files and filegroups only when your database is using the full or bulk_logged recovery model, because you must apply log backups after you restore a file or filegroup; you can't make log backups of a database that's using the simple model.
  • Unlike full database and differential backups, a backup of a file or filegroup doesn't back up any portion of the transaction log. So, none of the transaction log is in a file or filegroup restore, and you still need to restore the log.
  • You can restore individual file or filegroup backups from a full database backup.
  • Immediately before restoring an individual file or filegroup, you must back up the transaction log. You must have an unbroken chain of log backups from the time the file or filegroup backup was made until the time the files or filegroups are restored.
  • After restoring a file or filegroup backup, you must restore all the transaction logs made between the time you backed up the file or filegroup and the time you restored it. Restoring these interim transaction logs guarantees that the restored files are synchronized with the rest of the database.
Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish