Hello all,
I'm using SQL 2005 Express edition. I'm currently connecting to the database file directrly (from ASP.NET) and that db file is not attached to the server.
My question is this:
What is the difference ? Will the performance improve if the db is attached and I connect to it throught the server ? I'm really not sure what the difference is.
Thanks
Hello all,
I'm using SQL 2005 Express edition. I'm currently connecting to the database file directrly (from ASP.NET) and that db file is not attached to the server.
My question is this:
What is the difference ? Will the performance improve if the db is attached and I connect to it throught the server ? I'm really not sure what the difference is.
Thanks
Hello everybody,
I have an ASP.NET 2.0 website running on a windows server 2003 - sql express management studio installed. Today I had to make some changes in my database, add some columns, and so on...
So I put the app_offline.htm into the root of the web application, so the IIS unloaded the application domain, attached it in the sql management studio, made the changes I had to do and detached the db. Ok - then I removed the app_offline.html from the root of the application and looked at my internet explorer telling me that the user NT_network... cannot login...
What did I do wrong? I attached it, detached it, and at once something went wrong...
I tried everything, at least I copied the whole application to another directory and.... It worked fine again!
So, can anybody help me, I don`t know what I did wrong or rather how to make changes in my sql express for the future, not having trouble with any security issues...
regards
Simon
I have detached the database via sp_detach_db in a job, (I was trying to use a job to detach the database, then make copies of the mdf file, then attach the databases as different copies, somehow the job failed due to file locked) I tried to delete the physical files, but they were still locked. I tried several times, two of them I was able to delete the mdf files, but not the ldf file. The other one both mdf file and ldf file can not be deleted. I was not able to see the database via Management studio.
Can anyone help?
Thanks
I am using SQL 2012 SE. I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
DECLARE @cmd nvarchar(4000)
[Code] ...
The only message is I see while the query is executing is :Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing.
Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases.
View 2 Replies View Related
Hi
I have a database consisting of multiple files on a remote server.
Both servers have the same user (bob) which is in the administrator group.
The directory that the mdf/ndf files exist on is shared as "nas" with full rights given to the user bob.
They are both in the same workgroup.
SQL server is running as this user (not ideal I know but server and services will be locked down later)
The script I used to create the database uses the following line to enable remote database files:
-- Enable the use of network drive for DB files
DBCC TRACEON(1807)
GO
I used SSMS to detatch the database and then modified my create script by removing database sizes and adding "FOR ATTACH" at the end. (I have also tried using sp_attach_db but no use)
So the script looks like this:
CREATE DATABASE [dbname] ON PRIMARY
( NAME = N'DBName', FILENAME = N'\123.45.67.89asDBName_FG.mdf' , MAXSIZE = UNLIMITED, FILEGROWTH = 10GB ),
-- Filegroups for the Alerts Table and Indexes.
FILEGROUP [FG_DBFile2]
( NAME = N'DBFile2_FG', FILENAME = N'\123.45.67.89asDBFile2_FG.ndf' , MAXSIZE = UNLIMITED, FILEGROWTH = 5GB ),
FILEGROUP [FG_AlertsIndexes]
( NAME = N'DBFile3_FG', FILENAME = N'\123.45.67.89asDBFile3_FG.ndf' , MAXSIZE = UNLIMITED, FILEGROWTH = 5GB )
LOG ON
( NAME = N'DBName_log', FILENAME = N'\123.45.67.89asDBName_log.ldf' ,MAXSIZE = 13GB , FILEGROWTH = 0%)
FOR ATTACH
GO
When I run the script I get the following error:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5120, Level 16, State 101, Line 5
Unable to open the physical file "\123.45.67.89asDBName_FG.mdf". Operating system error 5: "5(error not found)".
I know the permissions are ok as I can map a drive without being prompted.
SQL server is running as the user bob which is the same user I log onto the server as.
I am running Windows 2003 Server R2 Standard Edition on server with SQL Server 2005 SP2.
The NAS box is running SQL Server R2 Storage Edition.
One thing I noticed was that the mdf/ndf files on the NAS box are locked as if SQL is still using them, i.e. I cannot rename them, move them, alter any properties etc. I just get "Access Denied".
I've tried rebooting both servers but still no luck. I've also tried stopping SQL server then renaming the files, but again "Access Denied".
I know there has been a problem in the past detaching and attaching db files on a NAS, but the Hotfix is included in SP2, which I am using.
I hope I have included enough info but if I've missed anything, it can be provided.
(Event Log basically replicates what the error message says.)
Any help or ideas would be greatly appreciated.
Thanks
Steve
Hi, paws.
My server crashed recently, I didn't give to much importance because I knew that having access to the partitions I could recover my DB files and attach my database to my new server, and everything was fine until ... I found that my DTS Packages and Jobs where not in my Attached DB.
Now, where can I find these ?
Can you help a tormented man ?
Thanks in advance.
Francisco J
I feel that I have done something stupid here.I used SSMSE to detach my database so that I could create a copy - no problem here.But then in SSMSE I right-clicked Databases, chose attach and expected to see my original database. But there are no databases shown in the attach window!I have tried to find the original database in SSMSE but nothing I do allows me to see it. However, Windows Explorer shows me that the files are still where they were when I detached them.Please help - this is driving me crazy.ThanksChris
Hello,
I currently have an application that uses a Access/JET database for a desktop application. The application is single-user and outside of a few corrupt MDBs occasionally, the setup is working fine.
I am thinking about switching out from using the Access database to using a SQL Server Express detached database. I'm not sure if this is a "proper" usage of a detached database, but I've verified that I can access and query a detached one...
Is this something that is recommended? Are there any performance issues associated with it (I believe the detached would be faster than the JET database.) What kind of maintainence would be needed for the SQL Server file? (I currently have a "Repair and Compact" option for the Access file...anything along those lines needed?) Is there anything else I should be aware of?
Thank in advance!
Dear All,
i've detached my database. how to attache thesame again?
Vinod
Even you learn 1%, Learn it with 100% confidence.
I ran into a problem today where I had to pull a backup file from an older tape. The file was originally is a backup that was stored in a device like :
EXEC sp_addumpdevice
'disk', --type
'c2000_BackupDevice', --logical name
'\UNCPathackupFile.bak' --physical location
I put the file from the tape on a File Server and then tried to create a new backup device pointing to the file. That's easy enough, but when I try to restore from it I get an device error saying it's offline.
How can I get that device attached to my server and then extract the backup files stored inside it?
Please advise b/c this is an interesting problem I hadn't anticipated and it argues that I should change my backup strategy .
Thanks for any advice/experience you can provide,
alex8675
how can i attached MDF file only without .log and data only
View 6 Replies View RelatedHi,
I was trying to figure out how to back up a database that has been attached to the SQL Express instance. I found on the web this tutorial and tried to use it, however it seems like it cannot find the database. The DB is attached by the web application using AttachDbFilename in the connectionstring.
http://www.sqldbatips.com/showarticle.asp?ID=27
Any ideas?
thanks
Hey!
I need help getting all the users that are "attached" or "allowed" to access a given database and their login information. I do not know if this is even possible but I had tried to do my own research and cant figure it out/find any info on it. Any help is greatly appreciated!
Thank you!
-Teenprogrammer
I attached my database to SQL 2005 but it's showing up as read-only for some reason. I've attached this before just fine in another instance of the Vista OS. Why would it attach differently this time compared to all other times and show read only?
The .mdb and .ldf files are not read only if you right-click and check out their properties.
Hi All
I am in process of moving a SQL 2005 solution from a development box that used local storage to UAT environment with SAN attached storage. The solution uses database snapshots
The database files are on the SAN storage but during testing I was unable to create a Database snapshot on the SAN disk. Creating snapshots on the local disk worked fine.
Is their some restriction/problem in using the database snapshot technology with SAN storage?
Hello,
I was using a SQL Express database for some time now with no problems for a NUnit Test project. No, all of a sudden, I'm getting these problems:
DatabaseProviderTest.TestContainsUser : System.Data.SqlClient.SqlException : Cannot open user default database. Login failed.Login failed for user 'XXXXXX'.
For the SQLEXPRESS database, I checked and I am a user, and I gave myself every permission imaginable. The default database is master, so I don't know why I am getting this problem?
Any ideas? Thanks.
Hi allI have a question concerning sql database mdf files. In the old days I would user a ms access database. This file would be stored with the actual web files and would utilise a dsn connection. I have noted when designing with vwd 2005 express it allows you to use 2 methods of creating a mdf database. You can either create it as an attachment mdf or you can create it directly using sql manager. My question is, if you create the mdf database as an attachement file can you store it in the same manner as if you where using a ms access database, meaning can you store it with the web site's files so it uses the file storage allocated size and then create a connection similar to a dsn (but for sql) to the isp's sql engine or does it have to be uploaded to the isp' s sql server.The reason for this question is some of my customers do not want to pay the extra cost to have an sql allocation, however I do not want to go back to using old asp methods to create advanced sites as I prefer using stored procedures. Any help will be appreciated
View 4 Replies View RelatedWhy in the hell doesn't SQL Server provide a facility for dropping anactive connection on an attached database in SQL Server Managementconsole? I can't detach an attached database because apparently thereis an active connection.I know you can use SSEUTIL but it seems like kluge for a poorlythought out function.Crazy
View 2 Replies View RelatedI am supposed to be documenting the SQL Express 2005 SP2 installation and connection to our application on Vista. I have installed and uninstalled the application several times in trying to create the connection and document the process. I thought I was done but now the database will not attach because it thinks the databasse is already attached. The database is not attached. I even uninstalled it again thinking something was left over.
Among the possibilities are that the database we are trying to attach is from SQL Server 2003 on a Server 2003. I tend not to think it is a server issue but have no reason to make that statement.
I tend to think it is a Vista issue because I have encountered and circumvented several. Our applications work fine on the full version of SQL. I can connect my Vista computer to the servers without issue. We have no Vista Servers other than the workstation I am trying to get SQL Express running on.
I am about out of ideas. Any suggestion will be tried at this point. Brian
Hello,
In my vb.net application, I have an attached database called dbMaster with the following tables:
tblCustomer, tblProducts, tblUsers
In visual studio 2005, is it possible to publish only one table? My problem is, is that I want to send my end users updated data from my tblCustomer, and am finding that on my test laptop, if I publish dbMaster, it sends all tables when I just want one to be sent.
Thanks in advance.
Is it possible to try repair a database without being attached, giving the path to the files?
I'm trying to attach a database that i recover from a disk, and i'm not able to attach, gives me an error, saying i should do a db check.
Hi every body.
As you can see in the title I would like to import my tables from the data base which is in the SQL Server, to an mdf file which can be attached to the project.Can some one give an indice please?
Tran Backup failed. How to find the database for which Tran Backup failed as attached?
View 2 Replies View Relatedour clients have the flexibility to detach and attach databases (I know there are a lot of considerations around this but there is now way of changing it), once they attach a database we need to run some code to update a bunch of values in the database.
Other than creating a SQL Agent job are there any other options available to automatically execute a script once the database has been attached?
I downloaded the SR2 - major mistake Now I cannot get into the sample databases I have on my machine. I can attach to the Servers, but they have SR1 and now I guess if I would make changes from my SR2, then they will be screwed up.
My error when I try to log into my machine (adventure works) is:
TITLE: Connect to Server
------------------------------
Cannot connect to LND620JORRIT.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Is there any way to reverse SR2? Should I reinstall SQL 2005?
Help!
Anke
Today we turned off SQL Server 7, copied the MDF file (a five-month, 245MB project) to the network, reformatted and installed Windows 2000 SBS with SQL Server 2000.
Lo and behold, SQL Server 2000 refuses to attach the MDF file, claiming it cannot find the accompanying LDF (no longer in existence). Using sp_attach_single_file_db did not help, nor did creating a database of the same name on the server, detaching it, and trying to swap the old MDF file for it.
Please help... if I have to get out a hex editor to get this database attached, I'm ready, but nothing I've tried works so far, and this is very important data--I can't believe the log file is THAT important, especially after committing anything left while shutting down the service.
After reboot, some db were detached, some not.
How come?
I feel that I have done something stupid here.
I used SSMSE to detach my database so that I could create a copy - no problem here.
But then in SSMSE I right-clicked Databases, chose attach and expected to see my original database. But there are no databases shown in the attach window!
I have tried to find the original database in SSMSE but nothing I do allows me to see it. However, Windows Explorer shows me that the files are still where they were when I detached them.
Please help - this is driving me crazy.
Thanks
Chris
I had to detach a databse for a project. I am now having trouble trying to find this database. It is no longer listed in the Sql Server management studio with the other databases that haven't been detached.
Is the database moved to some other folder when it is detached? Where is this database now?
thanks
I want to manage my sql express .mdf database file (e.g. set Roles) but I seem to only be working with a temporary instance of my database when I attach to .sqlexpress and manage in SQL Server Management Studio Express (SSMSE). The Role I set is gone when I detach and re-attach.
If I try to manage my file in Visual Studio under Server Explorer, I do not see the "Roles" folder to modify roles.
Any help is appreciated.
I have a T-SQL script which deataches and attaches the database. Now here is what I want to do:
I
want to create a SQL Server Job and schedule it to run at a particular
time of the month to Detach all the databases on my local machine and
*Copy all the deatched databases over a network machine* and then
Attach those Database.
All this should be done in T-SQL. I
already have the T-SQLs for Attaching and Detaching. All I need is to
know the T-SQL (example) which will copy the *Detached Databases* from
mu local computer to my Network Computer. How do I achieve that?
Thanks