1

I recently offered to help a friend with some development. She isn't a technical person and it turned out what she needed was more a DBA/SQL developer. She gave me the IP and password to this server.

There is no development server. Only production. So I have to be extra careful with everything I do.

I accidentally altered a stored procedure and now I'm trying to get it back. I had the old employees send me the lastest files. They were in rar format and after I unzipped the DB folder, there was only a file inside(no extension). Looking at the contents led me to believe it is a SQL Server .mdf file. This leads me to my question.

Can I attach this mdf file to a new database I just created? Without it trying to take down the other instance? It seems like I could but since this is production, I have to be EXTRA careful.

The server is Windows Server 2008 R2, with SQL Server Management Studio 2012 installed.

Improve this question
2
  • You have already learned the lesson, but make sure you script your db objects to avoid a situation like this in the future.
    – jj.
    Commented Dec 2, 2014 at 18:05
  • @jj you are correct
    – IEnjoyEatingVegetables
    Commented Dec 10, 2014 at 23:42

2 Answers 2

Reset to default
3

You can first check to make sure it actually is a valid MDF file using DBCC CHECKPRIMARYFILE. It is an undocumented command but can be useful at times.

If that turns up nothing or shows that it is not a mdf file you can use the RESTORE HEADERONLY to see if it might be a backup file.

To your specific question, yes you can attach this as a different database name as long as the physical file is not in the same directory on the server as the original. So in your case I would just create a sub-directory in your data folder for this file and the future LDF file that would be created.

Improve this answer
2
  • Shawn, turns out I had saved the stored procedure and didn't have to use the mdf they provided me. I followed your link to DBCC CHECKPRIMARYFILE, and read up anyways. Thanks. I do have another question though... SSMS is unable to create a database diagram for the main database. My question is, "Can I create a new database with that mdf?". I created a mock database, to see if the issue of not being able to create a diagram was only for the main database. Sure enough, you can create diagrams for new databases. SO how could I copy the current db to a new one, so I can extract the diagram?
    – IEnjoyEatingVegetables
    Commented Dec 6, 2014 at 18:41
  • 1
    I would take the error or information you are getting when trying to create diagrams and post as a new question.
    – user507
    Commented Dec 6, 2014 at 19:11
0

The answer is yes ONLY if the files are not in use.

USE [master] GO CREATE DATABASE [YourDataBase] ON ( FILENAME = N'Path....\FileName1_Data.MDF' ),-- Data files (mdf/ndf) ( FILENAME = N'Path....\FileName2_log.ldf' ) -- log files (ldf) FOR ATTACH GO

Improve this answer
2
  • 1
    If you read OP question he asked Can I attach this mdf file to a new database I just created? the answer should be no you cannot and in fact you should not. You must attach it as different database whose name is not same as prod
    – Shanky
    Commented Nov 30, 2014 at 19:35
  • As Shanky mentioned, you can't attach to an existing database, but you can create a new one and use the mdf/ldf as the source files like @itzik Paz described. If you only have the mdf, you'll need to use CREATE DATABASE [NewDBName] ... FOR ATTACH_REBUILD_LOG. Also, go grab the trial, you can use it for 180 days free or get a dev copy for your own use. It should only cost $60 or you can build a new VM every 6 months... or at least get sql express which will work with DBs up to 10 GB.
    – Your_comment_is_not_funny
    Commented Dec 1, 2014 at 17:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.