January 2, 2020 at 2:26 pm
I need to restore a sql database into a NEW database as I need just one table from it to update another. I have the backup of the mdf file.
I've tried unsuccessfully to attach, point to the mdf file, change the Attach as to Test, then remove the log from the bottom but it fails (says it can't attach log as it's in use (it's trying to attach the original log name)). I've tried creating a new database with the new mdf and ldf names, detached it, then renamed the new mdf and then renamed backup to match, but then can't reattach because it says the primary doesn't match.
I'm running SQL 2014 and have been able to do this easily in other versions.
I then tried changing the path on the bottom screen to a new log, it gives the message that a new one will be created, but then it fails with an Operating System Error 2 (cannot find file). The files are in the SAME location as all the rest of the db files so there is no permission errors (since I can simply create a new database there).
January 2, 2020 at 2:42 pm
Instead of trying to attach the files, have you tried restoring them, with a new database name, using the backup you have of the main database?
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 2, 2020 at 2:51 pm
I only have an mdf as a backup and not an actual sql server generated bak file. How does one restore a database using restore feature from an mdf file?
January 2, 2020 at 2:52 pm
I'm unable to run a backup of my current database as the table I need to fix is already corrupted. This is why I needed to restore to a test database as I only need to fix one table. The only backups we have are server copies thus have the whole mdf as a backup and not SQL generated ones.
January 2, 2020 at 3:07 pm
If the database is corrupted won't the "backup" (I assume this means a copy of the .mdf file) of the file contain the same corrupted data as running a full backup of the database?
Are you trying to put this new test database on the same instance where the original database is? If you have another test environment (i.e. another instance), it may prove easier to try to attach the files (you need both .mdf and .ldf files) in a different location than trying to attach it to the instance where the original database already resides.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 2, 2020 at 3:15 pm
We only have 1 sql instance. I do not have a copy of the ldf file.
January 2, 2020 at 3:19 pm
By corruption I mean deleted data I need back. No, the backup of the mdf file I have has the data that was deleted before the "corruption."
January 2, 2020 at 4:27 pm
As far as I know, this cannot be done. I tried it with an AdventureWorks database I have on my local machine, and it failed with a generic error. And, in my case, the .ldf file was even available. I'm running SQL Server 2019 Developer Edition on my machine, so it should have every available feature.
If you have the .mdf files available wouldn't the .ldf files be available as well? Not that it matters that much, I'm just curious.
I would highly recommend spinning up another instance of SQL Server (even developer edition or SQL Express on your local machine) and try using the older copies of the .mdf/.ldf files to attach it to the separate SQL Server instance.
Beyond that, I don't know what else to suggest. Perhaps another contributor has a solution for you.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 2, 2020 at 5:01 pm
I have a new instance installed. I also cannot attach this mdf file in this new instance as it's still trying to locate the original ldf file (it's looking at the current directory of the original instance's file location and saying it's currently being used). I remove the ldf file (as all the research shows is what I'm supposed to do) it also fails. Same issues as original instance.
When it literally says "a new empty log file will be created" why is it not creating it? I either get cannot attach because it's trying to use the existing location of the ldf from the mdf's info or it says it will create a new one, but it doesn't create it then fails because it can't open it.
January 2, 2020 at 5:11 pm
Created the database manually on a new instance with a new name. Stopped SQL. Renamed new empty database and renamed backup. Started SQL. It's now in "Recovering Pending". Can I still force this database to work just to do some queries on it? What if an ldf file becomes corrupt in the future? It sounds like we'll be dead in the water if this ever happens to our live server.
January 2, 2020 at 5:18 pm
I've been able to get the database in Emergency mode so at least I can run queries on it and export the data I need.
January 2, 2020 at 6:01 pm
Unfortunately now that it's in Emergency mode I cannot connect to any tables in order to update our live table's with a query. Is there NOTHING I can do?
I tried to run this:
ALTER DATABASE VPTest SET SINGLE_USER;
GO
DBCC CHECKDB (VPTest, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
Go
Alter DATABASE VPTest set MULTI_USER;
But STILL receive errors about the old log file. This is nuts.
Msg 5173, Level 16, State 1, Line 3
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
Log file 'V:\Program Files\Microsoft SQL Server\NewInstance\MSSQL\DATA\VPTest_log.ldf' does not match the primary file. It may be from a different database or the log may have been rebuilt previously.
Msg 5123, Level 16, State 1, Line 3
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'V:\Program Files\Microsoft SQL Server\OldInstance\MSSQL\DATA\Viewpoint_log.LDF'.
Msg 5024, Level 16, State 2, Line 3
No entry found for the primary log file in sysfiles1. Could not rebuild the log.
Msg 5028, Level 16, State 2, Line 3
The system could not activate enough of the database to rebuild the log.
Msg 7909, Level 20, State 1, Line 3
The emergency-mode repair failed.You must restore from backup.
January 3, 2020 at 10:34 am
First, you can't do this on a server where the same database already exists. You must be doing this on a new, or different, instance of SQL Server.
Here's how you do it. Scroll down. Use the T-SQL example. It's the clearest and easiest method with very few steps. You will build a new log file, which is what you need to do.
This will only work if the MDF file is intact. If the MDF file is corrupted because you backed up whatever was wrong with the other one, this will fail. If whatever method you used to backup the server wasn't aware that SQL Server has these things called transactions and they don't pause they're writing while the backup software gathers data, this will fail.
The key point is, your backup methodology is utterly flawed. You should not be copying systems as a method of backup. You should be using the SQL Server mechanisms provided, namely BACKUP DATABASE and BACKUP LOG in combination to establish the ability to recover to a point in time.
Best of luck.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2020 at 2:01 pm
Yes it's very clear the backup system is flawed but unfortunately our IT is outsourced and that was all I was given and have access to. I've requested a new array so at least I can create my own SQL backups to use in the future when needed.
I was able to attach with a backup of the mdf and ldf files that I found on a new server.
January 3, 2020 at 2:10 pm
Don't forget, even with a log file, if the backup process ignored SQL Server transactions, the attach could still fail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply