"Restoring" a mdf database file to a new database

Question

I have a SQL Server Express instance and have been doing a file level backup of the whole server twice a day. I was thinking that the file level backup can serve as the backups for the tables. This may be a mistake.

I now need to look at data that is contained in an older database.mdf file. I do not want to restore the data to the production database. I see here about attaching a .mdf file to the server. My .mdf file has the same name as the production database as it is a backup of the file. If I do this what will I end up with?

A new database with the same name?
A new database with a automatic renaming?
My production database overwritten?
Something else?

What would be the best way to "restore" a .mdf file with the same name as a production database to a new database name so I do not overwrite the production database?

Sorry but, what about testing what it does on another instance of SQL Server? Maybe on your local machine or a test machine in your infrastructure. If the only option you have is to name it as the original, i expect the attach procedure to fail on the server where there is already a db called like that — mororo, Aug 13 '19 at 15:13
_This may be a mistake._ Yes, it was. You need to fully understand the disaster recovery options available and to have, in place, a defined, documented, and tested recovery plan. Presumably you have never made a backup of anything within your sql server instance. — SMor, Aug 13 '19 at 15:32
@SMor - You are correct. I have only backed up the entire server at file level with Unitrends. You are correct, I have not used the backup that is provided by the SQL server itself. IIRC the only option I had was a manual option and I do not consider manual options to be adequate as people forget/delay doing the manual backup on time, thus the Unitrends option. We did test file level restores, just not with SQL server databases. I also did not anticipate this scenario of needing to restore into a different database name. What would be a good AUTOMATIC backup option for SQL server? — Eric Snyder, Aug 13 '19 at 18:21
Nope - YOU need to define what your requirements are with respect to disaster recovery. That will lead you to choosing the appropriate recovery model. And that, in turn, will lead you to identifying the backup tasks you need to perform and the schedule to use for them. Once you have decided and implemented this, you document it, you practice it, and you make damn certain that at least 2 people know all about this. — SMor, Aug 13 '19 at 21:22
The express version does not support the agent - which is the functionality one normally uses to automate sql server tasks. MS has a discussion of doing similar things for the express version [here](https://support.microsoft.com/en-us/help/2019698/how-to-schedule-and-automate-backups-of-sql-server-databases-in-sql-se) — SMor, Aug 13 '19 at 21:23

David Browne - Microsoft · Answer 1 · 2019-08-13T18:39:00.257

6

You can attach the files as a database with a different name on the same instance, or with the same name on a different instance. eg copy the files somewhere the SQL Server instance can access them and run something like:

CREATE DATABASE AdventureWorks_copy  
    ON (FILENAME = 'C:\temp\AdventureWorks_Data.mdf'),   
    (FILENAME = 'C:\temp\AdventureWorks_Log.ldf')   
    FOR ATTACH;

https://learn.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-2017

edited Aug 13 '19 at 18:39

answered Aug 13 '19 at 15:26

David Browne - Microsoft

80,331
6
39
67

So if I change the name of the mdf file and attach that file SQL server will attach using the file name and not something inside the file? – Eric Snyder Aug 13 '19 at 18:23
1

You specify the name for the new database and all the files in the `CREATE DATABASE ... FOR ATTACH` statement. The physical file names don't really matter. – David Browne - Microsoft Aug 13 '19 at 18:27

score 0 · Answer 2 · answered Aug 13 '19 at 20:07

When using Microsoft SQL Server Management Studio V18.0.

RBC on Databases and select "Attach".
Browse to the file you want to attach.
When you select the file there is a spot where you can change the name you want the database to have when attached:

After attaching in the example above the database will be named "WhateverYouWant".

If there is already a database with the same name the attach will fail and you will get a link that tells you why.

"Restoring" a mdf database file to a new database

2 Answers2