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 RepliesI am looking to make the primary key auto increment by 1. I have found by looking around the internet that you need to do this in the Tables -> [Table Name] -> Columns -> [Column Name], Properties window, and I see the "Identity Increment" however all of the properties there are greyed out -- I can't access them. Any ideas on how to make this work?For some background: I'm running SQL Server 2005 with Visual Studio 2005. To create this database, I right-clicked on my project, and went to 'Add SQL Database', I filled in the columns all from within visual studio.
View 1 Replies View RelatedHi,
I'm trying to install SQL Server 2005 onto my XP Professional PC. When I get to the "Components to install" part, the "SQL server database services option" is greyed out. Infact, all options except "Workstation components..." are greyed out. I've tried uninstalling all previous versions of SQL server and trying again, but with the same results.
any help would be much appreciated
Simon
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 Relatedhi
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
how do I move my database schema from 1 machine to another? Its a MSDE 2000 DB and Im using visual studio 2003.
In VS when I goto the server explorer and right click on a DB the "generate create script" options is greyed out.
What am I doing wrong?
If you cant help me with the above question could you please recommend another way for me to get my DB schema with all its constraints from one of my work stations to another. I dont want to manually recreate it.
Thankyou
I'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 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 want to use the debugger on my workstation which has also got SQL Server Dev installed . buyt the debugger is greyed out when I check it from Query analyzer window ?
How do I make it to use
thanks
I know there's a trick to this, that I can't figure out.
Have an SSIS package that I developed and deployed/saved(?) to a file on the SSIS server. Now I need to change the data flows. Did that, no issues, lets me resave the file, but I can't debug or execute the package so I can test what I did is right? Why is the debug drop down box greyed out? There's gotta be something I'm missing here.
THanks,
Lezza
Hello I am working on a sql express table and while configuring the steps after I select the data source and the selectment statement window shows, I want to use the advanced tab but it is greyed out. I want to be able to add edit and delete my data. I have administrator rights for this project and the workstation so thats not the issue. What I am tryng to accomplish is extending a website to manage it's content and users. Also the table has colums and the colums has test data within them I tested a query and the connection had a successful return. Maybe it's a configuration thing I am unaware of.
DKB
If I open Enterprise Manager through a saved MSC file, the Tools > SQLQuery Analyzer option is greyed out (unavailable). If I open EnterpriseManager from the Programs menu, Query Analyzer is available. Is theresomething specific I need to do with msc?I created an MSC file and added the Enterprise Manager snap-in.
View 3 Replies View RelatedHello,
I have installed all the required installations for SQl Server CE, however when I open SQL Management Studio, the server type option is greyed out and I do not have the option to select SQL CE server type.
I have installed SQl Management Studio Express edition (if that helps.).
Please suggest.
Regards,
Arjun
HI There
I have installed Reportin Services 2005 , X64. I selected install but do not configure.
Afterwards i applied Service Pack 1 and ran all the hotfixes for sql components as per KB 918222.
The i launch the RS configuration manager i succesffuly setup Report Server/Manager Virtual DIrectory and servcie identity.
I then setup the database, i create a new database to the local 2005 instance i just installed with RS, the databse is created successfully.
Basically i have green ticks all the way, however Initialize is greyed out i cannot take the option to initialize the server ???
When i try to restart the RS service it says that the service failed to repond in a timely fashion, when i check the even log the servcie only started 10 minutes later, and there are no errors ?
WHen i go back to RS config manager , initialize is till greyed out , this is the last step i ahve to do but i cannot take the option ???
PLEASE HELP, Thanx
Hi Guys,
Why is that some of the databases has their "Transaction Log" & "File and Filegroup" options in Backup greyed out? I was trying to create a backup job using the sql enterprise backup wizard. However, the database that is the backend of our erp application has its "Transaction Log" and "File and Filegroup" option greyed out.
Any idea why?
sk
I would like to enter info into the startup service account so that the MS
SQL Server Agent will run under this account. Then I can run jobs.
However, I don't know why the "startup service account" info is
greyed out, which prevents me from entering this information.
Any help would be appreciated!!