Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Lookup something on old MDF file

Hi Experts,

I'm trying to restore a database from a backup of MDF file, but not looking to replace the existing database but instead I want this as copy of the DB, as the purpose is to lookup something back dated.

Tried simple restore method and got the attached error after selecting that file.

How can that be done?

Thanks
Untitled.png
Avatar of Bill Prew
Bill Prew

Sounds like another process has access to the file, at least from the error you are getting.  You will have to track down what that is and terminate it.  If this is just on your local computer a reboot may solve it.  Also, since you are in the Program Files area, you may need elevated privs if Access is trying to write to that folder...

Hi,

As it looks, it seems that you're trying to restore an actual database, not a backup. In this case, try to use attach instead of restore.

1) Don't use the gui for db restores, of any kind.


2) You need to specify a different db name AND new directory locations for every file to be restored.  It's also a good idea to use different file names (although if they are in a different directory path, it's not technically required).  You do all that by using MOVE clauses on the RESTORE command, like this:


RESTORE DATABASE db_name_restore

FROM DISK = 'x:\full\path\to\db\backup\db_name.BAK'

WITH MOVE 'logical_file_name1' TO 'x:\full\path\to\primary\file\db_name_restore.mdf',

    MOVE 'logical_file_name2' TO 'x:\full\path\to\log\file\db_name_restore_log.ldf'


You can get the logical file names using the command:


RESTORE FILELISTONLY

FROM DISK = 'x:\full\path\to\db\backup\db_name.BAK'


The first column contains the logical file name.  You must use that name in the MOVE clause.  And you must do a separate MOVE clause for every file in the db.



Hi

I agree with Scott.

I always script my restores.

I mostly run the restore headeronly and restore filelistonly against the backup file to just see what I've got, even if I know the system fairly well.

If the restoring to another databasename other than the one backed up or on another SQL instance you may need to add the replace clause, as in

restore ...
from disk ...
with
    replace
    , move '' to ''
    , move '' to ''
    , stats = 10
;

I've added the stats clause as I like to track the progress of the restore.

HTH
  David
Avatar of bfuchs

ASKER

Hi,

Sounds like another process has access to the file

This doesn't make sense as its was just retrieved from a backup folder, nobody should have access to that.

In this case, try to use attach instead of restorequote
Also thought so, but getting similar error attached.

@Scott, David,

As Eduard pointed out, I'm not trying to restore current DB and don't want do that, just want attach the MDF file in order to lookup something.

Can you please post exact code I need to run for MDF file (not BAK).

Thanks,
Ben
Untitled.png

CREATE DATABASE database_name_new

ON ( NAME = database_name_new, FILENAME =  'x:\full\path\to\primary\file\db_name.mdf', FILEGROWTH = 30MB )

FOR ATTACH_REBUILD_LOG


The logical_file_name above, NAME = <logical_file_name>, can be any name you want.




Avatar of bfuchs

ASKER

@Bill,

Actually you were right, I've mistakenly chosen a wrong file...

Now I'm selecting the correct file, is it okay to run the attach command (under SSMS attach)?

Thanks,
Ben
Untitled1.png

Hi,

You can read more about:

https://blog.sqlauthority.com/2010/04/26/sql-server-attach-mdf-file-without-ldf-file-in-database/

Avatar of bfuchs

ASKER

@Scott,

Tried the following

CREATE DATABASE database_name_new 

ON ( NAME = 'BackupOfSNVNDec29', FILENAME =  'C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\Dec 29 restore\NursingVisitNotes_Data.mdf', FILEGROWTH = 30MB )

FOR ATTACH_REBUILD_LOG

Open in new window


Got the below

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\NursingVisitNotes_Log.ldf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\NursingVisitNotes_Log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'database_name_new'. CREATE DATABASE is aborted.

Open in new window


Thanks,
Ben
Avatar of bfuchs

ASKER

@Eduard,

Before I try anything that is being suggested there, I want make sure I am not overwriting the current DB!

Is there a way to guarantee that (like leave an open a SQL query based on some table there...)?

Thanks,
Ben

Hi,

Try to move the MDF file in a different location as you'll need at least administrator rights to access this folder (or start SSMS as administrator).

Regarding the name, as long as you're specifying a different name it should be no problem. 

You'll need to execute the sp_attach_db stored procedure with the desired name and the physical file path as parameters

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-db-transact-sql?view=sql-server-ver15


There is also possible to use sp_attach_single_file_db if it suits better for you

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-attach-single-file-db-transact-sql?view=sql-server-ver15



Avatar of bfuchs

ASKER

Hi,

File was on a folder set as read-only, I asked admin to remove that, but now I'm getting error below on both create and attach command.

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\NursingVisitNotes_Log.ldf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\NursingVisitNotes_Log.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'BackupOfSNVNDec29'. CREATE DATABASE is aborted.

Thanks,
Ben
Avatar of bfuchs

ASKER

Have tried these two.

CREATE DATABASE database_name_new 

ON ( NAME = 'BackupOfSNVNDec29', FILENAME =  'C:\Temp\NursingVisitNotes_Data.mdf', FILEGROWTH = 30MB )

FOR ATTACH_REBUILD_LOG




sp_attach_single_file_db  @dbname=  'BackupOfSNVNDec29'      ,  @physname=  'C:\Temp\NursingVisitNotes_Data.mdf' 

Open in new window


Thanks,
Ben

Hi,

The cause can be also related to the missing rights on the folder used by MS SQL for storing the databases, as long as some system tables will be updated.

The error message is related to which from the 2 SQL queries?

Avatar of bfuchs

ASKER

The error message is related to which from the 2 SQL queries?
Both.

The cause can be also related to the missing rights on the folder used by MS SQL for storing the databases

What should I do now?

Thanks,
Ben
Avatar of bfuchs

ASKER

Is it possible its due to the missing log file?

BTW, Just tried running SSMS as admin, same issue.

Thanks,
Ben

You need to make sure you attach the original mdf.  A failed attachment modifies the file, which often renders it invalid for a subsequent attach attempt.  


If you're using the same copy of the file, then copy the original file to a valid location, then try to attach it.

Avatar of bfuchs

ASKER

You need to make sure you attach the original mdf
I'm trying to lookup something on a backup file, meaning we realized something was changed and I want to see when this change took place.

We used to have a scheduled backup (Bak file) but for long time it didn't run.
Therefore I requested a backup from the MDF file that is being done on a daily bases on entire SQL data folder, this MDF file in Q is from one month ago Dec 29.

Thanks,
Ben
Avatar of bfuchs

ASKER

Hi Experts,


I'm requesting now the LDF file associated with that MDF.

Do you think that will solve the issue?

Thanks,
Ben

It should.  As long as it's a "clean" copy of the .LDF.

Avatar of bfuchs

ASKER

I've both now the MDF and the LDF but its still now working...

From the error message it looks like its still trying to access the file in its original location

"C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\NursingVisitNotes_Log.ldf"

What should I do in order to change that path?


As long as it's a "clean" copy of the .LDF.
What means a clean copy?

Thanks,
Ben

What should I do in order to change that path?


Use the [WITH] MOVE clauses that I specified earlier.  If you're doing it thru the gui, I don't have a clue, someone else would have to help you with that, I've never done a restore thru the gui.


What means a clean copy?

The log file was not somehow corrupted, such as a bad db shutdown or something.  Also, it should be a copy of the .LDF as it existed BEFORE an attach was attempted.  If an attach attempt fails, you need to copy over the original files again before attempting another attach.  The attach attempt itself can modify the .MDF and .LDF files (it will modify the master .MDF file, it may or may not modify the .LDF file).

Avatar of bfuchs

ASKER

Are you referring to this comment?

There you have specified a Bak file name which I currently dont have for that date.

If you're doing it thru the gui,
I've absolutely no preference, just will use the first method that works.

The log file was not somehow corrupted, such as a bad db shutdown or something.
Nothing that I know of.

Also, it should be a copy of the .LDF as it existed BEFORE an attach was attempted
Yes, this is it.

Again, my current db name is SkilledNursingVisitNote
File names are
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\NursingVisitNotes_Data.mdf"
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\NursingVisitNotes_Log.ldf"

Backup file names are
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\Dec 29 restore\NursingVisitNotes_Data.mdf"
"C:\Program Files\Microsoft SQL Server\MSSQL10_50.EXP08\MSSQL\DATA\Dec 29 restore\NursingVisitNotes_Log.ldf"

Please post exact code ready for copy/paste.

P.S. As mentioned my main concern is not to override the existing DB!!


Thanks,
Ben
Avatar of bfuchs

ASKER

Just updating...

I've tried the following

1- Killing all active processes from the server, restarted SQL server service, restarted SSMS.
2- Copying those files into diff folder, diff drive, another server...
3- Removing spaces and digits from folder names
4- Renaming data file and log names
5- Referring to remote server by IP address
6- Opening SSMS as admin
7- Executing SSMS from another PC, not the server.
8- Some combinations of all those...

So far nothing seems to work...lets see who can do the magic!!

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Thank you!

So what was the issue previous attempts didn't work?

Were they done thru the gui or with a SQL command?


If thru the gui, could be a dozen different things, it's complex to get set up perfectly.


If thru the command, my best guess is that the wrong filename was used or the same file was used after a failed ATTACH attempt.  


Or, if you tried to do attach with log rebuild, SQL couldn't rebuild the log because the db needed rolled back / rolled forward first.  Once you attached the actual log file, that problem went away, because SQL had the current log to do any needed roll back / roll forward.

The good news is that the command will work again in the future, exactly as is.  If you'd used the gui, you would still have to make sure you re-did everything on the screen exactly like you did before: ugh!  Iow, it's kinda hard to copy, paste and reuse for a gui :-) :-).