SQL Backup 9

Finding database files

When you create a scheduled restore job to restore the most recent backups each time (by selecting Restore from folder and file name pattern on step 1 of the Schedule Restore Jobs wizard) you can specify where each data and log file will be restored to by selecting Individual files to specified locations on step 3. You can also use this option to specify where full text catalogs (SQL Server 2005) and filestreams (SQL Server 2008 and later) will be restored to. This option is equivalent to using the MOVE logical_file_name TO operating_system_file_name option in the RESTORE command.

To specify where a data or log file, filestream or full text catalog is restored to, you need to identify the file using its logical name. Three ways of retrieving logical names of data and log files, full text catalogs and filestreams are described below.

If you have launched the Schedule Restore Jobs wizard from a reminder, the option to restore individual files to specified locations is selected by default. The logical names are populated with details from the source database (the database that was or will be backed up by the scheduled backup job).

1. Using a SQL query

Run the following query, specifying the name of the source database (the database that was or will be backed up).

SELECT * from <SourceDatabaseName>.sys.database_files

This query will return details of all the data and log files, full text catalogs and filestreams in the database, including the logical name and physical location of each file.

For more information, refer to your SQL Server documentation.

2. Using SQL Server Management Studio

From SQL Server Management Studio, select the source database (the database that was or will be backed up). Right-click to display the context menu and select Properties. From the Database Properties dialog, select the Files page to view details of the database's data and log files. From the Database Properties dialog, select the Filegroups page to view details of the database's filestreams. It is not possible to view information about full text catalogs.

For more information, refer to your SQL Server documentation.

3. Using a backup of the source database

Run RESTORE FILELISTONLY on an existing backup of the source database to see details of all data and log files, full text catalogs or filestreams contained in the backup. For example:

SQLBackupC.exe -SQL "RESTORE FILELISTONLY FROM DISK = [pubs_FULL_20120229.sqb]"
execute master..sqlbackup '-SQL "RESTORE FILELISTONLY FROM DISK = [pubs_FULL_20120229.sqb]"'

For more information, see The RESTORE command.


Didn't find what you were looking for?