How to restore multiple SQL Server databases that use the same naming scheme for their MDF and LDF files

Question

I've got a segment of C# code that restores a SQL Server database. This works well. The issue is running it again on another .bak file. Even though the second backup has a different name, it needs to write to the same directory as the first backup and also has the same naming scheme for the .mdf and .ldf files.

I'm just curious if there is a way to modify the naming scheme of the .mdf and .ldf files, or if there is some other method to create subdirs under the initial SQL Server directory for these files to be restored to.

Error message that I'm currently getting:

Additional information: The file XXXXXX.MDF cannot be overwritten. It is being used by database XAXAXAXAX

I figure I could use a move statement, but I am trying to keep from needing all of the directory values hardcoded or logged in a config somewhere.

string sql = "SELECT database_id FROM sys.databases WHERE Name = '"+yuy+"'";

SqlConnection con = new SqlConnection(@"" + singleconn.Replace(@"\\", @"\"));
SqlCommand command = new SqlCommand(sql, con);
con.Open();

object resultObj = command.ExecuteScalar();
con.Close();

if (resultObj == null)
{
    string sql2 = "Restore Database " + yuy + " FROM DISK = '" + @"\" + localdir.Replace(@"\\", @"\") + @"\" + FileName + "'";

    SqlCommand command2 = new SqlCommand(sql2, con);
    con.Open();
    command2.ExecuteNonQuery();
    con.Close();
    con.Dispose();

    File.Delete(@"\" + localdir.Replace(@"\\", @"\") + @"\" + FileName);
    MessageBox.Show("Database recovered successfully!");
}
else
{
    Random rnd = new Random();
    int card = rnd.Next(52);
    MessageBox.Show("There is already a database under this name; renaming the DB to " + yuy + card.ToString());

    string sql2 = "Restore Database " + yuy + card.ToString() + " FROM DISK = '" + @"\" + localdir.Replace(@"\\", @"\") + @"\" + FileName + "'";

    SqlCommand command2 = new SqlCommand(sql2, con);
    con.Open();
    command2.ExecuteNonQuery();
    con.Close();
    con.Dispose();

    File.Delete(@"\" + localdir.Replace(@"\\", @"\") + @"\" + FileName);
    MessageBox.Show("Database Recovered Successfully!");
}

Figured most of this out thanks to scsimon, for now the last thing I am getting in terms of errors is this.

Additional information: Logical file 'XXXXXX.mdf' is not part of database 'Databasename'. Use RESTORE FILELISTONLY to list the logical file names.

The thing is I am pulling this straight from the Databasename properties. any help would be appreciated.

S3S · Accepted Answer · 2019-01-04 18:59:43Z

4

I'm just curious if there is a way to modify the naming scheme of the .mdf and .ldf files, or if there is some other method to create subdirs under the initial SQL Server directory for these files to be restored to.

You can use the MOVE clause as you suggested with the RESTORE command to rename and/or move your data files. It looks like this:

RESTORE DATABASE myDatabase FROM DISK = '\\somedir\someSubDir\mybackup.bak'
WITH
MOVE 'datafile1' TO 'E:\somedir\new_datafile2.mdf',
MOVE 'logfile'   TO 'E\somedir\new_log.ldf'

This was created to move the files from the default location in the backup to another directory (which you can do) but it can also be done to rename them too. Naturally you'd do it for all your .ndf too.

I am trying to keep from needing all of the directory values hardcoded or logged in a config somewhere.

No worries there, just restore the database with the HEADERONLY to view the contents of the backup like name, date, and other useful info. For the file paths specifically, you'd use FILELISTONLY. This would prevent you from hard coding them. More on that here.

CREATE TABLE #DataFiles (LogicalName nvarchar(128)
                        ,PhysicalName nvarchar(260)
                        ,[Type] char(1)
                        ,FileGroupName nvarchar(128) null
                        ,Size numeric(20,0)
                        ,MaxSize numeric(20,0)
                        ,FileID bigint
                        ,CreateLSN numeric(25,0)
                        ,DropLSN numeric(25,0)
                        ,UniqueID uniqueidentifier
                        ,ReadOnlyLSN numeric(25,0) null
                        ,ReadWriteLSN numeric(25,0) null
                        ,BackupSizeInBytes bigint
                        ,SourceBlockSize int
                        ,FileGroupID int
                        ,LogGroupGUID uniqueidentifier null
                        ,DifferentialBaseLSN numeric(25,0) null
                        ,DifferentialBaseGUID uniqueidentifier null
                        ,IsReadOnly bit
                        ,IsPresent bit
                        ,TDEThumbprint varbinary(32) null
                        ,SnapshotURL nvarchar(360) null
                        )
INSERT INTO #DataFiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''E:\DB Backups\YourBackup.bak''')

SELECT LogicalName, PhysicalName FROM #DataFiles

DROP TABLE #DataFiles

edited Jan 4, 2019 at 18:59

answered Jan 3, 2019 at 22:10

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Digit Plays Over a year ago

Ill implement this tomorrow and let you know if this works. thank you for the answer scsimon

S3S Over a year ago

You bet @DigitPlays let me know if you run into issues

Digit Plays Over a year ago

ok, thank you for the help. This would work however I would have to specify both file paths and the mdf/ldf file names. is there a way to change it without specifying that? @scsimon

HardCode Over a year ago

I would also include changing the logical name of the MDF and LDF to match the physical name:

ALTER DATABASE newDB MODIFY FILE  ( NAME = oldDB, NEWNAME = newDB) GO ALTER DATABASE newDB MODIFY FILE  ( NAME = oldDB_log, NEWNAME = newDB_log) GO

Digit Plays Over a year ago

Everything is now working. I can not thank you all enough. I offer up my first born as a sign of gratitude. Thank you very much to scsimon and HardCode. pointing me in the right direction you guys are my frickin heroes.

|

Collectives™ on Stack Overflow

How to restore multiple SQL Server databases that use the same naming scheme for their MDF and LDF files

1 Answer 1

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related