I got a mdf file from my co-worker
who just stopped SQL server and delete ldf file
and give me a mdf file.
if it was detached, it would work fine
but it was just copied.
Is there any way to attach this mdf file?
I tried sp_attach_db and sp_attach_single_file_db
and both failed.
thanks,
I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code:
srv = New Server("MYPCSQLEXPRESS")
db = srv.Databases("washmaster")
Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File)
Dim recoverymod As RecoveryModel
recoverymod = db.DatabaseOptions.RecoveryModel
rs.NoRecovery = False
rs.Devices.Add(bdi)
rs.Database = "washmaster"
rs.ReplaceDatabase = True
srv.KillAllProcesses("washmaster")
rs.SqlRestore(srv)
This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception:
....Cannot open backup device..[filename]...Operating system error 5(Access is denied.)
The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied.
How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way?
Thanks,
SJonesy
I have used the copy database wizard, but I realized I had forgotten to shrink the transaction log file. So I canceled the wizard. My database, detached by the wizard, has now disappeared. The mdf file is still there, but when I try to attach it manually I get the "create file encountered operating system error 5 while attempting to open the physical file..." error.
Any way I can recover it?
Thanks.
I have a customer they are running raid 5 on a windows 2000 server one of the drives went bad. The customer replaced the drive and raid rebuilt the drive, every thing seamed to be fine but there is one database file that cannot be attached to SQL. The file is 15G so I know there is information the error states that the file is not a Primary file. Any clue on how to fix this?
mdf file size 5,738,944 KB
ldf file size 10,176 KB
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J
Hi Neil, thanks for you advice. The reason I asked the question is because in SQL2000, there was an option which would allow you to export to any file type (including excel). This was done by using the DTS Import/Export interface, does this function exisit in SQL2005Expres? Surely?
DW
RESOLVED RESOLVED RESOLVED
DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings
Not great, but better than continuing to bang head against wall.
Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.
===========================================================================
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
The Connection strings are all of the form
connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
how can i attach the mdf file with a corrupted/deleted Log file
my log file is deleted how can i attach the mdf file
I know allot of folks are having this problem and I tried lots of things but nothing works. I understand the problem is coping the SQL Express on another server is the problem - I just not sure what to do?
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
This is the last statement on the Stack Trace:
SqlException (0x80131904): An attempt to attach an auto-named database for file e:wwwdata81d0493fwwwApp_DataTestDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735091
I checked my server forum and they said I had to name a database:
Example: Database=(unique name);
But this didn't work either.
I just tried a simple web project that has only one database and one table in SQL Express with one sqldatasource and one datagrid. It works fine on my pc but when I use the copy function in Visio Studio 2005 Pro - I can't run the site on the remote server: www.myjewelrydirect.com
I tried coping the database manually. I tried disconnecting the database before I copy it. Below is my connection statement:
<connectionStrings>
<add name="TestDB" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|TestDatabase.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
With all the comments in these forums - this must be a bug.
I have been working on this problem for over 2 weeks - HELP!
I have MS SQL server database with two files
1- x_data.mdf, 2- x_log.ldf
The database become suspect and i have no space problem.
now the problem is that the log file become corrupted because when i stop the database service I tray to copy the log file into another place but I can't
Now the result is that I have one data file x_data.mdf and how can I solve the problem
and the question is how can I back the data again when I have a data file without a log file
Thanks
I'm in a BIG BIG trouble...
A co-worker tried to help me out with the huge database huge log file.
So he first detach my db, and deleted the log.ldf file...
now, i tried to attach it back....ERROR...
could someone help me out ... please..
!_! Crying Nyssa.
Hello Experts, How Can i Attach and Detach my MDf file to an instance of SQL server 2005 Programatically or by T-SQL :)
ElmasryA1
Is it possible to attach sdf file (SQL Server Compact Edition Database File) to sql server 2005 ent. edition?
Thanks in advance.
Hi all,
I have "SQL Server Express" as well as SSMS-E installed on the same machine. I am trying to get SSMS-E to connect to a database (i.e., an .MDF file) that resides in a folder on the desktop.
After establishing a connection to my "SQL Server Express" instance within SSMS-E, I right-click on "Databases", in the "Object Explorer" window and select "Attach..." and click the "Add..." button. The resulting dialog box opens in the directory:
"C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData" where I see several MDF files. But as I said, my MDF is not in this directory - it's on the desktop.
If I try to navigate to the proper directory on the desktop I see no MDF files. In fact, I cannot even navigate past my username -- that is, I cannot even navigate to the desktop, let alone folders on the desktop. I don't know why.
Of course I *could* put the MDF file in the SQL-E approved directory but shouldn't I just be able to navigate to it using SSMS-E? Incidentally, I *AM* able to connect to the database on my desktop if I use Visual Studio and I specify "UserInstance=true". Not specifying "UserInstance=true" results in this error:
An attempt to attach an auto-named database for file C:Documents and SettingsRobinDesktopMyCompany.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Can someone explain what might be causing the problem with SSMS-E, and, for that matter, why it's necessary to specify "UserInstance=true" in Visual Studio?
Thanks so much. I hope these are not too many questions.
Robin
Hello Professionals, How can i attach and detach my mdf file to an instanse of sql 2005 programatically or by T-SQL
ElmasryA1
Hello,
I have Sql Server in one pc and I want to attach one databse that is in another computer that doesnt have SQL Server. Sql Server doesnt permit UNC path when attaching database files. Is there anyway to workarounf this. Is Trace Flag 1807 of any use?
Thanks
Hi
I am very inexperienced with the Administration side of SQL Server 2005.
I have been given a .bak file which I need to restore onto my SQL Server. The database .bak file was created on another PC and I cannot seem to restore/recover the .bak file onto my SQL Server.
Can anybody help me with this?
Thanks!!
Dear All,
We have a DB on sql server 2000 64 bit and the build is 2187.
The database consisted of two transaction log files and several data files at the time we took backup. However, the backup taken was a cold backup (backup of files through OS). In this backupset, one transaction log file was skipped due to some error...... Now I want to restore the database for referring to some history data. Will I be able to attach the database with all MDF and one of the transaction log files.??
I heard this kind of recovery is not possible. If anybody can post some solution , I would be grateful...!!
Regards,
Chetan
Hi,I am newbie to SQL Server. I am using SQL Server 2000.I know SQL server compiles the SQL stmt or SP and stores the plan forlater use(I know its not always done. But should be done to reduceexecution time) in memory somewere. I want to capute all the executionplan which are kept in memory. In oracle v$sql_plan can be used toaccess the plan in oracle. I want to know how to do that in SQL Server.I can use profiler, but it contributes some CPU utilization.Thanks,Thiru.WantedToBeDBAWantedToBeDBA {at} gmail {dot} com
View 1 Replies View RelatedDear All...
I want my database file to be attached to remote server database when i published my site
Notes :
Remote Server Database:SqlServer2000
.net Version:1.1
hello all.I have a database file in sqlserver 2005 mode, i set it's compatiblity to sqlserver 2000 (80) but i cant attach it in sqlserver 2000.My host only support sqlserver 2000!I need any help to convert or attach it!Thank all!
View 2 Replies View RelatedHi,
I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).
I have tried restoring and attaching options but nothing works :(
I also get the error saying
"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".
Please this is urgent. WIll be VERY grateful for your urgent reply.
Hi,
I was having problems on myt PC so i did a fresh installation. However I had backed up my SQL database (I only have the MDF file and not the log file).
I have tried restoring and attaching options but nothing works :(
I also get the error saying
"Can not open backup device.... Device error or device off-line. See the SQL server error log for more details. RESTORE database is terminating abnormally".
Please this is really urgent. Will be VERY grateful for your urgent reply.
i resive a error when i run script below i vbscript
What is wrong??
Function InstallDB()
Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Const HKEY_CURRENT_CONFIG = &H80000005
Dim strComputer
Dim objRegistry
Dim strKeyPath
Dim strValueName
Dim strValue
Dim arrValues
Dim intValue
Dim strpath
Dim strpath2
Dim strDBServerName
Dim objSQLServer
strComputer = "."
Set objRegistry = GetObject("winmgmts:\" & strComputer & "
ootdefault: StdRegProv")
'Get String value
strKeyPath = "Software est"
strValueName = "PATH2"
objRegistry.GetStringValue HKEY_CURRENT_USER, strKeyPath, strValueName, strValue
strpath = strValue & "Data est_Data.mdf , " & strValue & "Data est_log.ldf"
strDBServerName = "."
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = False 'sql authetication
objSQLServer.Connect strDBServerName & " est", "sa", "test" MsgBox (strpath)
objSQLServer.AttachDB "test", strpath
End Function
I copied database .mdf and trying to attached to sql 2012 server but it gives error as attached
tried following
1. added user everyone with full control
2. coping .mdf in sql server default data folder
running sql studio manager in adminstrator
In Access 97, I am trying to attach to a table in my SQL Server 2000db.I use File/Get Data, which displays all the SQL tables including theone I can't attach to. (I can attach to all the other SQL tables.)But, when I select that table I get a message that tells me the nameis invalid. I don't understand this because it gives me the name inthe Get Data dialog, and I don't have to enter it manually.Any ideas?Thanks,Bob C.
View 3 Replies View RelatedI have a data file, call it Data.mdf, that I am accessing through Visual Studio. It does not show up in SQL Server 2000 Enterprise Manager.
How can I get it assigned to, or imported into, the local server so I can manage it?
Thanks
Glen
If you are having problems installing SQL Server, including Express Edition, and you previously installed one of the Community Technology Preview (CTP) releases, then read on...
View 2 Replies View RelatedHi All,
we are changing some reports from English to German...some of the reports that we have changed generate the message 'An internal error occurred on the report server. See the error log for more details.'
We go to
C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFiles
On the machine but we cannot find any more detailed error messages...
Does anyone have any idea as to whether the messages migh be written somewhere else?
Thanks and Best Regards
I build a WebApp which i use the default DataBase that come with the App_Data folder.
I have Users and roles into that folder with all my tables regarding my new App.
Why i cant make it work under IIS on my webserver ? When i open it with Visual Studio everything works but outside of it nothing.
An attempt to attach an auto-named database for file c:inetpubwwwrootSurveyApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf;
I look arround and cant find a thing on how to fix my issue. I try deleting the Folder under my User Account for SQL Express and nothing happend.
I've read through all the posts in this fourm that are related to the problem I'm getting with this error message on my main form, but none of the fixes seen to solve the problem. So I hope someone can get me pointed in the right direction.
I'm running visual studio 2005 professional, and SQL Server 2005 Express Edition and this is a desktop application. I added the db file to the solution with the Add New Data Source wizard, I can create the datasets and preview the data correctly.
An attempt to attach an auto-named database for file C:Program FilesMicrosoft Visual Studio 8Common7IDEdbInventory.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
When I run the program the exception is throw in the Settings.Designer.vb code, Public ReadOnly Property for the connection string. The InnerException is this...
{"An error occurred loading a configuration file: Could not find a part of the path 'C:Documents and Settings<UserName><ApplicationName>.vshos_StrongName_1sdf1e34hkn1hqmkn2bgjjwstusfj2sg1.0.0.0user.config'. (C:Documents and Settings<UserName>Application Data...<ApplicationName>.vshos_StrongName_1sdf1e34hkn1hqmkn2bgjjwstusfj2sg1.0.0.0user.config)"}
This folder doesn't exist anywhere on the system, any ideas....
TIA,
I have this report i setup as an agent job in SSMS; It's setup to run every 7 minutes and it only sends the report if data is present. I'd like to add the ability to omit any rows that were sent in the previous report.
This is what the script looks like:
if exists (select o.ord_billto, o.ord_refnum , o.ord_hdrnumber, o.mov_number, o.ord_status, o.ord_cmdvalue, o.ord_startdate
from orderheader o
where ord_billto in ('A','B','C','D')
and DATEDIFF(minute , o.ord_datetaken, GETDATE())<=7
[Code] ....
Also, why I can't seem to use IF ( Select [...]) > 0? When i try using that instead of IF EXISTS i get this error: "Msg 116, Level 16, State 1, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
An attempt to attach an auto-named database for file C:WebApp_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
My web.config file, the connection strings
<connectionStrings>
<add name="POAdatabaseConnectionString1" connectionString="Data Source=sss;Persist Security Info=True;Initial Catalog=POAdatabase.mdf;Integrated Security=SSPI" providerName="System.Data.SqlClient" />
</connectionStrings>
I cann't really find the statement for attaching aspnetdb.mdf. I don't where to find it. Is there any other web.config file in the C drive? Thanks for help!