restore/Attach not working as expected
-
To All,
I have done many restores or attaches of db's and yet this particular one is not working as it should.... Any/All help is welcomed.
USE [master]
GOCREATE DATABASE tops3 ON
( FILENAME = N'D:\MSSQL\DATA\tops3.mdf' ),
( FILENAME = N'D:\MSSQL\DATA\tops3_3a.ndf' ),
( FILENAME = N'E:\MSSQL\logs\tops3_log.ldf' )
FOR ATTACH
GOMsg 5120, Level 16, State 5, Line 4
Unable to open the physical file "F:\MSSQL\DATA\tops3_3a.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 1813, Level 16, State 2, Line 4
Could not open new database 'tops3'. CREATE DATABASE is aborted.The files are there...the access is available...no security to stop the creation... I know the message is file "NOT" there but believe me it is...all spelling is correct..
Also please notice that its asking for the F drive and the file is on the D:\ drive... but i get the same results regarless what drive i put the file on...its as if it reads the ".mdf" and that tells where the file is located but it wont allow me to redirect. My F:\ drive now is for my tempdb files and when i put it on the F i still get the same error.
Any ideas?
D
DHeath
- DHeath - Wednesday, June 6, 2018 7:49 AM
Usually that error is from the service account not having full control of the folder, files. But the wrong drive is odd. Double check the permissions anyway but maybe try sp_attach_db and see if that works or you get the same errors.
Sue
-
Thanks for the suggestion Sue as its much appreciated....checked all permissions and all is well. tried sp_attach_db and results below
SP_attach_DB 'tops3',
'D:\MSSQL\Data\tops3.mdf',
'D:\MSSQL\Data\tops3_3a.ndf',
'E:\MSSQL\logs\tops3_log.ldf'
GOMsg 5120, Level 16, State 5, Line 16
Unable to open the physical file "F:\MSSQL\DATA\tops3_3a.ndf". Operating system error 2: "2(The system cannot find the file specified.)".
Msg 1813, Level 16, State 2, Line 16
Could not open new database 'tops3'. CREATE DATABASE is aborted.DHeath
-
I assume that before you detached the database, the tops3_3a.ndf file was on the F drive? I can't think why your CREATE DATABASE statement isn't looking for it where you tell it to. Maybe there's a non-printing character in there somewhere that makes SQL Server think it's a different file? What happens if you use the GUI to specify the locations, and then press the Script button?
John
-
John thanks for the reply...
The database was completely removed and i took the old files from a VEEAM restore and now trying to attach them. When the files originally existed they were in the locates of D:\, E:\, and F:\ but now that i want to restore them to a different server. In the GUI it too is looking for the F:\ that is not there.DHeath
-
I can't figure out where it's finding that file. I just checked some database header, boot pages and didn't find file locations.
When you get it looking for the F:\ in the GUI, when does it start looking for it? Once you add the first mdf?Sue
-
Thanks sue..here is some food for thought... this is what i see when i use the GUI but when it run it too errors the same
DHeath
-
Another thought - did you try attaching to a different instance? I'm guessing you get the same thing but worth a try to see if it's the instance.
Sue
-
.
If From BOL CREATE DATABASE (SQL Server Transact-SQL) topic
FOR ATTACH [ WITH < attach_database_option > ] Specifies that the database is created by attaching an existing set of operating system files. There must be a <filespec> entry that specifies the primary file. The only other <filespec> entries required are those for any files that have a different path from when the database was first created or last attached.
where
<filespec> ::= { ( NAME = logical_file_name , FILENAME = { 'os_file_name' | 'filestream_path' } [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ] ) }
The
NAME = logical_file_name
is not optional (without a hack) when the FILENAME of a non-primary file differs from what is in the detached database's sys.database_files.
If you do not know the logical name, temporarily attach the database files where SQL Server expects them to go (F:\MSSQL\Data\tops3_3a.ndf), run
USE [DTC-TOPSDB1]; SELECT * FROM sys.database_files, keep the results handy, detach the database, and use result's returned name as the NAME in the <filespec> of a rewritten CREATE DATABASE ...FOR ATTACH statement that points to the new FILENAME. The <filespec>'s parentheses are also required, while the SIZE, MAXSIZE, and FILEGROWTH clauses are optional (i.e. can be omitted).If temporarily attaching database files where SQL Server expects them to go will not be possible, create a new empty sacrificial/dummy/hack database that has the same number of files and file names as the database that will be attached, take the sacrificial database offline, swap in copies of the database files that you want to attach, put the database in emergency mode, run above SELECT using the sacrificial database, save the SELECT results, make sure you still have copies of the original database files, drop the sacrificial database. and attach using the above CREATE DATABASE ...FOR ATTACH WITH (<filespec>) syntax.
- SoHelpMeCodd - Wednesday, June 6, 2018 8:44 PM
Interesting - that kind of makes sense in one way, but I can't find where it's stated in the documentation. The only thing I found was this:
NAME logical_file_name
Specifies the logical name for the file. NAME is required when FILENAME is specified, except when specifying one of the FOR ATTACH clauses.which kind of contradicts what you said. In another way, what you said doesn't make sense, because it's not always possible to attach files in their original location, especially if that location doesn't exist on the new server. Maybe D will have to try your hack!
John
-
Thank you all for the suggestions...
I am wondering if this has something to do with the fact that its an attach the database to a different server then where it originates from. I cant do a restore from backups this is my only option
So when you request that i look in sys.databases_files its not there because it was not there in the beginning. This is a copy of all files..(mdf ndf ldf )to a test server and then attach them accordingly.THIS could be the problem but i dont know.
D
DHeath
- DHeath - Thursday, June 7, 2018 7:14 AM
Nope, it shouldn't be the case. if you detach and attach a database from an instance to another it should work just fine, if its from a SQL Server earlier version to a later it only would change the database level (database version) from the older to the newer.. and you wouldn't be able to go back.
-
Thanks Alejandro... those were my thoughts as well and you have confirmed my belief.
Oh well i am going to give up on this as i am out of ideas and THANKS TO ALL that have taken time to read and or leave a suggestion.
Greatly appreciated.
D
DHeath
-
RESOLVED........ heres how
Since the files came from a different server..looked at the server of origination Server A and found the database and did a select * from sys.database files and found that the database had 5 files 1mdf 2ndf and 1ldf
Then on Sever B recreated the same database with database with 5 files and totally empty nothing but structure
did the sys.database files and say i had the same "structure" as Server A setup does. THEN...took files 4 and 5 offline...those were the both ndf files while the database was attached.
Now i copied the 3 files i had over the mdf ldf and ndf and then bring the database back online and go and bring the files back online and the files that were offline will not be needed but your database has the correct number of files...
This might create more files but they are NULL and in my case thats ok.Here is a link i found that kinda shows what took place but a co-worker gets the credit for the guidance and info
https://sqlserverscribbles.com/2013/01/17/how-to-bring-the-database-online-when-one-or-more-data-files-is-accidentally-deleted/Thanks AGAIN to all that took time to read or suggest
DHeath
-
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply