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 Records From Sql Table

I AM TRYING TO DELETE RECORDS FROM THE SQL SERVER TABLE,

I AM USING THE COMMAND BELOW TO DELETE THOSE 71 RECORDS BUT MY WHOLE TABLE GOT DELETED AND HAD TO RECOVER FROM BACKUP..
SO WHAT IS WRONG WITH THE SYNTAX GIVEN BELOW

DELETE FROM dbo.Payment_Placement_AIMS
WHERE EXISTS
(select * from Payment_Placement_AIMS INNER JOIN
TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM') AND (Payment_Placement_AIMS.EMP_TYPE = 'First

employment'));since you are using exists in the where clause it is returning true value and the condition is satisfied in result the whole table is deleted (no specific record is mentioned)

following query will help you

DELETE FROM dbo.Payment_Placement_AIMS
WHERE Payment_Placement_AIMS.JC_ID in
(select Payment_Placement_AIMS.JC_ID from Payment_Placement_AIMS INNER JOIN
TESTING ON Payment_Placement_AIMS.JC_ID = TESTING.JC_ID
WHERE Payment_Placement_AIMS.Date_Stamp > '10/21/2007 12:00:00 AM') AND (Payment_Placement_AIMS.EMP_TYPE = 'First

employment'))sql

Deleting primary key when no foreign records exist?

Is there a trigger that would handle this situation? I have a 1 to
many between two tables and would like the primary key deleted if the
last foreign record is deleted.
Thanks in advance!Sure, in an AFTER trigger on the child table, something like (including a
scenario to try it out.):
set nocount on
go
create table parent
(
parentKey int primary key
)
create table child
(
childKey int primary key,
parentKey int foreign key references parent(parentKey)
)
insert into parent
select 1
union all
select 2
union all
select 3
insert into child
select 1,1
union all
select 2,1
union all
select 3,1
union all
select 4,2
union all
select 5,2
union all
select 6,2
union all
select 7,3
union all
select 8,3
go
create trigger child$deleteTrigger
on child
after delete
as
--be sure to add error handling
delete parent
--this gets all parent rows that are related to the deleted child rows based
on the migrated key from the parent
where exists (select 1
from deleted
where parent.parentKey = deleted.parentKey)
--this excludes parents where a child still exists
and not exists (select 1
from child
join deleted
on child.parentKey =
deleted.parentKey
where parent.parentKey = deleted.parentKey)
go
delete child where parentkey = 1
select *
from parent
left outer join child
on parent.parentKey = child.parentKey
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1139625347.478136.146410@.g14g2000cwa.googlegroups.com...
> Is there a trigger that would handle this situation? I have a 1 to
> many between two tables and would like the primary key deleted if the
> last foreign record is deleted.
> Thanks in advance!
>|||Many thanks Louis!|||Yes, you can do this with a trigger, if you like to write procedural,
proprietary code. Look for a COUNT(*) = 0 in a PK-FK join.
Another way to do this is to put one "child" item the same table as the
"parent" since you seem to require at least one "child" as part of the
design. When you delete the "only child", you have to delete the
parent.
Oh, did I mention that the code is messy?|||> Another way to do this is to put one "child" item the same table as the "p
arent"
Joe,
Are you saying your solution conforms to 3NF?
What if later you'll need to delete the child row stored along with the
parent? You'll have to move another row from the child table to the
parent one. Are you claiming it's less messy than Louis's solution?
Also instead of selects against the child table you'll have to select
against a union of the child and the parent, right?
How would you enforse a unique constraint on the child?sql

deleting only 1000 rows at the time

Hi!
I have a huge table with about 40 mil of rows. I just discovered
that 10 mil can be safely deleted however, I do not want to delete all
in one shot. I would like to delete 10000 rows at the time.
This will start removing all 10 millions:
delete from apps where code = 'I'
how can I delete 10000 at the time?
Thank you,
T.On Sep 7, 10:42 am, tolcis <nytolly...@.gmail.com> wrote:
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
DELETE TOP(100) FROM ...|||What version of SQL Server are you running? With SQL Server 2005 the
TOP keyword is supported for DELETE, so it would look like:
DELETE TOP 100 FROM apps WHERE code = 'I'
SQL Server 2000 does not support TOP directly as part of DELETE so it
is a bit more complicated.
DELETE apps
FROM (SELECT TOP 1000 * FROM apps) as X
WHERE apps.appsKey = X.appsKey
Roy Harvey
Beacon Falls, CT
On Fri, 07 Sep 2007 08:42:54 -0700, tolcis <nytollydba@.gmail.com>
wrote:
>Hi!
>I have a huge table with about 40 mil of rows. I just discovered
>that 10 mil can be safely deleted however, I do not want to delete all
>in one shot. I would like to delete 10000 rows at the time.
>This will start removing all 10 millions:
>delete from apps where code = 'I'
>how can I delete 10000 at the time?
>Thank you,
>T.|||tolcis,
It depends on your SQL Server version, but this should work in 2000 and
2005.
DECLARE @.HowMany INT
SET ROWCOUNT 10000
SET @.HowMany = 1
WHILE @.HowMany > 0
BEGIN
delete from apps where code = 'I'
SELECT @.HowMany = @.@.Rowcount
END
SET ROWCOUNT 0
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1189179774.558864.201630@.o80g2000hse.googlegroups.com...
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
>|||After playing a bit, seems that a temp table reduce the system impact. On
the other hand, I have been logging in on Sundays to reduce my behemoth 120
million records... have it down to about 90 million at this time. I use the
wait for delay so that the system can move away from the big table to perform
other IO. The wait for delay can be any size. I chose five seconds below.
create table #deletedkeys(gianttablendx bigint)
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
insert into #deletedkeys(gianttablendx )
select top 1000 raw_event_key from gianttable where gianttablendx < 122786624
delete from bill_raw_transactions where gianttablendx in (select
gianttablendx from #deletedkeys)
truncate table #deletedkeys
WAITFOR DELAY '00:00:05'
GO
drop table #deletedkeys
--
Regards,
Jamie
"tolcis" wrote:
> Hi!
> I have a huge table with about 40 mil of rows. I just discovered
> that 10 mil can be safely deleted however, I do not want to delete all
> in one shot. I would like to delete 10000 rows at the time.
> This will start removing all 10 millions:
> delete from apps where code = 'I'
> how can I delete 10000 at the time?
> Thank you,
> T.
>

Deleting entries under a "Category" that is being deleted??

Hello. I have a simple project using 3 tables: Categories, Subcategories, and Items. I have just gotten my insert, edit, and delete functions to work, but I've noticed a problem I hope someone can help with:

When I delete a certain category (lets say "Restaurants"), the subcategories and items that were in that category still remain in the database/table. (If I delete "Restaurants", the subcategories "Italian", "Seafood", etc - as well as any items in those subcategories - are not deleted).

So what would I need to do to delete any Subcategory that is in the deleted Category (shares a CategoryID) and then delete all Items in those Subcategories?

For reference, all Subcategories in a Category have reference to that CategoryID, and all Items in a Subcategory have a "SubcategoryID" field. I understand that I need to traverse the tables and remove all Subcategories with the CategoryID being deleted, but since the Items do not have a reference to the CategoryID, how would I delete those as well?

Thanks to whoever can help me out here!

First of all, you should add constraints to your tables. They will prevent you from removing Categorys if you have reletad Subcategorys in order to maintain referential integrity.

And then you have 2 options, the first is that in the constraint you declare that in deleting a Category, all releted SubCategorys (and Items) are deleted also.

The other option is that you delete the first all items in the table item that are related to subcategorys that are related to the category that you want to delete, then delete all items in the table subcategory that are related to the category that you want to delete and finally delete the category form the table category:

DELETE FROM item WHERE item.subcategoryID In (SELECT subcategory.subcategoryID FROM category INNER JOIN subcategory ON category.categoryID = subcategory.categoryID WHERE category.categoryID = @.categoryID);

DELETE FROM subcategory WHERE subcategory.category In (SELECT category.categoryID FROM category WHERE category.categoryID = @.categoryID);

DELETE FROM category WHERE category.categoryID = @.categoryID;

|||

Thanks alot for the reply! Actually, a bit after I posted the question I began to do something very similar to what your 2nd option above. What I thought to do was to remove the "Delete" button on the GridView and replace it with a Select Button whose text was "Delete", and then when the selectedIndexChanged event for the GridView is called (meaning when the user clicks the select button (which says "Delete"), I used this statement to delete the "items" in that category:

DELETE FROM items WHERE items.subcategoryID IN (SELECT subcategories.subcategoryID FROM subcategories WHERE subcategories.CategoryID = " & Me.GridView1.SelectedValue

However, when I tried to run this, I got an error since there were no actual Items in the Items table who were children of the selected category to delete. So I began to write a FOR EACH statement to go through the Items table and search for any items who were nested in that category, and if there were any, then call the above command. This seems like alot of work though, and I'm really interested in these "Constraints" you spoke of.

Could you please give more info/examples of how to declare and use a constraint on a delete command such as what I want to do? If possible, I'd like to just have the constraint declare that if a category is deleted, all subcategories and items under that category are also deleted. If this can be done automatically, that would be perfect!

I've never heard of or seen these constraints in action before though, so any help or links you can give will help alot! I'm done with work for the day, so take your time and I will check them out tomorrow! Thanks!

|||

For more information about constraints search the internet, keywords "SQL SERVER FOREIGN KEY CONSTRAINTS", For example:

http://technet.microsoft.com/en-us/library/ms175464.aspx

If you want more information about constraints that will also delete related records in other tables, search for "SQL SERVER CASCADING CONSTRAINTS", for example:

http://technet.microsoft.com/en-us/library/ms186973.aspx

But the error that you got when running the delete statement, is not because there where no records. If there are records they will be deleted, if not, there will be no error. I think that the SQL statement is not correct, I think you forgot to add the ")" and the end!

And when using parameters (in this case the categoryID), it's better to useparameterized Queries!

|||

Ah, yeah I just forgot to type the ) in the post (it wasn't directly copied from the code). I'm not sure what the error pertained to, however I have read up on the Cascading Delete funtion and implemented it, and it works great! I didn't think to look into a solution using SQL Server instead of Visual Studio.

Thanks alot for your help!

Deleting DB

I just restored a database and want to delete it (part of testing things). It is telling me that it cannot be deleted because it is in use. No one is using it. How do I get rid of it?
Thanks
USE master
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE dbname
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:CF19874A-09FD-4B05-8C78-7A9C51BFD3B0@.microsoft.com...
> I just restored a database and want to delete it (part of testing things).
It is telling me that it cannot be deleted because it is in use. No one is
using it. How do I get rid of it?
> Thanks
|||Perhaps you have an open connection from Query Analyzer on your machine.
/*this shows the connections and what database each is using: */
exec sp_who2
go
You could KILL a SPID or you can run this command (remove the comment block
first) to set the database to single user mode:
/*
ALTER DATABASE <YourDBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
*/
Keith
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:CF19874A-09FD-4B05-8C78-7A9C51BFD3B0@.microsoft.com...
> I just restored a database and want to delete it (part of testing things).
It is telling me that it cannot be deleted because it is in use. No one is
using it. How do I get rid of it?
> Thanks

Deleting DB

I just restored a database and want to delete it (part of testing things). It is telling me that it cannot be deleted because it is in use. No one is using it. How do I get rid of it?
Thanks
USE master
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE dbname
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:CF19874A-09FD-4B05-8C78-7A9C51BFD3B0@.microsoft.com...
> I just restored a database and want to delete it (part of testing things).
It is telling me that it cannot be deleted because it is in use. No one is
using it. How do I get rid of it?
> Thanks
|||Perhaps you have an open connection from Query Analyzer on your machine.
/*this shows the connections and what database each is using: */
exec sp_who2
go
You could KILL a SPID or you can run this command (remove the comment block
first) to set the database to single user mode:
/*
ALTER DATABASE <YourDBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
*/
Keith
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:CF19874A-09FD-4B05-8C78-7A9C51BFD3B0@.microsoft.com...
> I just restored a database and want to delete it (part of testing things).
It is telling me that it cannot be deleted because it is in use. No one is
using it. How do I get rid of it?
> Thanks
sql

Deleting DB

I just restored a database and want to delete it (part of testing things).
It is telling me that it cannot be deleted because it is in use. No one is
using it. How do I get rid of it?
ThanksUSE master
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE dbname
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:CF19874A-09FD-4B05-8C78-7A9C51BFD3B0@.microsoft.com...
> I just restored a database and want to delete it (part of testing things).
It is telling me that it cannot be deleted because it is in use. No one is
using it. How do I get rid of it?
> Thanks|||Perhaps you have an open connection from Query Analyzer on your machine.
/*this shows the connections and what database each is using: */
exec sp_who2
go
You could KILL a SPID or you can run this command (remove the comment block
first) to set the database to single user mode:
/*
ALTER DATABASE <YourDBName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
*/
Keith
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:CF19874A-09FD-4B05-8C78-7A9C51BFD3B0@.microsoft.com...
> I just restored a database and want to delete it (part of testing things).
It is telling me that it cannot be deleted because it is in use. No one is
using it. How do I get rid of it?
> Thanks

Deleting and recreating database with same name

Question...
I created a database, but I want to totally blow that database away
and recreate it. I right-clicked on the DB and deleted it, no longer
there. Now when I go to recreate the database with the same name, I
get the following error:
Error 5170: Cannot create file 'C:\...MDF' because it already exists.
CREATE DATABASE failed. Some file names listed could not be created.
Check previous errors.
Okay, when I tell SQL I want to delete a database, I want it 100%
deleted. Apparently SQL's idea of Delete and mine are different, so
any suggestions on how I can totally remove this database? Since MS
is known to do plenty hidden the background, I don't want to just
delete the .MDF file, since I'm sure this will confuse SQL quite a
bit.
So back to the root question... how do I 100% delete a database where
I can create another DB with the same name?
Thanks,
Alex.You'll need to delete the MDF file it's complaining about from the OS.
HTH
Ryan Waight, MCDBA, MCSE
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.|||> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted.
This is what DROP DATABASE does, which is the command called from QA and EM. Unless you detach it,
of course.
> Apparently SQL's idea of Delete and mine are different
No, something strange must have happened.
Just try to delete the database files. If the database still exists in the eyes of SQL Server, then
the files are locked and you can't delete them.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.

Deleting a Pubisher Database

Dear All,
I recently set up a DB for replication testing. My testing
is complete so I deleted the 'Publisher' stuff in the test
database, and managed to delete the subscriber.
I then went to delete the publisher db and got the
following error message Error 3724: Cannot drop the
database 'Test' because it is being used in replication',
well it isn't.
Can anyone tell me what to do ?
Thanks
Jim
go to tools, replication, configure publishers, subscribers, and
distributor, click on the published databases tab, and uncheck any check
marks for transactional/snapshot or merge which correspond to the database
you are trying to drop.
Then try to drop your database again.
"Jimbo" <anonymous@.discussions.microsoft.com> wrote in message
news:213b01c49f15$d0c09c70$a501280a@.phx.gbl...
> Dear All,
> I recently set up a DB for replication testing. My testing
> is complete so I deleted the 'Publisher' stuff in the test
> database, and managed to delete the subscriber.
> I then went to delete the publisher db and got the
> following error message Error 3724: Cannot drop the
> database 'Test' because it is being used in replication',
> well it isn't.
> Can anyone tell me what to do ?
> Thanks
> Jim
|||Thanks Hilary.
I think the term 'I'm not worthy, I'm not worthy' is
appropiate here. ;)
Jim

>--Original Message--
>go to tools, replication, configure publishers,
subscribers, and
>distributor, click on the published databases tab, and
uncheck any check
>marks for transactional/snapshot or merge which
correspond to the database
>you are trying to drop.
>Then try to drop your database again.
>"Jimbo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:213b01c49f15$d0c09c70$a501280a@.phx.gbl...
testing[vbcol=seagreen]
test[vbcol=seagreen]
replication',
>
>.
>

Deletes occurring at publisher

I'm using Merge replication on a table with an autoidentity field. All
users are entering data at the publisher (no records are ever deleted),
and at the subscribers the data is read-only. I'm having a problem
with records being deleted at the publisher. I'm assuming it has to do
with a subscriber having problems, but I can't track it down.
Moving forward (SQL 2005), I want to change the replication type to
transactional. Could someone please tell me the best type of
replication to use in order to prevent records from being deleted at
the publisher?
Any help would be greatly appreciated.
Thanks,
Amy Marshall
Does anything show up in the conflict viewer? The deletes could be logged
here. Are you using join filters? These can cause these types of deletes,
but normally not on the publisher.
Transactional replication is designed for one way replication. It is not
clear to me what your data flow requirements are. With transactional
replication users will be able to delete data in any location, but only
deletes occurring on the publisher will be replicated to the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<marshallae@.bowater.com> wrote in message
news:1138635297.513324.39100@.g47g2000cwa.googlegro ups.com...
> I'm using Merge replication on a table with an autoidentity field. All
> users are entering data at the publisher (no records are ever deleted),
> and at the subscribers the data is read-only. I'm having a problem
> with records being deleted at the publisher. I'm assuming it has to do
> with a subscriber having problems, but I can't track it down.
> Moving forward (SQL 2005), I want to change the replication type to
> transactional. Could someone please tell me the best type of
> replication to use in order to prevent records from being deleted at
> the publisher?
> Any help would be greatly appreciated.
> Thanks,
> Amy Marshall
>
|||Amy,
the issue might be caused by compensating changes. Please take a look at :
http://support.microsoft.com/default...&Product=sql2k
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This morning I had 264 conflicts, which I kept to make sure I got the
deleted records back into the table, but this was the first time there
were any conflicts at all for that table. We are not using any join
filters on this table. I was thinking of using transactional
replication, because all entries into the problem table are done at the
publisher, and the inputs into that table are synched to the
subscribers based on a timeframe that is set from an application which
determines sync times. (Most users set synch times anywhere from 60
minutes - 240 minutes).
What's weird, is that there is a table that is in the same merge
package, that links to an ID in the problem table, but those records
are NEVER deleted at the publisher (no foreign key constraint exists
between the 2 tables). This one table seems to have the problem, and
while monitoring replication, I rarely ever see problems with
subscribers and this package.
Thanks for your input.
Amy

DeletedFlag field and unique keys

I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||

A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:

ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.

I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.

The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.

DeletedFlag field and unique keys

I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||

A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:

ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.

I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.

The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.

deleted xp_cmdshell... how do I restore it?

For security reasons I dropped xp_cmdshell from my master database. Now
I find that I need it once again... is there a way to restore it or
somehow copy it from another database server? I've tried to use
enterprise manager to DTS it over but I can't DTS anything from the
master database. Is there any other way?
Thanks in advance.
Rsto create it back use the following and let know if it works:
Use Master
sp_addextendedproc xp_cmdshell, 'xplog70.dll'

deleted xp_cmdshell... how do I restore it?

For security reasons I dropped xp_cmdshell from my master database. Now
I find that I need it once again... is there a way to restore it or
somehow copy it from another database server? I've tried to use
enterprise manager to DTS it over but I can't DTS anything from the
master database. Is there any other way?
Thanks in advance.
Rsto create it back use the following and let know if it works:
Use Master
sp_addextendedproc xp_cmdshell, 'xplog70.dll'

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 tables

Is there any way to recover deleted table data (w. simple log)
Hi,
If the recovery model is SIMPLE you can not restore to a POINT_IN_TIME. So
in your case you can not recover directly. The only solution is to restore
the
last full database backup with a new name and copy the table to the real
database.
Thanks
Hari
"NO ROM BASIC" <.> wrote in message
news:OFGFY52CHHA.3660@.TK2MSFTNGP06.phx.gbl...
> Is there any way to recover deleted table data (w. simple log)
>
|||Thanks for reply
Ok, but there is no any backup. MDF file contains old data. Is there any
"undelete", "recover deleted" eg. sql commad or utility?
Possible a utilty for extract deleted sql row or table data because delete
function not really delete this data. I find a util, view mdf file
structure, pages and some info this pages but this insufficient to restore.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com>, haber iletisinde unlar
yazd:%23vT98y6CHHA.4992@.TK2MSFTNGP03.phx.gbl...
> Hi,
> If the recovery model is SIMPLE you can not restore to a POINT_IN_TIME. So
> in your case you can not recover directly. The only solution is to restore
> the
> last full database backup with a new name and copy the table to the real
> database.
> Thanks
> Hari
>
> "NO ROM BASIC" <.> wrote in message
> news:OFGFY52CHHA.3660@.TK2MSFTNGP06.phx.gbl...
>

deleted tables

Is there any way to recover deleted table data (w. simple log)Hi,
If the recovery model is SIMPLE you can not restore to a POINT_IN_TIME. So
in your case you can not recover directly. The only solution is to restore
the
last full database backup with a new name and copy the table to the real
database.
Thanks
Hari
"NO ROM BASIC" <.> wrote in message
news:OFGFY52CHHA.3660@.TK2MSFTNGP06.phx.gbl...
> Is there any way to recover deleted table data (w. simple log)
>|||Thanks for reply
Ok, but there is no any backup. MDF file contains old data. Is there any
"undelete", "recover deleted" eg. sql commad or utility?
Possible a utilty for extract deleted sql row or table data because delete
function not really delete this data. I find a util, view mdf file
structure, pages and some info this pages but this insufficient to restore.
Thanks
"Hari Prasad" <hari_prasad_k@.hotmail.com>, haber iletisinde þunlarý
yazdý:%23vT98y6CHHA.4992@.TK2MSFTNGP03.phx.gbl...
> Hi,
> If the recovery model is SIMPLE you can not restore to a POINT_IN_TIME. So
> in your case you can not recover directly. The only solution is to restore
> the
> last full database backup with a new name and copy the table to the real
> database.
> Thanks
> Hari
>
> "NO ROM BASIC" <.> wrote in message
> news:OFGFY52CHHA.3660@.TK2MSFTNGP06.phx.gbl...
>> Is there any way to recover deleted table data (w. simple log)
>