Hi i m Uday,i want to delete ie Drop a database from specified location
for this i used this query as
DROP DATABASE AdventureWorks1,'C:Sql_dB_creatorsqlDB_creatorApp_DataAdventureWorks1'
but i gives incorrect syntax error.
So plz give the sql query to Drop a Database from specified path
Thank You
uday
I'm trying to install the Eval copy of SQL Server 2005 on a Win2k Server computer. The error message that
"The drop location is missing file(s) that allow setup to determine how a multi instance product should be installed. Setup cannot continue, please repair the drop and try again."
Has anyone seen this error? any ideas?
Thanks!
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 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
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
Got a couple of questions
1. If you are managing databases in SQL Express with SQL Server Managment Express CTP, why doesnt VBE place the database there where you can see them? I can create a database inside SQL Server Managment Express CTP and view its tables, relations,etc. from within SQL Server Managment Express CTP, but when I make a database with VBE it does not show up in SQL Server Managment Express CTP. Also the database that was made inside SQL Server Managment Express CTP cannot be seen from VBE. Why? what did I do wrong, or is it supposed to be like that? Is that where you would want to place a database for multiple pc to share?
2.Login Question ;-----shouldnt post this here but,
Is there a reason why you dont want to use SQL Authetication over Windows? The are a few pcs here that doesnt have user accounts installed(I know that not a good practice, but when you have multiple people needing different data quickly- switching users is not preferred, its a time thing for what I would call dummy terminals).
How do you take an instance of SQL and change to SQL Authetication over windows?
Curious?
David
Can someone please tell me what's wrong with my script below? I am trying to restore a database backup to a different database name and location.
RESTORE DATABASE test
FROM 'C:mybackup.bak'
WITH MOVE 'h_data' TO 'C:Program FilesSQL2000MSSQLDataTest_data.mdf'
Thanks
SHK
Hi all,
We need an architecture in which one office is in pune and other in mumbai,
principal server is in pune and mirrored in mumbai,
now we need an architecture in which mumbai user will performing read only operation from mumbai server (local server )and write operation in pune server(remote server)
and pune user will be performing read and write operation in pune server(local server)
now when link between pune and mumbai goes down, mumbai users will not be having acess to pune server so at that time mumbai user will not be performing write operation in pune server (local server) and when link goes up we want mumbai server to get updated with pune server.
how this is possible or any other different solution to this, what mandatory is we want mumbai users to perform read operation from mumbai server ONLY.
Regards,
Vinit Fichadia
Thanks,
Vinit Fichadia
Hai,
I would like to know, is it possible to copy the entire database from the object browser to a particular location of hard drive and vice verse. (simaltaneously copy .ldf file also).
If possible can you write script for this?
From SQL Server 2005(object browser) to c:mydatabase
Thanks.
Regards
Kashif Chotu
I can install the SQL Server Express in a computer and locate the database files in another computer of the same local network?
View 1 Replies View RelatedHey,
I'm trying to get the information where the databases are stored by default.
With Management Studio I can look on Server Properties -> Database Settings -> Database Default locations
Is there a way to read this information in TSQL?
Cheers,
Manolo
SQL location:
When I create the setup for the Teacher Starter Kit( Created with Visual Basic) (Click Once Setup) and install it in someone else computer, I can not find the SQL database ( in sense of opening it and tweak it in case i need to). the program works fine and store data, but where is the database?!! that I can not find. can you help me please? or explain.
Later On I would need to instal the ClickOnce setup of this application on a server in so everyone can reach it. is the the ClickOnce setup sufficient for this task. and will the database be added to the SQL SERVER databases ?
Please advice ...
Thank you.
RM
If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
C:Program FilesMicrosoft SQL ServerMSSQLData
I want to change that to:-
D:Database Files
I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
C:Program FilesMicrosoft SQL ServerMSSQLData
So how do I change the default?
(It's not satisfactory to have to move each database after it's created)
Thanks, Andy Abel
Hi again,I have setup a maintenance plan schedule, my question is; can I create anadditional plans located on another network location ?In my attempts the only locations displayed are those on the current server.thank you
View 1 Replies View RelatedI have installed my modell database onto c:mssql7data...
How do I now change the properties of this so that when someone creates a database, the path it will get created to is set to d:
I thought that I could just move model, but it seems that this isn't possible.
Any advice?
thanks
derek
changing master database location in sql server 2000
HELLO PLEASE HELP ME I AM TRYING TO CHANGE THE LOCATION OF MASTER DATABASE ( MDF AND LDF ) AND ERRORLOG FILES I HAVE TRIED EDITING STARTUP PARAMETERS IN ENTERPRISE MANAGER I FAILED ,I TRIED IN COMMAND
PROMPT "SQLSERVR.EXE " I SUCCEEDED IN CHANGING THE DATA FILE AND ERROR LOG BUT UNABLE TO CHANGE THE LOG FILE PATH FROM DEFAULT LOCATION C:PROGRAM ............
TO D:DATA
PLEASE GIVE ME THE PROCESS TO CHANGE DATA ,LOG AND ERROR LOG FILES of master database
thanq in advance
I have C,D,E drives on server. Data files will be on D and Log on E. My question is what is best practice for data and log files for system databases during sql server installation selection? Should they be on C drive along with SQL Server installation or D & E? If they should not be on C then what is the reason and what is benefit to move them on other drives.
View 9 Replies View Related
Hi,
I would like to change the default location where new databases are created.
In Server Management Studio, I right clicked my instance and selected Properties. I selected the Database Settings option and am presented with an option to change the Database Default Location (the location for databases and logs can be changed here). If I change the paths for the DB's and logs, and create a new DB, the new files are saved to the new default location paths that I just entered.
My question is this:
If I change these paths, will my system databases (master, model, msdb, tempdb) still be accessible and usable by SQL server? I don't need (want) to change the location of these files, only all my other databases and logs.
Will this do what I want?
Thanks for the help!
Oliver
Hey everyone I've got this question that has me stuck for the last few days but its an important part of my website.....What I am trying to do is basicly have a user be able to upload a file, have that uploaded file plus some other info automaticly display on other parts of my site, and have a different user eventually be able to download that file....I have thought about allowing the file upload as a BLOB but still cannot find a proper way to execute this using VB, plus I have heard that this way of doing it is not reccommeneded cause databases were not designed to store large files like this, lots of articles recommened having the file upload to a Folder on your server then get the binary data for the file that can be placed in a database to refrence that particular file.....Well this also proves to be a lot harder then said here is what I got so far (written in C#) protected void UploadBtn_Click(object sender, EventArgs e)
{
if (FileUpLoad1.HasFile)
{
FileUpLoad1.SaveAs(@"C:Documents and SettingsAdamMy DocumentsVisual Studio 2005ProjectsWebsiteFiles" + FileUpLoad1.FileName);
Label1.Text = "File Uploaded: " + FileUpLoad1.FileName;
}
else
{
Label1.Text = "No File Uploaded.";
}
}
and here is the asp part of the code that goes with it<asp:FileUpLoad id="FileUpLoad1" runat="server" />
<asp:Button id="UploadBtn" Text="Upload File" OnClick="UploadBtn_Click" runat="server" Width="105px" />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>
Now from what I know is I need to get the binary of the file which I have read you can do with the Page.Request.Files statement but again not sure how I would impliment this. Does anyone have any suggestions on which way I should take when dealing with this should I try and just use the BLOB method or use the binary refrence method? and if so how would I impliment this, heck even some good tutorials on the subject would be great... Thanks.....Adam
In SQL Server 2012 Instance properties ,database default location is not showing system drive E: which was my backup drive. How can I add it again?
View 8 Replies View RelatedI've configured log shipping to use for DR purposes. I'm concerned that the physical location of the secondary is mis-reported by SQL Server Management Studio.
Viewing the secondary location (with Studio DB_name Properties Files) shows the path of the primary DB (I expected it to show the path of the secondary).
This SQL command shows the correct/actual paths of both primary and secondary DB's when run on their host servers.
SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files
Is this just cosmetic?
Here is an Example of how the Studio shows the incorrect path for the secondary.
Example:
(Primary) servername=prodSrv, DBname=aquaDB, Actual_Path=G:aquaDB, SQL-studio-Properties-Path=G:AquaDB, sys.master_files Path=G:AquaDB,
Log shipped to
(Secondary / Read Only) servername=DRSrv, DBname=aquaDB, Actual Path=F:aquaDR, SQL-studio-Properties-Path=G:AquaDB(WRONG), sys.master_files Path=F:aquaDR
I am trying to delete a database that is labeled suspect. Is there a way to force a drop of a database? It is shows it is marked inaccessible when trying to delete.
Thanks, Steve
anyone know how to drop my databse on sql pleasee
charles
I am trying to drop a database, but keep getting the following error."cannot drop database 'blah' because it is currently being used forreplication".This db is not currently being replicated, but once was. It is thesubscriber side of an old replication pair.Can anyone tell what I have to do to make this go away?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHello everyone!I'm from El Salvador and i have a problem, i have a sybase systemrunning on windows 2000 server, probably a programmer drop adatabase...but i dont know who did it?, is it possible to know who dropthe database?, if the answer is yes... plese help me, because we need tosolve this security problem in our company.Sybase saves all the TSQL and transaction somewhere?,Saludos....--Posted via http://dbforums.com
View 1 Replies View RelatedI'm using SQL Server 2012 and was attempting to move the msdb, model, and tempdb databases to a new location and accidentally gave their log files an mdf extension instead of ldf when providing the new pathfilename. After the server wouldn't start I checked my script and noticed my error. I have good backups of my system databases, so I was hoping to start the MSSQLSERVER service in single-user mode (using the -m startup parameter) and then just restore master using sqlcmd.
Unfortunately the service was starting but I couldn't connect via sqlcmd using any of the three protocols (it said the server was not found or not accessible each time). I also tried using the dedicated Admin connection but I got the same error. Then I went into the Templates folder and copied the master, msdb, model, and tempdb templates into the DATA folder and tried to restart SQL Server but still no luck (now the MSSQLSERVER service won't start at all). Is there an easy way to fix this mess without having to reinstall from the setup application?
what is the sql query to drop all tables in a database in sql server 2000
View 5 Replies View RelatedHi everybody,
I would like to know if there would be any special way to force drop a database from an ASP.NET page.
When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout.
In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.
Thanks in advance
I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:
1. If I run this command
EXEC sp_MScheck_uid_owns_anything 5
I get this weird error message:
"The user owns objects in the database and cannot be dropped."
Not sure why that is since I'm just trying to list the objects, not drop them.
2. I tried running a simple query to get the objects from the system table instead:
SELECT * from [dbo].[sysobjects] where uid = 5
This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:
The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.
Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!
-Cliff
I have a list of 35 tables that need to drop the primary key index from in my database.
My problem is as follows for these 35 tables:
1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?
I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.
Thanks
How can I drop all the indexes in all the tables in a MS 2000 SQL Server?
View 9 Replies View RelatedDoes anyone happen to have a script that will drop all database objects?
I'm looking for something generic that will work with any SQL Server 2000 database. I can write one myself, but I thought I would check here first.
Thanks in advance
John
Hi All,
I use SQL SERVER 2005 and SQL SERVER Mobile.
My SQL Server I created publication, and on mobile subscriber and did web Synchronization , no problem.
I deleted publication from under Local Publication with SQL SERVER Managment Studio.
and now I want drop database, I get this error "Drop failed for Database DBNAME ... Cannot drop database DBNAME because it is being using Replication"
How to I drop this database ?
And where registred this replication information ?
Regards,
Mahir Quluzade