How to query a SQL Server backup file

Question

I am using SQL Server 2008 R2, I have a backup file B:\backups\full_backup.bak but I do not know what backups this file has or what files each back has. How can I write a query to restore a database from this backup file?

I have tried to restore database but it threw an error saying

Msg 3156, Level 16, State 4, Line 1
File 'Application_Primary' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Application_Primary.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 3156, Level 16, State 4, Line 1
File 'Application_FTS' cannot be restored to 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Application_FTS.mdf'. Use WITH MOVE to identify a valid location for the file.

Msg 3156, Level 16, State 4, Line 1
File 'Application_Log' cannot be restored to 'L:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Application_Log.ldf'. Use WITH MOVE to identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Code:

restore database Application_db
From disk = 'B:\backups\full_backup.bak'

Answer 1

It appears to me that you are trying to restore a backup which has a database backup from another server with some path stored for data file (.mdf,ldf).

You will need to restore the database using the MOVE clause. Just the RESTORE command will try to restore database to the paths of the database from where this backup was taken.

To Check what backups there are in a backup File

USE master;
GO

RESTORE HEADERONLY 
FROM DISK = N'B:\backups\full_backup.bak'
GO

This will return the backups this backup file holds. You need to make sure what Position you backup file is at.

To Check what files are in a backup File

USE master;
GO

RESTORE FILELISTONLY 
FROM DISK = N'B:\backups\full_backup.bak'
 WITH FILE = 1    --<-- Position of backup in the backup file 
GO

This will return the files that particular backup has. You will need these file names for MOVE clause in your restore statement.

Restore Database

USE master;
GO

RESTORE DATABASE [Application_DB]
 FROM DISK = N'B:\backups\full_backup.bak'
WITH FILE = 1             --<-- position of database backup in the backup file 
,MOVE N'Application_Primary' TO N'D:\<some valid path>\Application_Primary.mdf'
,MOVE N'Application_FTS'     TO N'D:\<some valid path>\Application_FTS.ndf'
,MOVE N'Application_Logy'    TO N'D:\<some valid path>\Application_Log.ldf'
,RECOVERY;

GO

Answer 2

You can restore HEADERONLY of the file to see exactly which backups it contains:

DECLARE @HeaderInfo table
  (
        BackupName  nvarchar(128),
        BackupDescription  nvarchar(255) ,
        BackupType  smallint ,
        ExpirationDate  datetime ,
        Compressed  bit ,
        Position  smallint ,
        DeviceType  tinyint ,
        UserName  nvarchar(128) ,
        ServerName  nvarchar(128) ,
        DatabaseName  nvarchar(128) ,
        DatabaseVersion  int ,
        DatabaseCreationDate  datetime ,
        BackupSize  numeric(20,0) ,
        FirstLSN  numeric(25,0) ,
        LastLSN  numeric(25,0) ,
        CheckpointLSN  numeric(25,0) ,
        DatabaseBackupLSN  numeric(25,0) ,
        BackupStartDate  datetime ,
        BackupFinishDate  datetime ,
        SortOrder  smallint ,
        CodePage  smallint ,
        UnicodeLocaleId  int ,
        UnicodeComparisonStyle  int ,
        CompatibilityLevel  tinyint ,
        SoftwareVendorId  int ,
        SoftwareVersionMajor  int ,
        SoftwareVersionMinor  int ,
        SoftwareVersionBuild  int ,
        MachineName  nvarchar(128) ,
        Flags  int ,
        BindingID  uniqueidentifier ,
        RecoveryForkID  uniqueidentifier ,
        Collation  nvarchar(128) ,
        FamilyGUID  uniqueidentifier ,
        HasBulkLoggedData  bit ,
        IsSnapshot  bit ,
        IsReadOnly  bit ,
        IsSingleUser  bit ,
        HasBackupChecksums  bit ,
        IsDamaged  bit ,
        BeginsLogChain  bit ,
        HasIncompleteMetaData  bit ,
        IsForceOffline  bit ,
        IsCopyOnly  bit ,
        FirstRecoveryForkID  uniqueidentifier ,
        ForkPointLSN  numeric(25,0) NULL,
        RecoveryModel  nvarchar(60) ,
        DifferentialBaseLSN  numeric(25,0) NULL,
        DifferentialBaseGUID  uniqueidentifier ,
        BackupTypeDescription  nvarchar(60) ,
        BackupSetGUID  uniqueidentifier NULL,
        CompressedBackupSize  numeric(20,0)
  )


 INSERT INTO @HeaderInfo EXEC('RESTORE HEADERONLY 
 FROM DISK = N''B:\backups\full_backup.bak''
 WITH NOUNLOAD')

 SELECT * FROM @HeaderInfo

is case of the error: MSSQLServer creates database phisical files in the path of main database file which has been backed up unless you specify a new path for the database files with move command:

RESTORE DATABASE Application
   FROM DISK=N'B:\backups\full_backup.bak'
   WITH FILE 1,--use currect file number
   MOVE 'Application_Primary' TO 'C:\Application_Primary.mdf', 
   MOVE 'Application_FTS' TO 'C:\Application_FTS.mdf', 
   MOVE 'Application_Log' TO 'C:\Application_Log.ldf'