Restoring files in non-standard locations

Databases created in SQL Server have their files placed by default in a standard location. It's also possible to create the filegroups for a SQL Server database in a user-designated directory. This can be useful in a variety of instances—for example, if you don't want to clutter the MSSQL directory with the actual databases, but want them in their own specific directories for ease of organization.

If you have multiple filegroups in a database, they will all be consolidated into a single backup file, making it easy to transport the backup from machine to machine if needed. However, when performing the restore operation, SQL Server will insist on restoring the files in the same directories and pathnames. For instance, if you have a filegroup in the directory C://filegroups, the backup will be restored to the same directory. If no such directory exists, you will have to create it, or the restore process will terminate with an error.

This can create problems when moving to a machine that has a completely different set of directories or drive-mappings. If you don't want to preserve the same layout of physical files, there is a way to eventually move the files into new locations:

  1. Create new, matching directories that match the locations of the original files. If you need to restore to a drive letter that doesn't exist, use the SUBST command to map drives to temporary directories as needed.
  2. Create new filegroups in the desired location

    Requires Free Membership to View

  1. and migrate tables into them as needed. When a filegroup has no tables or other data structures allocated to it, it can usually be removed safely.

Another possibility is to recreate the database on the server using the RESTORE... WITH MOVE command, which creates the database using the file locations specified.

Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!

This was first published in August 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.