Search code examples
sql-serverssms

Adding a SQL Server database with the same name SQL Server 2019

I have the following:

DB1 - Live DB QMLive.mdf / OMLive.ldf - Currently Running

DB2 - QMLive.mdf / OMLive.ldf (3 Days Prior from a Backup)

I need to Attach DB 2 as MonthEnd.mdf / MonthEnd.ldf as I do not have a .bak file (had issues with SQL Network Auth on a NAS)

When I rename DB2 Files and try to attach it, I get an error that I can't mount it... I guess the DB2 has the same Name as DB1 stored in a Table or something.

Solution

  • In the following example I created a MyDB (MyDB.mdf + MyDB_log.ldf) Database. Then I made a copy of these files: MyDB-2.mdf + MyDB-2_log.ldf

    I then attached the "-2" files as "MyDB-2" database to SQL Server using this procedure:

    1. Right click on "Database" and select "Attach..."
    2. Add the "MyDB-2.mdf" using the "Add..." button
    3. Manually edit the highlighed sections adding "-2" to "Attach as" and "Current File Path"

    enter image description here

    This way both databases are attached:

    enter image description here