I need to detach a db. When I right click and choose detach the popup box shows 0 connections however, I'm getting the error 3701 cannot detach because it's currently in use. How can I tell what is causing this or what is using the db?
View 3 RepliesQ1.)In SQL Server 2000, is it always possible to use copies of thedata and transaction log files of a database from one server toreattach to a new database on another server, or even to the sameserver, without first detaching from the existing database?Books Online says that detaching a database closes the data and logfiles cleanly. I read some where that some times, it is possible toattach without detaching, if the data and log file are in stablestate.This is what I tried - Tried to copy Northwind.mdf and ldf files usingwindows explorer and it gave an error message that the files are inuse. I shutdown the server and now I could easily copy them. Afterthat I renamed these files and successfully attached them as a newdatabase.Q2.)Is it possible to do the same with large production databases?Q3.) Please explain what goes on during detaching /attaching process.Thanks in advanceRavi
View 1 Replies View RelatedI hope this is the right forum for this question.
I am working with an asp.net project in Visual Web Developer 2005 Express. This is a starter kit project. There is a database that came with the project that I want to open up in the SQL Server 2005 Express management studio so that I can modify it.
I have detached the db in the VWD environment.But, I do not see this db in the db folder with in the SQL Server studio's IDE. I am assuming that I have to attach the database to the SQL Server management studio in order to work with it. Is this the correct way to do this type of thing? Is there a better way of doing it(a stored proc like mentioned below)?
I also have a question about the icons that pertain to this situation. I have noticed two variations of the same icon. I want to be sure I am interpretting these correctly. I am assuming that the icon that is a cylinder representing a db with the little
electrical plug coming out from behind it means that the db is attached. There is another version of this icon that has a red "x" over the plug. I am assuming that this means it is detached. Is this correct?
I have attached and detached dbs in Sql Server Express. Is the process the same for doing this with a project in Visual Web Developer? Is there a stored proc that I can use to detach from the SQL Server 2005 environment after I have made a database and attach this to my project in VWD in one step?
-thanks in advance
Since the "Cannot Open User Default Database (error 4064)" thread is closed, and I had this same error, I'd thought I'd leave the solution that worked for me since I spent about 2 hours researching this problem. After detaching my database, then reattaching it, I couldn't log back in with my login ID. I could get in using the sa account. I could also log in with my login ID if I set the default database to master using SQL Server Management Studio by navigating to Security, Logins, right-clicking my login ID, then setting the default database to master. However, this isn't a solution, because I need my login ID to default to the database I reattached, not master.
One way of resolving this is to set the new Login ID as the owner. Right click the database under Databases in Management Studio, choose Properties, choose Files, then set the owner with the new login ID. I can then connect to the reattached database with my login ID. If I set the owner back to sa, I can't connect with my login ID, I have to use sa. If someone has a solution to relink my new login ID and the user with the same name so I can log in with sa set as the owner, I'd love to hear it. I can't make that work.
BTW, if I go under Logins, right click my new login ID, choose properties, then User Mapping, my reattached database is selected under Users mapped to this login. However, the Users and Default Schema columns are both set to dbo.
--Steve
Hi
I have to migrate a SQL2000 database to SQL2005, I tried to use CopyDatabase Wizard (CDW) but invariably stopped all processes (may be for permissions, but I really I don€™t know) so I decided to detach de database, copy files to new server and attach it to SQL2005, it apparently works well, except for the fact that Schemas where created with the name of the users of SQL2000.
Is that correct?
Could it affect the performance or any other thing?
Is correct to do what I did?
Could be another problem in the recently attached database?
(Compatibility, data loss, corrupted structures, collation or something like that)
The server I am using is in a domain, it has windows server 2003 R2 SP2
@@Vesion returns
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
And in the source server with windows 2000 SP4
@@Vesion returns
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thank you in advance
Any comment will be greatly appreciated
JAPonce
I've been using my DotNetNuke database for weeks on my local PC with SQL Server Express. It attaches as a "User Instance". Basically, I am using all the DotNetNuke defaults. Today I decided to attach the DotNetNuke database in SQL Server Management Studio Express and peek at the tables. I attached the database; then I detached it when I was done. This all went well. But when I tried to open my DotNetNuke web site again I now get a message that it cannot connect. Attaching and detaching the database in SQL Server Management Studio obviously changed something about the database, but I don't know what. My connection string to the database has not changed.
The specific error displayed in the browser is:
ERROR: Could not connect to database specified in connectionString for SqlDataProvider
In event viewer is this message:
Exception information:
Exception type: SqlException
Exception message: Cannot open user default database. Login failed.
Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
My connection string is this:
<add name="SiteSqlServer" connectionString="Data Source=.SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;" providerName="System.Data.SqlClient" />
Any suggestions about how I can correct this?
Hi
I have tried to restore on db to another on different servers. Unfortunatly the sort orders dont match. Does anyone out there have any articles or know how to attach the the source db data and log files to the destination db. I would appreciate any assistance.
Thanks
D
I understand that you must sp_detach_db and sp_attach_db to move databases to a new server. What if you want to reinstall SQL 7.0 to a different drive on the same server? My situation is this:
We have C:, D:, and E: drives. I accidentally installed SQL 7 on the C: drive instead of the D: drive. I need to reinstall on D: and I need the data. Any suggestions?
Alan
Hello all
From time to time I receive mssql server databases to export from our customers. Last time I deleted the database files without detaching them. How can I detached them, since I need to atach new ones with the same name?
I'm using MS SQL Server 2003 and the databases are 6.5.
Thank you all.
Regards.
Hi,
Im not new to SQL Server but I am new to v.2005. We have done a class project and to finish up I am creating schemas, logins and different user roles and permissions.
I now need to detach the DB and send it to our team leader for submission. I'm scared to do this because I don't know if all the logins and passwords I've created will appear in it's new location. Will they be there in the new server's security folder just as I created them or will they dissapear into the ether?
Signed:
Sweating very much
It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity
Anybody know if it's possible to re-attach a readonly database without making it operational? I'm using log shipping to keep my hot standby current, but would like to take the detached db and duplicate it elsewhere, I just cant re-attach it as read-only again when I'm done. Thanks!
View 2 Replies View RelatedWhy does it take time to detach or attach a Db sometimes ?
View 4 Replies View RelatedI am trying to detatch a database that is set up as a publisher. I have desabled the replication tasks on both the publisher and subscriber db and am still unable to detach the db. Any ideas?
The image below is an example of the message I receive. I have tried after hours so at that time the users are 0.
Thanks.
I am trying to detatch a database that is set up as a publisher. I have desabled the replication tasks on both the publisher and subscriber db and am still unable to detach the db. Any ideas?
The image below is an example of the message I receive. I have tried after hours so at that time the users are 0.
Thanks.
Not sure if this is the exact forum I need but here goes.
Having a bit of an issue 'moving' a database. I've only just installed SQL Server 2005 on a Development/Test box so I can learn and mess around with it without doing any serious damage to anything important.
One of the first things I've done is install the AdventureWorks database and, having installed it, decided I would move the files to their optimum locations. However having detached the database I can't seem to move or copy the .mdf or .ldf files - something still seems to have its tentacles around them - 'Access denied' error. Even rebooting the server doesn't work. So I've had to delete then re-attach the database. The question then is does Detach serve any useful purpose? Or am am doing something wrong? With SS2000 I could detach a database, relocate the physical files and re-attach in seconds. The same procedure with SS2005 has taken me an hour!
Regards,
Gordon F.
I am currently having a problem in the implementation of a Database for my C#.net winform application.
Look at the 2 diagrams below:
Diagram 1 : A typical databse design where it usees only 1 mdf file.
Diagram 2: A new proposed approach to implement the Database with multiple mdf files of the same schema!!
In the new proposal of the DB, each mdf files is kept separately and an additional module is required to attach and detach these databases as and when required. The purpose: the performance of all actions on the small data is not affected by the size of the large data
How does this 2 methods measure up in terms of resource usage, performance, etc??
Need comments and views. Thanks.
All,
I wanted to to detach a DB say MyDB from MSDE and wanted to attach it to SQL Express.
It could able to Detach the DB from MSDE,but attachment to SQL 2005 is failing. When I verified MSDE Instance through Managment studio "MyDB" wasnot there ; means it got detached.I couldnt find the DB in SQL 2005. When I tried manually through command prompt I was able to attach "MyDB" to SQL2005.
Please help me out!!
Here is the code which I have written.
if SUCCEEDED(hr = CoCreateInstance (CLSID_SQLDMOServer, NULL, CLSCTX_INPROC_SERVER,
IID_ISQLDMOServer, (LPVOID*)&pSQLServer))
{
pSQLServer->SetLoginSecure (TRUE);
SQLDMO_LPCSTR DB = OLESTR("MyDB");
//Connect to MSDE First
if SUCCEEDED(hr = pSQLServer->Connect (szDbServer, szUserName, szPassword))
{
pSQLServer->DetachDB(DB,lpszResult); //Detach the DB
pSQLServer->DisConnect(); //Disconnect MSDE
LPBSTR lpszResult2 = NULL;
long lNumDB;
//Connect to SQL 2005
if SUCCEEDED(hr = pSQLServer->Connect (szSQLExpDbServer, szUserName, szPassword))
{
//LPCOLESTR lpo = A2COLE(sTemp);
//sTemp.operator LPCTSTR
pSQLServer->GetDatabaseCount (&lNumDB);
SQLDMO_LPCSTR files = OLESTR("C:\Program Files\Microsoft SQL Server\MSSQLData\MyDB.mdf,C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDB_log.ldf");
pSQLServer->AttachDB(DB,files,lpszResult2); // Attach the DB to SQL 2005
pSQLServer->DisConnect(); // Disconnect SQL 2005}
what is the difference between these two db tasks in mgt studio?
View 7 Replies View Related
I have SQL Express 2005 SP2 installed in my Vista Home Premium. I am using SQL Server Management Studio Express to manage .mdf.
When I click "Add..." to attach .mdf database, I have this prompt error message :
The server principal "My-PCMyName" is not able to access the database "model" under the current security context. (Microsoft SQL Server, Error: 916)
I have elevated the rights, "Run as administrator", of my SQL Server Management Studio Express, but still having problem.
I also encounter security error when detaching the existing .mdf :
The EXECUTE permission was denied on the object 'sp_detach_db', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Please advice. Thanks.
We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message
System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)
Please note we left the DataBaseA in the old SQL2000 server.
Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup
Many Thanks
I have database on localhost and i want to show this data on my website. I want to create a database online and want to sync with Local Host. Can it be possible syncing data automatically after some interval?
View 6 Replies View Relatedyes,I have an error, like 'The database file may be corrupted. Run the repair utility to check the database file. [ Database name = SDMMC Storage Cardwinpos_2005WINPOS2005.sdf ]' .I develope a program for Pocket Pcs and this program's database sometimes corrupt.what can i do?please help me
View 4 Replies View RelatedI want to create a duplicate database in sql 2000 using asp.net from a webform
I created a database using CREATE DATABASE .......
But how to copy tables, views, stored procedures to newly created
database from old using asp.net from webform
Is there any another method to create a duplicate database with another name
from existing database on same server ?
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
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
Hi all,
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.InsertCustomers
GO
CREATE PROCEDURE dbo.InsertCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
SET NOCOUNT OFF;
INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax);
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.UpdateCustomers
GO
CREATE PROCEDURE dbo.UpdateCustomers
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24),
@Original_CustomerID nchar(5)
)
AS
SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID));
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
====================================================================================
I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name.
Make sure that the name is entered correctly.
===============================================================================================================
I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
Thanks in advance,
Scott Chang
hii want to copy one database table to onther database table using script?my database is ms-sql server 2000
View 4 Replies View RelatedI recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies View RelatedWe have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies View RelatedI had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB]
MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf')
GO
I created the db with the attached script and I am able to access ituntil I reboot the server. I've tried enabling flag 1807 via the SQLserver service and the startup parameters of the instance. In allcases the database always come up suspect after a reboot. There wasone instance where I was able to recover, but I am not sure how thathappened.Does anyone have an idea of how I can reboot the server without thedatabase becomming suspect?USE MASTERGODBCC TRACEON(1807)GO--DBCC TRACEOFF(1807)--DBCC TRACESTATUS(1807)GOCREATE DATABASE ReadyNAS ON( NAME = ReadyNAS_Data,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Data.mdf',SIZE = 100MB,MAXSIZE = 20GB,FILEGROWTH = 20MB)LOG ON ( NAME = ReadyNAS_Log,FILENAME = '\NAS1NASDiskSQL ServerReadyNASReadyNAS_Log.ldf',SIZE = 20MB,MAXSIZE = 100MB,FILEGROWTH = 10MB)
View 5 Replies View RelatedHi all,
I downloaded and ran AdventureWorks.msi into my SQL Server Management Studio Express (SSMSE) one year ago.But I did not know how to attach it to my SSMSE then. Last week, I deleted it from the "Add or Remove" of Control Panel and I downloaded the new AdventureWork.msi and installed it my SSMSE. Today, I tried to use the Database Explorer of VB 2005 Express for the first Stored Procedure programming. I clicked on AdventureWorks.mdf and I got the following error: One or more files do not match the primary 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 corrupt and should be restored from a backup. Cannot open user default database. Login failed. Login failed for user 'CENADe1enxshc'. Log file 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAdventureWorks_Data_log.ldf' does not match the primary file. It may be from a different database of the log may have been rebuilt previously. Please help and advise me how to correct this problem.
Thanks,
Scott Chang