hi
Please help me .I have detached a database in sql server 2005 using object browser. Now i cant see the database in object browser of sql server management studio .I tried to attach it but the mdf file of that database is missing in the data folder. i had not created the backup.
Can any one help me how to reattach that database.I will be very thankful to him..
thanks !!
Ajeet
Ajeet Kumar
Software Developer
Ustech
India
Hello Everybody ..
I am trying to detach and attach a database on SQL 7.0 Server with SP 2 through Enterprise manager . However if i right click o the Database and go to all task , i see the ' detach database' greyed out .
The same thing is seen for ' attach Database 'when i try to attach the database .
Although it can done through T-SQL commands also , does any body know the reason why this field is greyed out ?
Does it depend on the SQL 7.0 Service pack or any kind of special permissions ? The same is visible on SQL 2000 Server Enterprise Manager .
Has anybody come across this problem ?
Many thanks in advance
Yatin.
How do you detach a database on one server and attach it to another
A good link will help
Thankx in advance
Is it possible to detach a SQL 7.0 database without removing the database from the server??
View 1 Replies View RelatedI've just created ASPNETDB database with ASP.NET Security. Now, I want to send this db to orther computer.First, I detached this db, then when I used attach database in that computer, there is an error :Error 602: Could not find row in sysindexes for database ID 8, object ID 1, indext ID 1. RUN DBCC CHECKTABLE on sysindexes. Please help me .Thank.
View 3 Replies View RelatedIn SQL when I right click on Databases the option to use Attach/Detach is greyed out. I don't understand why. I'm running SQL 7 on Win2000 server and I have Win2k at sp2 and SQL at sp2.
Help please!
Chris
I need some insight if possible. I installed a new server (to replace an aging machine) with SQL 2000. After installing SQL on the new server I tried to detach a database from the old server so that I can attach it to the new instance of SQL. My problem is that when I do the process I am stopped with an error message.
“Error 8146: procedure sp_detach_db has no parameters and arguments supplied.”
This is happening with all of the user databases on the old server.
I have tried to run this using both the Query Analyzer and from the right-click menu option and both return the same error message. Any help would be greatly appreciated.
Thanks.
An original database (eg A.mdf) is imported/exported to a different server as named B.mdf. The detach of A.mdf was unitentionally not done. It seems that both the database were updated.
A.mdf - 123000KB 25/12/2003
A_log.ldf - 14000KB 25/12/2003
B.mdf - 67000KB 25/12/2003
B_log.ldf - 1024KB 25/12/2003
In this case, which mdf & ldf should be the correct database?
If I I remove A.mdf database by "detach", will B.mdf database work?
SQL server 2000 and SP3 installed.
Assistance is appreciated
I'm trying to detach a database and reattach to another server. When trying to detach it using Enterprise Manager, the command is greyed out. I've tried taking the database offline first but the command is still not enabled. I'm logged in as sysadmin. What gives?
View 7 Replies View RelatedI open enterprise manager , right click and choose detach detach database.
I see line : connections using this database.
I think that this is connection from client connect to database.I feel that connection
disappear automatically after time interval.
I want to know how long time ?
Thank you very much.
I have the need to detach a database from code, at the moment I am using 'SSEUTIL -d' for this in my code and it works OK but now I want to change to using an SQLCommand. Here is my code so far:
Code Snippet
Dim conDatabase As SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=MyMDFPath"; Integrated Security=True;Connect Timeout=60;User Instance=True")
Dim comDetach As New SqlCommand("EXEC master.dbo.sp_detach_db '" + MyMDFPath + "'", conDatabase)
conDatabase.Open()
comDetach.ExecuteNonQuery()
conDatabase.Close()
However, when I execute this I always get the following exception:
Code Snippet
Cannot detach the database 'MyMDFPath' because it is currently in use.
It seems obvious to me that the problem is that to do the detach I create a connection - how do i get around this?
Thanks!
There are two databases on two web servers, db01 is on server01, db01_replica is on server02, db02 is on server02, db02_replica is on server01. db01 and db02 are both for one system. Each time after doing performance test, I have to recover databses. I copy the data files in a folder, try to use detach and attach to recover databases. But with two replication dbs, I don't know how to do it. The replication db should also be recovered.
View 1 Replies View RelatedHi all,
I'm trying to detach a database and after archive it for deliver to other server.
The detach wents fine and the database is removed from the database tree list in Management Studio. The problem is that the mdf file is being locked by some process (maybe SQL) and I can't imagine why. Here is the code for this operation:
USE master;
GO
ALTER DATABASE IMS_MCK_MIS SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE IMS_MCK_MIS SET EMERGENCY
GO
DECLARE @strDate NVARCHAR(8)
DECLARE @strCmd NVARCHAR(255)
SELECT @strDate = CONVERT(nvarchar(8), GETDATE(),112)
EXEC sp_detach_db 'IMS_MCK_MIS'
SELECT @strCmd = 'wzzip -ex -m C:Deliveryarchive_' + @strDate + '.zip E:DBSQLIMS_MCK_MIS.mdf'
exec master..xp_cmdshell @strCmd
Anyone have any idea how to quickly free the mdf file?
Thanks and regards
CG
If I detach a database on a server, then attach the database with asingle file only using the data file...will I lose any transactionsthat were in the original log file? There is a debate going on here atwork where some people think that during the detach, transactions inthe log file are saved to the data file.Thanks!
View 4 Replies View RelatedEverything I read about sp_resetstatus says you must restart SQL after. Isthere a way around that (don't want to shut down production). The suspectdb is just an empty db that is not needed. I need to create another db withthe same name.Thanks.
View 3 Replies View RelatedHi, all experts here,
Thank you for your kind attention.
I am having a question on detach/attach database. As when we detach/attach database, we have to copy both of .mdf and .ldf files to the targeted filepaths, in this case, when the database is large, then it takes a pretty while to copy the physical files though. In this case, I dont really see the point to detach/attach the database? Instead, backup/restore may be faster? (also, as detach the database, the files has to be taken offline)
Just confused about the way we trasfer database files with detach/attach. I am looking forward to hearing from you shortly for any advices for that.
With best regards,
Yours sincerely,
Need help
I made a mistake an attached a read only database to the server (now it is grey marked).
When I try to select this read only database in the MS SQL Server Management Studio, the server hangs up.
I removed the read only attribute from the .mdf and .ldf File and made a reboot of the server. Still the same problem.
How can I detach this read only database or how can I set the attribute to read/write (always hangs up the server, when I try to access this db)
I tried also:
alter database readOnly_dbname set read_write
or
USE master;
GO
EXEC sp_dboption 'readOnly_dbname', 'read only', 'FALSE';
Many thanks for an answer.
Kusi
This might sound dumb, but BOL says you can use detach/attach to "MOVE" a database but can you detach and copy the files to a new server, leaving the files on the original and running the attach on the new and the old? (effectually a way to copy)? I know how to use data transformations/import,export was curious?
Help appreciated!!
I have similar question like Tim asked but from a different angle. I wish I pose my question here too anybody can help answer this.
I tried to use detach and attach method to do backup of the user database (not master, maybe we can do master too) on another server, but it looks the login and permission credential would not go together. I come to see different kind of message I tried to login as non-sa user after I attatch the database on different machine. I would be denied for SQL timeout. It looks like loginand user dose not exist in new server because I did not move master database too. But even if I recreate the login when I tried to associate user with login I will see a message saying user or role already exist in database. If I go back to check the user it did not show up on enterprize manager on that database.
So my question would be
1). how to move login and user together with user database.
2) do we have to move master database where the login infor. reside
3). if so how to move master database from one server to another when other server already have master database exist?
Hope somebody can answer this.
Wislon
Hi,
Iam facing problems to connect to the new database after detach the aspnetdb.Simply the new database disappear (In SQL 2005 Management studio express the database contents disappear ! and I get an error when try to open it).If I try to connectto aspnetdb again I get an error telling me it is used by another process.I don't understand what is going on could any one explain to me?
I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
Peter
I am using the following C# code to establish a SQL connect to a SQL database file:
// connection string
// attach a SQL database file to a local SQL server express instance
string _connectionString = @"Server=.SQLExpress; AttachDbFilename=C:BalanceDatabase_1.mdf; Trusted_Connection=Yes; User Instance=True";
// using System.Data.SqlClient;
SqlConnection _sqlConnection = new SqlConnection(_connectionString);
// open the connection
_sqlConnection.Open();
// do something
// close the connection
_sqlConnection.Close();
So far, the connection works fine.
However, next, I want to copy the database file to another folder. So the following codes:
// source database file name
string sourceDatabaseFileName = @"C:BalanceDatabase_1.mdf";
// target database file name
string targetDatabaseFileName = @"D:BalanceDatabase_1.mdf";
// copy database file
System.IO.File.Copy(sourceDatabaseFileName, targetDatabaseFileName, true);
Then the program came with runtime exception: "IOException was unhandled: The process cannot access the file 'C:BalanceDatabase_1.mdf' because it is being used by another process."
Is it because the database file was sill attached to the local SQL Server express instance? What can I do to bypass this problem? Detach the database file? or dispose the local SQL Server express instance?
Many thanks indeed!
I am trying to detach an SQL7 db and reattach it to an SQL2K server. When I run sp_who I get a list of 10-15 processes that are running, most under my sa login. If I kill all the others and then try to run sp_detach_db I get the following:
Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'snb01' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have detached and reattached before, but I don't recall what I did differently. Pls help. TIA D. Lewis
Hi , anyone know of a tsql script to update and detach all user databases in one hit. i.e without naming each individually?
Cheers
Hi,
We have drive running out of space where the data and log file reside for the database in question on the server. I am trying to detach the files and move the log file to the different drive.
I am getting the error message that database can not be dropped as it is in use.
sp_detach_db 'order_management'
Server: Msg 3702, Level 16, State 1, Line 0
Cannot drop the database 'order_management' because it is currently in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Actually no one is using the database. Please let me know how to work around it. I put the db in single user mode also but didn't help.
Please help as the users are waiting to get back to the database.
Thanks,
Ann
Hi,
we are imlementing a Data Warehouse on SQL 7.0.
When we are loading new data into the Data Warehouse, I want to log off all the users connected to the Data warehouse and then start the loading..
How do I do it..Is there a way to log them off from a Batch file..I will be using the "sa" login for all loading..
TIA
Murali
Hi
I need to detach a Database, but with "EXEC sp_detach_db 'test','false'" I can't do this because it is in use.
Is there a Possibility to make a "force detach" or something?
Thanks for Help
Greets
Hello,
MSSQLSERVER VER : 2000
Using - Attach and Detach Database to move the database from one Server to another server.
I did detached the database from Production and attached my test server, everything OK
Questions about - syslogins & sysdevices data dictionary tables
1. When I select * from sysdevices on production it shows all the database name size,phyname etc. After attaching to my test server I cannot see these things.. I know because it different master database but, how do I fix this.
2. syslogins - is there way to move all the syslogins after attaching to test server.
Please let me know if you guys have done anything on this.
Thanks.
Regards,
K.
How I can detach from DB and validate it?
(I mean check if it is detached or not)
Is there any why to return a value from sp_detach_db in C++?
(may a cross posting, please check under developer posts)
There are two databases on two web servers, db01 is on server01, db01_replica is on server02, db02 is on server02, db02_replica is on server01. db01 and db02 are both for one system. Each time after doing performance test, I have to recover databses. I copy the data files in a folder, try to use detach and attach to recover databases. But with two replication dbs, I don't know how to do it. The replication db should also be recovered.
View 3 Replies View RelatedI'm using following connection string in my application in (C# 2.0 windows )
Data Source=.SQLEXPRESS; Trusted_Connection=yes;AttachDbFilename=|DataDirectory|myDB.mdf"
My application attaches the file successfully when the first connection is established, but after closing my application the database is remained in the sql server express and it does not detached automatically. According to this article the database shall be detached and I need it in my application because of the security.
http://technet.microsoft.com/en-us/library/ms165716.aspx
Please let me know what shall I do ?
Thanks in advance,
Saeideh
I have developed a db on a local machine (98) and would like to move it to another machine running nt 4 where it will function as a production db. I am mapped to see the other server (called "labnt"). The drive on "labnt" is partitioned, with the c drive holding programs, and the e drive designed to hold the data.
I would like to move the db (called SNB01) to the MSSQLData subdirectory on the e-drive. I have tried the following sproc to detach:
EXEC sp_detach_db 'SNB01', 'true'
And the following to reattach:
EXEC sp_attach_db @dbname = 'SNB01',
@filename1 = 'labntewmssqldataSNB01_Data.mdf',
@filename2 = 'labntewmssqldataSNB01_log.ldf'
The above path was given me by our network admin guy.
QA gives the following error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'labntewmssqldataSNB01_Data.mdf' may be incorrect.
Please help. I am thrashing about. D. Lewis
Hi folks,
Is it possible to detach a database on SQL Server 2000 and attach it to SQL Server 7 ? I know it's fine to do the other way around. Also, any adverse effects I should be aware of ?
Thanks