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:
- 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.
- Create new filegroups in the desired location 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