deleting table phisically

hello
I delete a table from a DataBase in enterprise manager(drop table) ,
but it is not deleted phisically and hard's free space does not
increas.
How can I delete table phisically?
thanksYou need to shrink the database to recover disk space on the drive...
"nsh" <nsh5776@.yahoo.com> wrote in message
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||How do you know the table is not deleted phisically? because of the database
file (.mdf) file?
Most likely you just vacated space within the .mdf file.
Back up the database, then try to shrink it.
using Enterprise manager, Right mouse on the database -> All Tasks ->
Shrink dbase
You can also see how much space is being used in the .mdf file grafically
using Enterprise manager, Right mouse on the database -> View -> Task
Pad
Hope this helps
"nsh" <nsh5776@.yahoo.com> escribió en el mensaje
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||thanks for helping.
Before delete table .mdf & .ldf size are the same after delete table
(each table size is about 5GB ).
when I make Data Base ,I set Autoshrink .Is not it enough?
please help me ...
thanks
"Marcelo" <marcelo.no@.spam.santiago.cl> wrote in message news:<#YAUSvkRDHA.1324@.TK2MSFTNGP11.phx.gbl>...
> How do you know the table is not deleted phisically? because of the database
> file (.mdf) file?
> Most likely you just vacated space within the .mdf file.
> Back up the database, then try to shrink it.
> using Enterprise manager, Right mouse on the database -> All Tasks ->
> Shrink dbase
> You can also see how much space is being used in the .mdf file grafically
> using Enterprise manager, Right mouse on the database -> View -> Task
> Pad
> Hope this helps
> "nsh" <nsh5776@.yahoo.com> escribió en el mensaje
> news:534bf457.0307091048.714766fe@.posting.google.com...
> > hello
> > I delete a table from a DataBase in enterprise manager(drop table) ,
> > but it is not deleted phisically and hard's free space does not
> > increas.
> > How can I delete table phisically?
> > thanks|||> when I make Data Base ,I set Autoshrink .Is not it enough?
No, because it doesn't auto-shrink every single time the database changes --
this would cause a performance nightmare.

Deleting of Databases

Windows 2003 SP2 with SQL 2000:
We are trying to delete some databases that are old and the Enterprise
Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
the problem but it doesn't. We've tried from other SQL boxes' Enterprise
Managers as well with no privail. Is there any way (potentially from SQLCMD)
to properly force the deletion of a database? Thanks in advance to anyone
with some info!ctowndu33 wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!
Have you tried to switch database to off-line mode (or detach database)
and then delete mdf/ldf files?
PL|||1) Do you get any error message (perhaps logged in the sql server error
logs?
2) Have you tried this from the command line or from query analyzer?
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:450B4B7A-F575-4F29-8D31-55B6F395ABB4@.microsoft.com...
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
> fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from
> SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Now we realize that we can delete the databases if we clear the check mark to
remove backups. It's almost like it is searching for old backups that don't
exist any more. I though maybe I would see some really slow speeds (if it is
searching for old backups) if I try and do a restore. Turns out it took like
10 minutes for the restore box to pop up. Could the history of backups
contain orphaned informationt that would be causing this non-responsive
behavior? Thanks again everyone!
"ctowndu33" wrote:
> Windows 2003 SP2 with SQL 2000:
> We are trying to delete some databases that are old and the Enterprise
> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would fix
> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
> Managers as well with no privail. Is there any way (potentially from SQLCMD)
> to properly force the deletion of a database? Thanks in advance to anyone
> with some info!|||Or perhaps it is simply taking forever to find the backups to delete? I
have a client with 6500+ databases on one server. I long ago had to create
several indexes on msdb tables so that things like this would work. The
index seeks enabled by the indexes let to very fast operations on
backup/restore tables.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"ctowndu33" <ctowndu33@.discussions.microsoft.com> wrote in message
news:AADA5A38-70AC-4B89-862E-BDE64C5F1178@.microsoft.com...
> Now we realize that we can delete the databases if we clear the check mark
> to
> remove backups. It's almost like it is searching for old backups that
> don't
> exist any more. I though maybe I would see some really slow speeds (if it
> is
> searching for old backups) if I try and do a restore. Turns out it took
> like
> 10 minutes for the restore box to pop up. Could the history of backups
> contain orphaned informationt that would be causing this non-responsive
> behavior? Thanks again everyone!
> "ctowndu33" wrote:
>> Windows 2003 SP2 with SQL 2000:
>> We are trying to delete some databases that are old and the Enterprise
>> Manager hangs on deletion. We upgraded from SP 3a to 4 hoping that would
>> fix
>> the problem but it doesn't. We've tried from other SQL boxes' Enterprise
>> Managers as well with no privail. Is there any way (potentially from
>> SQLCMD)
>> to properly force the deletion of a database? Thanks in advance to
>> anyone
>> with some info!

Deleting files

Hello there
On every w by Enterprise manager i:
1. Detatch the database
2. delete the log file
3. reattaching it again
I would like to do this by code.
Is there a way to do that?Roy Goldhammer wrote:

> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
Yes. Don't. Regularly detaching and deleting the log in an operational
system is certainly foolish, unnecessary and/or dangerous.
Firstly, set the correct recovery model - that means "Simple Recovery"
if you don't require log backups. Secondly, ensure you implement the
right tran log backups if you do need them. Finally, set the log file
to the right size, turn off autogrow and LEAVE IT ALONE.
More info:
http://support.microsoft.com/?id=110139
http://support.microsoft.com/?id=317375
http://support.microsoft.com/?id=315512
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
David Portas
SQL Server MVP
--|||Hi
Why?
What you are doing is unsupported and very dangerous. In some cases, you
will not be able to re-attach the database again and kiss your DB goodbye.
Rather put your database into the appropriate recovery mode, and backup the
log if it is not in simple mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OnjYA8GCGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
>|||I agree with David and Mike. Don't do this. It will bite you in the
backside.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OnjYA8GCGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
>

deleting duplicates

I'm having trouble deleting duplicates from enterprise manager. I get "Key
column information is insufficient or incorrect. Too many rows were affected
by update." messages.
I can run delete queries through query analyzer but that will delete the
original reocrd and duplicate. The table in question has 3 columns (all 3
columns show dup data) with no indexes and about 15 original rows duplicated.
Question 1: Why can't I delete from enterprise manager? The help file is
useless.
Question 2: What is the T-SQL for deleting the duplicate record (but leaving
the original)?
On Wed, 29 Mar 2006 20:01:01 -0800, carl wrote:
Hi Carl,
I'll address your questions in reverse order.

>Question 2: What is the T-SQL for deleting the duplicate record (but leaving
>the original)?
There is none (but see below form some kludges).
In the DELETE statement, you use a WHERE clause to tell SQL Server which
row(s) to delete. If two or more rows have the exact same data in ALL
columns, then any WHERE clause that matches one will match the other as
well. That's but one of the reasons why each table should always have at
least one PRIMARY KEY or UNIQUE constraint.

>Question 1: Why can't I delete from enterprise manager? The help file is
>useless.
Since Enterprise Manager is just a fancy front end that translates your
mouse clicks to queries, it has the same limitation as you have when
writing T-SQL statements in Query Analyzer.

>I can run delete queries through query analyzer but that will delete the
>original reocrd and duplicate. The table in question has 3 columns (all 3
>columns show dup data) with no indexes and about 15 original rows duplicated.
To delete just a single duplicated row, you can use this kludge:
SET ROWCOUNT 1
DELETE FROM MyTable
WHERE Column1 = ...
AND Column2 = ...
....
SET ROWCOUNT 0
If you want to get rid of *ALL* duplicates, rename the table, then
recreate it (don't forget to add the constraints this time!!) and move
the data back, using DISTINCT to squish the dups:
sp_rename 'MyTable', 'MyTableTMP', 'OBJECT'
go
CREATE TABLE MyTable
(Column1 int NOT NULL,
...
PRIMARY KEY (Column1, Column2)
)
go
INSERT INTO MyTable (Column1, ...)
SELECT DISTINCT Column1, ...
FROM MyTableTMP
go
DROP TABLE MyTableTMP
go
Hugo Kornelis, SQL Server MVP

Deleting a replicated database..

I am new to SQL server...In case you couldn't tell.
Under Enterprise Manager, SQLGROUP, Server, Databases, I
show three databases that are "suspect" that I need to
delete. The databases were manually removed
(Permanentley) from the SQL directory in explorer. When I
try to delete the database from enterprise manager, I get
the error message, "Error 3724: Cannot drop the
database "db" because it is being used for replication. I
can find no place to cancel or remove the replication.
Please help.
Thanks,
Scott
can you issue a sp_resetstatus 'ProblemDatabaseName' to mark these databases
as 'normal' and then try to delete them?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Scott" <scott@.jabrousa.com> wrote in message
news:240001c47014$a9f83790$a301280a@.phx.gbl...
> I am new to SQL server...In case you couldn't tell.
> Under Enterprise Manager, SQLGROUP, Server, Databases, I
> show three databases that are "suspect" that I need to
> delete. The databases were manually removed
> (Permanentley) from the SQL directory in explorer. When I
> try to delete the database from enterprise manager, I get
> the error message, "Error 3724: Cannot drop the
> database "db" because it is being used for replication. I
> can find no place to cancel or remove the replication.
> Please help.
> Thanks,
> Scott
>

Deleting a record from a VIEW

Hi All,
I am using Microsoft SQL Enterprise Manager version 8.0 and have
created a view from a combination of 4 different tables. I would like
to be able to go into sql and open the view and select a row and
delete that row however this seem impossible right now. I am not sure
if it's possible to delete a row from a view?? Or could it be that
these tables are all interconnected and in order to delete a record
that is joined to one or more of the tables it has to be deleted at
the top level of the join heirarchy etc etc. (do you understand what i
mean?) Can this be done??
Thanks in advance,
ErinXmlAdoNewbie (erin.sebastian@.cowaninsurancegroup.com) writes:
> I am using Microsoft SQL Enterprise Manager version 8.0 and have
> created a view from a combination of 4 different tables. I would like
> to be able to go into sql and open the view and select a row and
> delete that row however this seem impossible right now. I am not sure
> if it's possible to delete a row from a view?? Or could it be that
> these tables are all interconnected and in order to delete a record
> that is joined to one or more of the tables it has to be deleted at
> the top level of the join heirarchy etc etc. (do you understand what i
> mean?) Can this be done??

So if you have a view which is like:

create view petra (a1, b1, b2, c) as
SELECT n.a, n.b, k.b, c
from nisse n join kalle k on n.a = k.a
go

And you say "DELETE petra WHERE a1 = 2" what do you expect to happen?
From which table is the row to be deleted?

Anyway, Books Online says in the topic for DELETE:

view_name

Is the name of a view. The view referenced by view_name must be
updatable and reference exactly one base table in the FROM clause of
the view. For more information about updatable views, see CREATE VIEW.

Thus, it does not seem from your description that your view would
be updatable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 7 Jun 2004 11:39:38 -0700, XmlAdoNewbie wrote:

> Hi All,
> I am using Microsoft SQL Enterprise Manager version 8.0 and have
> created a view from a combination of 4 different tables. I would like
> to be able to go into sql and open the view and select a row and
> delete that row however this seem impossible right now. I am not sure
> if it's possible to delete a row from a view?? Or could it be that
> these tables are all interconnected and in order to delete a record
> that is joined to one or more of the tables it has to be deleted at
> the top level of the join heirarchy etc etc. (do you understand what i
> mean?) Can this be done??
> Thanks in advance,
> Erin

As Mr. Sommarskog explained, it is not technically possible to delete from
a view based on more than one table, and the reason is that it is unclear
what record should actually be deleted. However, if it is clear to you,
then you can do something like the following.

CREATE TABLE1 ( KEY1 int PRIMARY KEY,
NAME varchar(30) NOT NULL,
KEY2 int NOT NULL,
KEY3 int NOT NULL,
KEY4 int NOT NULL);
CREATE TABLE2 ( KEY2 int PRIMARY KEY, CITY varchar(30) NOT NULL);
CREATE TABLE3 ( KEY3 int PRIMARY KEY, STATE varchar(2) NOT NULL);
CREATE TABLE4 ( KEY4 int PRIMARY KEY, MARKET varchar(30) NOT NULL);

CREATE VIEW1 AS
SELECT TABLE1.KEY1, TABLE1.NAME, TABLE2.CITY, TABLE3.STATE, TABLE4.MARKET
FROM TABLE1
INNER JOIN TABLE2 on TABLE1.KEY2 = TABLE2.KEY2
INNER JOIN TABLE3 on TABLE1.KEY3 = TABLE2.KEY3
INNER JOIN TABLE4 on TABLE1.KEY4 = TABLE2.KEY4;

Now, suppose what you want to do is delete all rows from TABLE1 that match
TABLE4.MARKET='Orlando':

DELETE FROM TABLE1
WHERE TABLE1.KEY1 IN (
SELECT VIEW1.KEY1 FROM VIEW1 WHERE VIEW1.MARKET = 'Orlando' );

So, you see that you're not deleting directly from the view; you're
deleting from a table that participates in the view, based on information
you retrieved from the view.|||You can also define an INSTEAD OF TRIGGER on the view and delete the
records from the two tables in the trigger definition. This is
probably the cleanest way to do it and gives you the perception of
being able to delete from the view.

Ross Presser <rpresser@.imtek.com> wrote in message news:<143lxsb87s5gp.1l5wdsmlcexee$.dlg@.40tude.net>...
> On 7 Jun 2004 11:39:38 -0700, XmlAdoNewbie wrote:
> > Hi All,
> > I am using Microsoft SQL Enterprise Manager version 8.0 and have
> > created a view from a combination of 4 different tables. I would like
> > to be able to go into sql and open the view and select a row and
> > delete that row however this seem impossible right now. I am not sure
> > if it's possible to delete a row from a view?? Or could it be that
> > these tables are all interconnected and in order to delete a record
> > that is joined to one or more of the tables it has to be deleted at
> > the top level of the join heirarchy etc etc. (do you understand what i
> > mean?) Can this be done??
> > Thanks in advance,
> > Erin
> As Mr. Sommarskog explained, it is not technically possible to delete from
> a view based on more than one table, and the reason is that it is unclear
> what record should actually be deleted. However, if it is clear to you,
> then you can do something like the following.
> CREATE TABLE1 ( KEY1 int PRIMARY KEY,
> NAME varchar(30) NOT NULL,
> KEY2 int NOT NULL,
> KEY3 int NOT NULL,
> KEY4 int NOT NULL);
> CREATE TABLE2 ( KEY2 int PRIMARY KEY, CITY varchar(30) NOT NULL);
> CREATE TABLE3 ( KEY3 int PRIMARY KEY, STATE varchar(2) NOT NULL);
> CREATE TABLE4 ( KEY4 int PRIMARY KEY, MARKET varchar(30) NOT NULL);
> CREATE VIEW1 AS
> SELECT TABLE1.KEY1, TABLE1.NAME, TABLE2.CITY, TABLE3.STATE, TABLE4.MARKET
> FROM TABLE1
> INNER JOIN TABLE2 on TABLE1.KEY2 = TABLE2.KEY2
> INNER JOIN TABLE3 on TABLE1.KEY3 = TABLE2.KEY3
> INNER JOIN TABLE4 on TABLE1.KEY4 = TABLE2.KEY4;
> Now, suppose what you want to do is delete all rows from TABLE1 that match
> TABLE4.MARKET='Orlando':
> DELETE FROM TABLE1
> WHERE TABLE1.KEY1 IN (
> SELECT VIEW1.KEY1 FROM VIEW1 WHERE VIEW1.MARKET = 'Orlando' );
> So, you see that you're not deleting directly from the view; you're
> deleting from a table that participates in the view, based on information
> you retrieved from the view.|||Thanks Everyone for your help! I have decided to build a little
utility in C# that will allow the user to pick the record to delete
from the view and then delete the record from the table and all joined
tables.
I appreciate all your help!
Erin

deleting a publication

Hi,
I removed a publication using the enterprise manager, and it has gone from
the publications views, but i can make the db ofline, because it reports it
is still published.
What can i do to tidy this up?
Thanks
Duncan
Duncan,
try
USE master
EXEC sp_dboption 'your db name', 'published', 'FALSE'
EXEC sp_dboption 'your db name', 'merge publish', 'FALSE'
HTH,
Paul Ibison

Deleting a datbase.

Can anyone help me with this ?
I was wondering if there is a command I can run that deletes a database? I know this can be done in Enterprise Manager.
Have tried a few sites and searches to no avail...
Any help appreciated...
Maith agat,It's as easy as DROP DATABASE.

PS It's in the books online;)

Deleted Transaction Logs!

I accidentalty deleted the transaction logs on a DB I had, and now I can get
it up on SQL enterprise, any ideas?
-Gary
Hi,
Try to attach the database with out the tranaction log using
sp_attach_single_file_db system stored procedure. This may not work since
your database is not detached.
Usage:
sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
file'
If it fails restore the database from the last successful backup.
Thanks
Hari
MCDBA
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>
|||Thx Hari,
I have replications running, and it won't let me detach, any ideas on how to
detach this?
-Gary
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Try to attach the database with out the tranaction log using
> sp_attach_single_file_db system stored procedure. This may not work since
> your database is not detached.
> Usage:
> sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
> file'
> If it fails restore the database from the last successful backup.
> Thanks
> Hari
> MCDBA
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> get
>
|||is it safe to delete these files? I wont lose core data will I?
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>
|||and if I try to disable publishing, I get errors regarding the database
whose transaction logs are deleted.
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uKy83nMHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Thx Hari,
> I have replications running, and it won't let me detach, any ideas on how
to
> detach this?
> -Gary
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
since
MDF
can
>
|||You will loose any uncomitted data and anything after the last CHECKPOINT,
do you have your recovery model full or simple? If its simple I think you
loose less.
You can also create a new database with the same name, stop sql service and
replace the data files with your original database and then start the
service again. If this didnt work set the database to emergency mode and
then try to copy objects using DTS to a new database.
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> is it safe to delete these files? I wont lose core data will I?
> -Gary
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> get
>
|||how can I go to emergency mode?
-Gary
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> You will loose any uncomitted data and anything after the last CHECKPOINT,
> do you have your recovery model full or simple? If its simple I think you
> loose less.
> You can also create a new database with the same name, stop sql service
and
> replace the data files with your original database and then start the
> service again. If this didnt work set the database to emergency mode and
> then try to copy objects using DTS to a new database.
>
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
can
>
|||UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uhpeyhNHEHA.2128@.TK2MSFTNGP11.phx.gbl...
> how can I go to emergency mode?
> -Gary
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
CHECKPOINT,
you
> and
> can
>
|||You don't have any of the log files at all?
You're best bet is to open a call with PSS. Depending on the circumstances
PSS may be able to help you recover most of the data. There are a number of
things that you could do on your own, that are not documented. But,
honestly, the PSS call is pretty cheap and doing it on your own can be
tricky. I really think the PSS call is worth the price.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>
|||Hi Gary,
Here is a technique suggested by Jasper Smith once. I
documented it for later use.
Here's a technique I have used in the past - you need to
change the file/database names as this was for a specific
case :
If you did not detach the database prior to copying it
then you will not be able to attach it easily (probably).
First thing to try is make sure the folder
'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
then create it,copy your MDF there and retry your statement
If that doesn't work then you're in a bit of trouble but
you can try this
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file
to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you
could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need
them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names
*/
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME
= 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file
will be small so it will be worth increasing its
size
Unfortunately your files will be called
fake_Data.MDF and
fake_Log.LDF but you can get round this by
detaching the
database properly and then renaming the files and
reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
Regards
Thirumal

Deleted Transaction Logs!

I accidentalty deleted the transaction logs on a DB I had, and now I can get
it up on SQL enterprise, any ideas?
-GaryHi,
Try to attach the database with out the tranaction log using
sp_attach_single_file_db system stored procedure. This may not work since
your database is not detached.
Usage:
sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
file'
If it fails restore the database from the last successful backup.
Thanks
Hari
MCDBA
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Thx Hari,
I have replications running, and it won't let me detach, any ideas on how to
detach this?
-Gary
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Try to attach the database with out the tranaction log using
> sp_attach_single_file_db system stored procedure. This may not work since
> your database is not detached.
> Usage:
> sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
> file'
> If it fails restore the database from the last successful backup.
> Thanks
> Hari
> MCDBA
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > I accidentalty deleted the transaction logs on a DB I had, and now I can
> get
> > it up on SQL enterprise, any ideas?
> >
> > -Gary
> >
> >
>|||is it safe to delete these files? I wont lose core data will I?
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||and if I try to disable publishing, I get errors regarding the database
whose transaction logs are deleted.
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uKy83nMHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Thx Hari,
> I have replications running, and it won't let me detach, any ideas on how
to
> detach this?
> -Gary
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Try to attach the database with out the tranaction log using
> > sp_attach_single_file_db system stored procedure. This may not work
since
> > your database is not detached.
> >
> > Usage:
> >
> > sp_attach_single_file_db 'dbname' , 'physical_name with the path of
MDF
> > file'
> >
> > If it fails restore the database from the last successful backup.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > I accidentalty deleted the transaction logs on a DB I had, and now I
can
> > get
> > > it up on SQL enterprise, any ideas?
> > >
> > > -Gary
> > >
> > >
> >
> >
>|||You will loose any uncomitted data and anything after the last CHECKPOINT,
do you have your recovery model full or simple? If its simple I think you
loose less.
You can also create a new database with the same name, stop sql service and
replace the data files with your original database and then start the
service again. If this didnt work set the database to emergency mode and
then try to copy objects using DTS to a new database.
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> is it safe to delete these files? I wont lose core data will I?
> -Gary
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > I accidentalty deleted the transaction logs on a DB I had, and now I can
> get
> > it up on SQL enterprise, any ideas?
> >
> > -Gary
> >
> >
>|||how can I go to emergency mode?
-Gary
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> You will loose any uncomitted data and anything after the last CHECKPOINT,
> do you have your recovery model full or simple? If its simple I think you
> loose less.
> You can also create a new database with the same name, stop sql service
and
> replace the data files with your original database and then start the
> service again. If this didnt work set the database to emergency mode and
> then try to copy objects using DTS to a new database.
>
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> > is it safe to delete these files? I wont lose core data will I?
> >
> > -Gary
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > I accidentalty deleted the transaction logs on a DB I had, and now I
can
> > get
> > > it up on SQL enterprise, any ideas?
> > >
> > > -Gary
> > >
> > >
> >
> >
>|||UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uhpeyhNHEHA.2128@.TK2MSFTNGP11.phx.gbl...
> how can I go to emergency mode?
> -Gary
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> > You will loose any uncomitted data and anything after the last
CHECKPOINT,
> > do you have your recovery model full or simple? If its simple I think
you
> > loose less.
> >
> > You can also create a new database with the same name, stop sql service
> and
> > replace the data files with your original database and then start the
> > service again. If this didnt work set the database to emergency mode and
> > then try to copy objects using DTS to a new database.
> >
> >
> >
> > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> > > is it safe to delete these files? I wont lose core data will I?
> > >
> > > -Gary
> > >
> > > "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> > > news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> > > > I accidentalty deleted the transaction logs on a DB I had, and now I
> can
> > > get
> > > > it up on SQL enterprise, any ideas?
> > > >
> > > > -Gary
> > > >
> > > >
> > >
> > >
> >
> >
>|||You don't have any of the log files at all?
You're best bet is to open a call with PSS. Depending on the circumstances
PSS may be able to help you recover most of the data. There are a number of
things that you could do on your own, that are not documented. But,
honestly, the PSS call is pretty cheap and doing it on your own can be
tricky. I really think the PSS call is worth the price.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Hi Gary,
Here is a technique suggested by Jasper Smith once. I
documented it for later use.
--
Here's a technique I have used in the past - you need to
change the file/database names as this was for a specific
case :
If you did not detach the database prior to copying it
then you will not be able to attach it easily (probably).
First thing to try is make sure the folder
'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
then create it,copy your MDF there and retry your statement
If that doesn't work then you're in a bit of trouble but
you can try this
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file
to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you
could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need
them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names
*/
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME
= 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file
will be small so it will be worth increasing its
size
Unfortunately your files will be called
fake_Data.MDF and
fake_Log.LDF but you can get round this by
detaching the
database properly and then renaming the files and
reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
Regards
Thirumal|||thanks for the help!
-Gary
"Thirumal" <treddym@.hotmail.nospam.com> wrote in message
news:19c8701c41d14$d4ea68e0$a501280a@.phx.gbl...
> Hi Gary,
> Here is a technique suggested by Jasper Smith once. I
> documented it for later use.
> --
> Here's a technique I have used in the past - you need to
> change the file/database names as this was for a specific
> case :
> If you did not detach the database prior to copying it
> then you will not be able to attach it easily (probably).
> First thing to try is make sure the folder
> 'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
> then create it,copy your MDF there and retry your statement
> If that doesn't work then you're in a bit of trouble but
> you can try this
> 1) Make sure you have a copy of PowerDVD301_2_Data.MDF
> 2) Create a new database called fake (default file
> locations)
> 3) Stop SQL Service
> 4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
> to where fake_Data.MDF used to be and rename the file
> to fake_Data.MDF
> 5) Start SQL Service
> 6) Database fake will appear as suspect in EM
> 7) Open Query Analyser and in master database run the
> following :
> sp_configure 'allow updates',1
> go
> reconfigure with override
> go
> update sysdatabases set
> status=-32768 where dbid=DB_ID('fake')
> go
> sp_configure 'allow updates',0
> go
> reconfigure with override
> go
> This will put the database in emergency recovery mode
> 8) Stop SQL Service
> 9) Delete the fake_Log.LDF file
> 10) Restart SQL Service
> 11) In QA run the following (with correct path for log)
> dbcc rebuild_log('fake','h:\fake_log.ldf')
> go
> dbcc checkdb('fake') -- to check for errors
> go
> 12) Now we need to rename the files, run the following
> (make sure
> there are no connections to it) in Query Analyser
> (At this stage you can actually access the database so you
> could use
> DTS or bcp to move the data to another database .)
> use master
> go
> sp_helpdb 'fake'
> go
> /* Make a note of the names of the files , you will need
> them
> in the next bit of the script to replace datafilename and
> logfilename - it might be that they have the right names
> */
> sp_renamedb 'fake','PowerDVD301'
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='datafilename', NEWNAME
> = 'PowerDVD301_Data')
> go
> alter database PowerDVD301
> MODIFY FILE(NAME='logfilename', NEWNAME
> = 'PowerDVD301_Log')
> go
> dbcc checkdb('PowerDVD301')
> go
> sp_dboption 'PowerDVD301','dbo use only','false'
> go
> use PowerDVD301
> go
> sp_updatestats
> go
> 13) You should now have a working database. However the
> log file
> will be small so it will be worth increasing its
> size
> Unfortunately your files will be called
> fake_Data.MDF and
> fake_Log.LDF but you can get round this by
> detaching the
> database properly and then renaming the files and
> reattaching
> it
> 14) Run the following in QA
> sp_detach_db PowerDVD301
>
> --now rename the files then reattach
> sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
>
> Regards
> Thirumal

Deleted Transaction Logs!

I accidentalty deleted the transaction logs on a DB I had, and now I can get
it up on SQL enterprise, any ideas?
-GaryHi,
Try to attach the database with out the tranaction log using
sp_attach_single_file_db system stored procedure. This may not work since
your database is not detached.
Usage:
sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
file'
If it fails restore the database from the last successful backup.
Thanks
Hari
MCDBA
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Thx Hari,
I have replications running, and it won't let me detach, any ideas on how to
detach this?
-Gary
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Try to attach the database with out the tranaction log using
> sp_attach_single_file_db system stored procedure. This may not work since
> your database is not detached.
> Usage:
> sp_attach_single_file_db 'dbname' , 'physical_name with the path of MDF
> file'
> If it fails restore the database from the last successful backup.
> Thanks
> Hari
> MCDBA
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> get
>|||is it safe to delete these files? I wont lose core data will I?
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||and if I try to disable publishing, I get errors regarding the database
whose transaction logs are deleted.
-Gary
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uKy83nMHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Thx Hari,
> I have replications running, and it won't let me detach, any ideas on how
to
> detach this?
> -Gary
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:OKQbWmMHEHA.3584@.TK2MSFTNGP09.phx.gbl...
since
MDF
can
>|||You will loose any uncomitted data and anything after the last CHECKPOINT,
do you have your recovery model full or simple? If its simple I think you
loose less.
You can also create a new database with the same name, stop sql service and
replace the data files with your original database and then start the
service again. If this didnt work set the database to emergency mode and
then try to copy objects using DTS to a new database.
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
> is it safe to delete these files? I wont lose core data will I?
> -Gary
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> get
>|||how can I go to emergency mode?
-Gary
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
> You will loose any uncomitted data and anything after the last CHECKPOINT,
> do you have your recovery model full or simple? If its simple I think you
> loose less.
> You can also create a new database with the same name, stop sql service
and
> replace the data files with your original database and then start the
> service again. If this didnt work set the database to emergency mode and
> then try to copy objects using DTS to a new database.
>
> "Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
> news:uZUp64MHEHA.2668@.TK2MSFTNGP10.phx.gbl...
can
>|||UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:uhpeyhNHEHA.2128@.TK2MSFTNGP11.phx.gbl...
> how can I go to emergency mode?
> -Gary
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23jMoDfNHEHA.3356@.TK2MSFTNGP11.phx.gbl...
CHECKPOINT,
you
> and
> can
>|||You don't have any of the log files at all?
You're best bet is to open a call with PSS. Depending on the circumstances
PSS may be able to help you recover most of the data. There are a number of
things that you could do on your own, that are not documented. But,
honestly, the PSS call is pretty cheap and doing it on your own can be
tricky. I really think the PSS call is worth the price.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Gary" <gary.rey@.virtium.NOSPAM.com> wrote in message
news:endeRjMHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I accidentalty deleted the transaction logs on a DB I had, and now I can
get
> it up on SQL enterprise, any ideas?
> -Gary
>|||Hi Gary,
Here is a technique suggested by Jasper Smith once. I
documented it for later use.
Here's a technique I have used in the past - you need to
change the file/database names as this was for a specific
case :
If you did not detach the database prior to copying it
then you will not be able to attach it easily (probably).
First thing to try is make sure the folder
'D:\DATA2K\MSSQL$SKINNER2K\DATA\ exists,if not
then create it,copy your MDF there and retry your statement
If that doesn't work then you're in a bit of trouble but
you can try this
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file
locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file
to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the
following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following
(make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you
could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need
them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names
*/
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME
= 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME
= 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the
log file
will be small so it will be worth increasing its
size
Unfortunately your files will be called
fake_Data.MDF and
fake_Log.LDF but you can get round this by
detaching the
database properly and then renaming the files and
reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
Regards
Thirumal

Deleted entire DB by accident!

Gentlemen, Can anyone save my life at the moment!
I need to restore a DB that I accidentally erased using
the Delete command in the Enterprise admin. I was on the
wrong server and the DB names are the same.
(No Backups were on this box since it's a dev box.)
Can anyone tell me a way to get my DB back?
Thanks,
BradTake your last production backup and restore it to your dev server.
Or were you fibbing and delete a production database you had no backups for?
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> Gentlemen, Can anyone save my life at the moment!
> I need to restore a DB that I accidentally erased using
> the Delete command in the Enterprise admin. I was on the
> wrong server and the DB names are the same.
> (No Backups were on this box since it's a dev box.)
> Can anyone tell me a way to get my DB back?
> Thanks,
> Brad|||The concept of Dev in our dept is that it it's Dev before
it goes to production. So, it's never actually seen
production. There is no actual restore .BAK file and
the .MDF file is missing.
Any ideas?
>--Original Message--
>Take your last production backup and restore it to your
dev server.
>Or were you fibbing and delete a production database you
had no backups for?
>"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
message
>news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
>> Gentlemen, Can anyone save my life at the moment!
>> I need to restore a DB that I accidentally erased using
>> the Delete command in the Enterprise admin. I was on
the
>> wrong server and the DB names are the same.
>> (No Backups were on this box since it's a dev box.)
>> Can anyone tell me a way to get my DB back?
>> Thanks,
>> Brad
>
>.
>|||Turn off the sql server and start searching for a file restore utility (I
don't have one or know of one in particular) that will work on your OS. If
the filespace is overwritten you will lose your data.
Just in case, though, did you select to delete the filegroups? If not they
will still be there as .ndf, .mdf, .ldf files.
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:2774501c38f69$d9323470$a601280a@.phx.gbl...
> The concept of Dev in our dept is that it it's Dev before
> it goes to production. So, it's never actually seen
> production. There is no actual restore .BAK file and
> the .MDF file is missing.
> Any ideas?
>
> >--Original Message--
> >Take your last production backup and restore it to your
> dev server.
> >
> >Or were you fibbing and delete a production database you
> had no backups for?
> >
> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> message
> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> >> Gentlemen, Can anyone save my life at the moment!
> >> I need to restore a DB that I accidentally erased using
> >> the Delete command in the Enterprise admin. I was on
> the
> >> wrong server and the DB names are the same.
> >> (No Backups were on this box since it's a dev box.)
> >> Can anyone tell me a way to get my DB back?
> >>
> >> Thanks,
> >>
> >> Brad
> >
> >
> >.
> >|||Thanks for the help, unfortunately I've been trying to
recover the MDF files with recovery utilities but so far
I've had no luck. Is it even possible to track down an
MDF file once it's been deleted with Enterprise Manager?
>--Original Message--
>Turn off the sql server and start searching for a file
restore utility (I
>don't have one or know of one in particular) that will
work on your OS. If
>the filespace is overwritten you will lose your data.
>Just in case, though, did you select to delete the
filegroups? If not they
>will still be there as .ndf, .mdf, .ldf files.
>
>
>
>"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
message
>news:2774501c38f69$d9323470$a601280a@.phx.gbl...
>> The concept of Dev in our dept is that it it's Dev
before
>> it goes to production. So, it's never actually seen
>> production. There is no actual restore .BAK file and
>> the .MDF file is missing.
>> Any ideas?
>>
>> >--Original Message--
>> >Take your last production backup and restore it to your
>> dev server.
>> >
>> >Or were you fibbing and delete a production database
you
>> had no backups for?
>> >
>> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
>> message
>> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
>> >> Gentlemen, Can anyone save my life at the moment!
>> >> I need to restore a DB that I accidentally erased
using
>> >> the Delete command in the Enterprise admin. I was on
>> the
>> >> wrong server and the DB names are the same.
>> >> (No Backups were on this box since it's a dev box.)
>> >> Can anyone tell me a way to get my DB back?
>> >>
>> >> Thanks,
>> >>
>> >> Brad
>> >
>> >
>> >.
>> >
>
>.
>|||Deleting with EM means that EM executes the DROP DATABASE command. For the DROP DATABASE command,
SQL Server will delete the file physically. Working on the OS level is your last resort. And, if
keeping the data from the dev environment is crucial, make sure you have backup for the dev
environment next time. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in message
news:036001c38f72$ef2e2b90$a401280a@.phx.gbl...
> Thanks for the help, unfortunately I've been trying to
> recover the MDF files with recovery utilities but so far
> I've had no luck. Is it even possible to track down an
> MDF file once it's been deleted with Enterprise Manager?
> >--Original Message--
> >Turn off the sql server and start searching for a file
> restore utility (I
> >don't have one or know of one in particular) that will
> work on your OS. If
> >the filespace is overwritten you will lose your data.
> >
> >Just in case, though, did you select to delete the
> filegroups? If not they
> >will still be there as .ndf, .mdf, .ldf files.
> >
> >
> >
> >
> >
> >
> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> message
> >news:2774501c38f69$d9323470$a601280a@.phx.gbl...
> >> The concept of Dev in our dept is that it it's Dev
> before
> >> it goes to production. So, it's never actually seen
> >> production. There is no actual restore .BAK file and
> >> the .MDF file is missing.
> >>
> >> Any ideas?
> >>
> >>
> >> >--Original Message--
> >> >Take your last production backup and restore it to your
> >> dev server.
> >> >
> >> >Or were you fibbing and delete a production database
> you
> >> had no backups for?
> >> >
> >> >"Brad G" <brad.guilbault@.cirquedusoleil.com> wrote in
> >> message
> >> >news:276e601c38f65$cc22d6d0$a601280a@.phx.gbl...
> >> >> Gentlemen, Can anyone save my life at the moment!
> >> >> I need to restore a DB that I accidentally erased
> using
> >> >> the Delete command in the Enterprise admin. I was on
> >> the
> >> >> wrong server and the DB names are the same.
> >> >> (No Backups were on this box since it's a dev box.)
> >> >> Can anyone tell me a way to get my DB back?
> >> >>
> >> >> Thanks,
> >> >>
> >> >> Brad
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Deleted Default DB, Need Hlp Fast!

I have stoopidly enough deleted default Db. That causes Enterprise Manager to be unable to work with my DB's. The default DB I deleted had no other functions other then being default DB, I mean it was outdated, and I had other DB's that contained all my importent work. They are still running, and I can view DB driven site hosted at localhost, even though default DB no longer excist. I am even able to upload new content, or add new users, so this means all my other DB's are fine. I can even see SQL server icon in my bottom right corner of my desktop, and it shows server running.

Now I am in the need of adding tables and rework some of my excisting tables and stored procedures, but I am not able to do that with Enterprise Manager, due to the lack of default Database.

How do I correct this problem? I have gotten one tip of doing the following:EXEC sp_defaultdb 'User', 'DB' but I am not sure what to do with this....tried to run it from command line, and put my username and the DB I would set to default but nothing happend.

So I need more details,step-by-step guiding will work, as I don't know a hole lot about Enterprise Manager and SQL.

Btw, this is my error in Enterpr.Managr:

A connection could not be established to MyComputer\VSDOTNET2003

Reason: Cannot open default database. Login failed..

Please verify SQL server is running and check your SQL server registration prpoerties and try again

Pls tell me there is a way to fix this problem.This is because youruser account is set to use a specific default db which is missing.

Try using the SA account or other administrative account to login and change your personal user account to have a different default db.|||Thx, but need also step-by-step guiding on howto do this.

Pls help me here.

s|||Pls tell me in small steps at a time howto fix this.
My account is actually put up as an admin account in winxp. There is an ADMIN account i can log in with, but as I know, just in safemode.

What is the SA account in SQL server? How can I gain access to that account?
Do I use Enterprise Manager, or another tool or command line?
I am able to view the "startup" picture in Enter.Mangr. like I always could, however when trying to connect to MyComputer/VSDotNet2003 I ca't because of the deleted account.
How can I set new default account using Enterprise Manager? Is there any script I can use? What buttons do I use if it is possible to use E.M.?

As you all see, I am not really familiar with E.M., and therefor need very detailed information on howto fix this.

Pls in small steps.

s|||Logon to your computer as Administrator
Open Enterprise Manager
Expand until you can see the MyComputer/VSDotNet2003 node.
Expand that so you can see the "Security" folder.
Expand the folder so you can see the "Logins" node
Click "Logins", on the right you should see a list of permitted logins.
Right-click the login in question, and choose 'Properties'
On the "General" tab, choose a new default database from the list.

Cheers
Ken|||You mean like when I set a security setting for a newly created DB?
When I try to expand the MyComputer/VSDotNet2003 node, I run into the same problem. This action can't be performed due to lack of default DB, and I get the same error as mentioned before:

A connection could not be established to MyComputer\VSDOTNET2003

Reason: Cannot open default database. Login failed..

Please verify SQL server is running and check your SQL server registration prpoerties and try again

So how then can I set new default DB?

What does people think of this? Actually as I write this, I get a note that this works:

From MS Support (http://support.microsoft.com/default.aspx?scid=kb;en-us;307864&Product=sql):

If the user's default database no longer exists, or has been marked suspect, use the ISQL command line utility to change the user's default database to a database that is currently available for a connection.

1. At a command line prompt, type the following and then press ENTER: C:\>isql -E

2. At the isql prompt, type the following and then press ENTER: 1>sp_defaultdb 'user's_login', 'master'

3. At the second prompt, type the following and then press ENTER:2>go

Thx a lot to DarrelNorton for the respond, and all of you who took the time to helping a newbie.

Thx

slopee|||I'm glad you got the problem solved.

In the case of using Enteprise Manager, what I was suggesting is that you logon with a different user account (i.e. an another account that can access SQL Server), whose default database has *not* been deleted.

So, if you deleted the default database for User1, then logon as User2, and go into EM, and change the default database for User1. Then User1 should be able to login to SQL Server again.

Cheers
Ken

Delete TempDB Database on SQL Server 2000 EE

I inherited a SQL Server 2000 Enterprise Edition database server that has
system databases that do appear in the SQL Server 2000 Enterprise Manager
GUI. How can get the system databases to appear in the SQL Server 2000
Enterprise Manager GUI? When I opened the Query Analyzer the system
databases appear.
Please help me resolve this issue.
Thanks,Right click on the name of the server and choose Edit SQL Server
Registration Properties. Check the box that says "Show system databases and
system objects"
Click OK
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,|||"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,|||"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,

Delete TempDB Database on SQL Server 2000 EE

I inherited a SQL Server 2000 Enterprise Edition database server that has
system databases that do appear in the SQL Server 2000 Enterprise Manager
GUI. How can get the system databases to appear in the SQL Server 2000
Enterprise Manager GUI? When I opened the Query Analyzer the system
databases appear.
Please help me resolve this issue.
Thanks,
Right click on the name of the server and choose Edit SQL Server
Registration Properties. Check the box that says "Show system databases and
system objects"
Click OK
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,
|||"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,
|||"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:E9636172-06F5-4DAF-9EC0-992C661C48F1@.microsoft.com...
> I inherited a SQL Server 2000 Enterprise Edition database server that has
> system databases that do appear in the SQL Server 2000 Enterprise Manager
> GUI. How can get the system databases to appear in the SQL Server 2000
> Enterprise Manager GUI? When I opened the Query Analyzer the system
> databases appear.
> Please help me resolve this issue.
> Thanks,

delete sql server registration in tsql

Is there any way in tsql to delete a sql server registration? (I know
how to do this from Enterprise Manager).

Thanks!
Jennifer(J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

Is there any way in tsql to delete a sql server registration? (I know
how to do this from Enterprise Manager).

There are undocumented extended stored procedures to manipulate the
registry. However, starting with SQL 2000 SP4, they were changed to
permit changes only within the tree of the SQL Server instance, if
memory serves.

In any case, I would say that it does not really make sense. Enterprise
Manager is a client, and SQL Server is a server. A server sends data to
its clients on requests. It does not send requests to the clients.

If you want manipulate the registry on remote machines, Windows offers
more direct ways to do this, and that is far better than sneaking around
through SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Thanks for the reply. Just to be clear, though. I wasn't trying to be
sneaky and manipulate registry on other machines. ;) I was trying to
unregister servers on my own computer. We have about 400 different
servers where I work - each one is the SQL server for a restaurant. I
have every one of them registered on my computer so that if I need to,
I can get to their database. Well...a lot of changes were made
recently, and now the user name/password that is associated with said
servers is not valid. All I wanted to do was get those unregistered
from the Enterprise Manager so I don't have to see them. I was hoping
I could select all of them in Enterprise Manager, right click and
delete the registration. But it doesn't work like that. I don't
really see myself doing that 400 times for each individual server. :)
So I was just wondering if I could do it with a loop or something along
those lines from Query Analyser. I see now that is really not
possible.

Thanks,
Jennifer

Erland Sommarskog wrote:

Quote:

Originally Posted by

(J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

Is there any way in tsql to delete a sql server registration? (I know
how to do this from Enterprise Manager).

>
There are undocumented extended stored procedures to manipulate the
registry. However, starting with SQL 2000 SP4, they were changed to
permit changes only within the tree of the SQL Server instance, if
memory serves.
>
In any case, I would say that it does not really make sense. Enterprise
Manager is a client, and SQL Server is a server. A server sends data to
its clients on requests. It does not send requests to the clients.
>
If you want manipulate the registry on remote machines, Windows offers
more direct ways to do this, and that is far better than sneaking around
through SQL Server.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||I could be totally wrong here but . . .

As far as I know there is a MMC folder that has the data you want.

<drive>:\Documents and Setting\<your logon name>\Application
Data\Microsoft\MMC

Adrian

Jennifer wrote:

Quote:

Originally Posted by

Erland,
>
Thanks for the reply. Just to be clear, though. I wasn't trying to be
sneaky and manipulate registry on other machines. ;) I was trying to
unregister servers on my own computer. We have about 400 different
servers where I work - each one is the SQL server for a restaurant. I
have every one of them registered on my computer so that if I need to,
I can get to their database. Well...a lot of changes were made
recently, and now the user name/password that is associated with said
servers is not valid. All I wanted to do was get those unregistered
from the Enterprise Manager so I don't have to see them. I was hoping
I could select all of them in Enterprise Manager, right click and
delete the registration. But it doesn't work like that. I don't
really see myself doing that 400 times for each individual server. :)
So I was just wondering if I could do it with a loop or something along
those lines from Query Analyser. I see now that is really not
possible.
>
Thanks,
Jennifer
>
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

>(J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

>>Is there any way in tsql to delete a sql server registration? (I know
>>how to do this from Enterprise Manager).

>There are undocumented extended stored procedures to manipulate the
>registry. However, starting with SQL 2000 SP4, they were changed to
>permit changes only within the tree of the SQL Server instance, if
>memory serves.
>>
>In any case, I would say that it does not really make sense. Enterprise
>Manager is a client, and SQL Server is a server. A server sends data to
>its clients on requests. It does not send requests to the clients.
>>
>If you want manipulate the registry on remote machines, Windows offers
>more direct ways to do this, and that is far better than sneaking around
>through SQL Server.
>>
>>
>--
>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>Books Online for SQL Server 2005 at
>http://www.microsoft.com/technet/pr...oads/books.mspx
>Books Online for SQL Server 2000 at
>http://www.microsoft.com/sql/prodin...ions/books.mspx

>

|||Adrian,

Thank you for the response. I found the folder and while the files did
look interesting, they did not appear to have the info in there.

Thanks,
Jennifer
abc wrote:

Quote:

Originally Posted by

I could be totally wrong here but . . .
>
As far as I know there is a MMC folder that has the data you want.
>
<drive>:\Documents and Setting\<your logon name>\Application
Data\Microsoft\MMC
>
>
Adrian
>
>
Jennifer wrote:

Quote:

Originally Posted by

Erland,

Thanks for the reply. Just to be clear, though. I wasn't trying to be
sneaky and manipulate registry on other machines. ;) I was trying to
unregister servers on my own computer. We have about 400 different
servers where I work - each one is the SQL server for a restaurant. I
have every one of them registered on my computer so that if I need to,
I can get to their database. Well...a lot of changes were made
recently, and now the user name/password that is associated with said
servers is not valid. All I wanted to do was get those unregistered
from the Enterprise Manager so I don't have to see them. I was hoping
I could select all of them in Enterprise Manager, right click and
delete the registration. But it doesn't work like that. I don't
really see myself doing that 400 times for each individual server. :)
So I was just wondering if I could do it with a loop or something along
those lines from Query Analyser. I see now that is really not
possible.

Thanks,
Jennifer

Erland Sommarskog wrote:

Quote:

Originally Posted by

(J.Evans.1970@.gmail.com) writes:
>Is there any way in tsql to delete a sql server registration? (I know
>how to do this from Enterprise Manager).
There are undocumented extended stored procedures to manipulate the
registry. However, starting with SQL 2000 SP4, they were changed to
permit changes only within the tree of the SQL Server instance, if
memory serves.
>
In any case, I would say that it does not really make sense. Enterprise
Manager is a client, and SQL Server is a server. A server sends data to
its clients on requests. It does not send requests to the clients.
>
If you want manipulate the registry on remote machines, Windows offers
more direct ways to do this, and that is far better than sneaking around
through SQL Server.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Jennifer (J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

All I wanted to do was get those unregistered from the Enterprise
Manager so I don't have to see them. I was hoping I could select all of
them in Enterprise Manager, right click and delete the registration.
But it doesn't work like that. I don't really see myself doing that 400
times for each individual server. :) So I was just wondering if I could
do it with a loop or something along those lines from Query Analyser. I
see now that is really not possible.

Nah, it's possible. But I doubt that it is any faster than to delete
all those servers by hand. You would learn about registry hacking in
the most inconvient way.

Anyway, the brute method is to run RegEdit, and go to
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X
and then go on a delete craze there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Totally awesome! Thanks! It took all of 2 seconds. Much much faster.

Erland Sommarskog wrote:

Quote:

Originally Posted by

Jennifer (J.Evans.1970@.gmail.com) writes:

Quote:

Originally Posted by

All I wanted to do was get those unregistered from the Enterprise
Manager so I don't have to see them. I was hoping I could select all of
them in Enterprise Manager, right click and delete the registration.
But it doesn't work like that. I don't really see myself doing that 400
times for each individual server. :) So I was just wondering if I could
do it with a loop or something along those lines from Query Analyser. I
see now that is really not possible.

>
Nah, it's possible. But I doubt that it is any faster than to delete
all those servers by hand. You would learn about registry hacking in
the most inconvient way.
>
Anyway, the brute method is to run RegEdit, and go to
HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X
and then go on a delete craze there.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx