Retrieving database name from bak file

Question

I have a bak file that has in it a back up of a database .

I want to restore this database to a new location and I need to retrieve the database name from this file any idea how to do so ?

I need it to override the data file location and the log file location .

Thanks for help.

Totten

Answer 1

RESTORE FILELISTONLY
FROM DISK = 'full path to your .bak file'

will show you the current file names in the backup. If there are multiple backups in one file and you do not specify "WITH FILE = X" you will only get information for the first backup in the file.

RESTORE DATABASE MyNewDBname
    FROM DISK = 'full path to your .bak file'
    WITH 
      MOVE 'LogicalFilename_Data' TO 'D:\somepath\...\MyDB.mdf',
      MOVE 'LogicalFilename_Log' TO 'D:\somepath\...\MyDB.ldf';
GO

Rough outline with SMO (not tested):

Restore restoreDB = new Restore();
restoreDB.Database = myDatabase.Name;
// Specify whether you want to restore database, files or log
restoreDB.Action = RestoreActionType.Database;
restoreDB.Devices.AddDevice(@"D:\somepath\...\MyDBFull.bak", DeviceType.File);

restoreDB.ReplaceDatabase = true; // will overwrite any existing DB     
restoreDB.NoRecovery = true;

// you can Wire up events for progress monitoring */
// restoreDB.PercentComplete += CompletionStatus;
// restoreDB.Complete += RestoreCompleted;

restoreDB.SqlRestore(myServer);

Ref.

Using SMO, you can retrieve the file list using Restore.ReadFileList

See Also: How to restore a database from C#

Backspin

You didn't say you wanted to do it programmatically. – Backspin

I am restoring a database to a different place , so i should override the mdf and ldf files . to do so i need to get the database name from my bak file. i have no idea what database located in my bak file . – Totten

Whea your first link is exactly what I need. – Totten

Answer 2

I was able to get the name of the database in what feels like a really hacky and uncertain way. However, this really works for me and I am just using this in a small test and development tool just for myself anyway.

If you follow the same answer as Mitch Wheat, what you need to do is the following:

Right after

restoreDB.Devices.AddDevice(@"D:\somepath\...\MyDBFull.bak", DeviceType.File);

Add these three lines:

var fileList = restoreDB.ReadFileList(myServer);

string databaseName = fileList.Rows[0].ItemArray[0].ToString();

restoreDB.Database = databaseName;

You read the associated file list using myServer and you get the information from a DataTable and an ItemArray. The name seems to be present in the first row and in the first item in the item array. After that, use this name to set the Database property of the restore object. Voilà, no need to provide your own database name if you simply want to restore or switch between existing databases whose names you don't really remember any more.

WARNING: This works only if your database has been created in the same instance in which you're restoring it. If you're using this method to restore databases created and backed up from elsewhere it will not work because there are more properties you will probably need to set! Which properties you might ask? You will have to go and shop for yourself inside the ItemArray. Nevertheless, one of the properties is the location to which the database needs to be restored. You can also find the location in there. Stating the obvious but make sure the folder in which the database used to reside also exists on your computer!

Anyway, I tried this out with a couple of databases and this works for me and hope it helps anybody out there!

Varicotomy

Answer 3

JUST Try as Follow:

DECLARE @BackUpPath nvarchar(2000)='X:\YourBackUpFileName.bak'
EXEC('RESTORE FILELISTONLY  FROM DISK=''' +@BackUpPath+ '''  ')

and X is your drive that contains backup file.

Estimative