Dropped Table Recovery in SQL

[.Curious.]

Hello,

Somehow dbo.Users_Master table from PS_UserData and dbo.Chars table from PS_GameData have been dropped. I dont know how it happened my server had been online for 11 days ,players told they can not log in it says "Account does not exist" so when I checked accounts I could not find Users_Master table then I checkeked "Scheme Changes History" from "Reports" , I see that those two tables were dropped. I do not know how it happened PSMServer_Mgr Console is not available for connection ports are closed. SQL connection for remoot is not available also but I checked logs for console command inquiries but could not find anything. Anyway it is my next concern. First of all I need to find a way to recover dropped tables.

Any of you knows a way to recover those files ,It will be appreciated. As far as I know droping a table sends it to Recycle Bin and it can be recovered but I dont know how to do it.

I searched and figured some programs doing that work but Trial versions are recovering every 10th rows and price of the software is quite expensive.

There must be an other way because it is not deleted only dropped.

Thanks in advance.

[JohnHeatz]

You'll need to put up your backup files. This is something that happened a lot some time ago, so you should actually search a bit around the forums, you'll find some useful information about what causes this.

[.Curious.]

My backup belongs to 11 days ago so it will be my last option. I will only take those two missing tables from my backup belongs to 11 days ago.

[JohnHeatz]

There's no way to recover the ones you already lost. This is exactly why you need to be constantly taking backups from your database.

[.Curious.]

Last 11 days's accounts and chars are missing.
All other databases exist and are up to date.

I wonder If I can have any benefit from it. CharItems exist and up to date so if CharID matches with the Charname correctly it will mean : no loss for the matching char.

Accounts and chars older than 11 days will loose nothing. That sounds good but datas for 11 days old chars will also remain in CharItems. New created chars will match with the existing charIDs which do not have related CharNames in Chars. Am I wrong?

Any idea about it? Any theory ?

[JohnHeatz]

The only idea is to simply do an 11 days rollback.

[KarmaIsMe]

PM me curious and I will explain your issue. ^^

[castor4878]

Originally Posted by .Curious.

I wonder If I can have any benefit from it. CharItems exist and up to date so if CharID matches with the Charname correctly it will mean : no loss for the matching char.

Accounts and chars older than 11 days will loose nothing. That sounds good but datas for 11 days old chars will also remain in CharItems. New created chars will match with the existing charIDs which do not have related CharNames in Chars. Am I wrong?

Any idea about it? Any theory ?

an argument is not better because it is written in bold, so the first nice idea is to stop these useless bold.

your tables were drop because, one - hacked - stored procedure contains the drop statements or more likely because the ID/password are weak ("shaiya" ?).
of course, having an human dropping these bases and then denying having done is a very standard reason.

the Users_Master table is only used for login, it contains username and password, their values have no interest / meaning but of course each player have to know his/her pair values. you can imagine to recreate manually that table; a ticket (with former ID / password) sent by each player will be helpfull (it will avoid you to contact each of them to give a new ID/pwd). Still the UserUID fields will not be paired with some characters.

the Chars table does contain these characters and each record shall be linked by an UserUID, you can also imagine to manually build that table; some of the fields will be defined by player (such as toon's name, hair, face, size, ... - you will give them rename and appearance stones), or recomputed (eg the chars stats, you will provide some stats reset stone), levels are or are not an issue, if your server is a grinding one, you have an issue, if all players have a level whatever, just set it. the PvP kills (K1-4) are lost you can not assume their old values, same for money; the last (map & position) will be set to AH.

such table can be build with the CharItems information, eg if the weapon with a given CharID are all archer's weapon then that char. was an archer, and so on, for each items grouped by CharID.
but of course you have no clue to know with UserUID holds these items and thus this char.

if you have +/- 20 chars to handle, it can be possible to rebuild the table (also because, we know - even if not intimately - these players). if you have 100+ players just forget it, offer some free items / whatever to convince players to restart a new account and (re)create their toons. and of course communicate with the usual bullshit about the alleged strengthening of security measures.

Originally Posted by KarmaIsMe

PM me curious and I will explain your issue

so you can, eventually, handle him without the supervision of a third party?

[.Curious.]

Originally Posted by castor4878

an argument is not better because it is written in bold, so the first nice idea is to stop these useless bold.

I am so sorry I could not predict that it will disturb. I just do it because I always do, no offensive purpose.

your tables were drop because, one - hacked - stored procedure contains the drop statements or more likely because the ID/password are weak ("shaiya" ?).
of course, having an human dropping these bases and then denying having done is a very standard reason.

I searched for a clue to figure out if anybody could reach my databases but couldnt find any. I checked all logs to find a command from the console. There isnt any and ports are closed nevertheless I tried to connect to my console and SQL remootely but it did not work so I am nearly sure of that nobody accessed my databases. I also wonder if anybody had reached why they wouldnt have deleted all or at least CharItems which is the most important. Instead of deleting the rows all the whole data only prefered to drop two tables. It sounds me strange. I also checked Dedicated server logs nobody accessed it either. If you ask me so how did it happen ,I wonder too...

the Users_Master table is only used for login, it contains username and password, their values have no interest / meaning but of course each player have to know his/her pair values. you can imagine to recreate manually that table; a ticket (with former ID / password) sent by each player will be helpfull (it will avoid you to contact each of them to give a new ID/pwd). Still the UserUID fields will not be paired with some characters.

the Chars table does contain these characters and each record shall be linked by an UserUID, you can also imagine to manually build that table; some of the fields will be defined by player (such as toon's name, hair, face, size, ... - you will give them rename and appearance stones), or recomputed (eg the chars stats, you will provide some stats reset stone), levels are or are not an issue, if your server is a grinding one, you have an issue, if all players have a level whatever, just set it. the PvP kills (K1-4) are lost you can not assume their old values, same for money; the last (map & position) will be set to AH.

such table can be build with the CharItems information, eg if the weapon with a given CharID are all archer's weapon then that char. was an archer, and so on, for each items grouped by CharID.
but of course you have no clue to know with UserUID holds these items and thus this char.

if you have +/- 20 chars to handle, it can be possible to rebuild the table (also because, we know - even if not intimately - these players). if you have 100+ players just forget it, offer some free items / whatever to convince players to restart a new account and (re)create their toons. and of course communicate with the usual bullshit about the alleged strengthening of security measures.

I will wait till night if I can not find an other option I will write a quiry to delete datas not related to existing CharIDs (Which are rollbacked from backups of 11 days ago) in CharItems so new created charIDs will not match the existing ChatItems datas. When I only reroll two missing tables from backups of 11 days ago ,old players will not loose any items gained within 11 days they will only loose kills.

Thanks for infos and your interest.

[castor4878]

Originally Posted by .Curious.

I will wait till night if I can not find an other option I will write a quiry to delete datas not related to existing CharIDs (Which are rollbacked from backups of 11 days ago) in CharItems so new created charIDs will not match the existing ChatItems datas. When I only reroll two missing tables from backups of 11 days ago ,old players will not loose any items gained within 11 days they will only loose kills.

Deleting items not belonging to existing chars is:

delete from CharItems where CharID not in (select CharID from Chars)

-- you will also need to clean up the skills and quests per chars tables

delete from CharQuests where CharID not in (select CharID from Chars)
delete from CharSkills where CharID not in (select CharID from Chars)

but old players won't necessary lose their kills (and money), you can backup and restore them:

1) create a temporary table with these fields

use PS_GameData
GO

create table CharsSave (
	CharID int NOT NULL,
	Money int NOT NULL,
	K1 int NOT NULL,
	K2 int NOT NULL,
	K3 int NOT NULL,
	K4 int NOT NULL)

2) backup some fields of the current Chars table to the temp. table

insert into CharsSave (CharID, Money, K1, K2, K3, K4)
	select CharID, Money, K1, K2, K3, K4 from Chars

3) restore the Chars table from backup

4) restore Money & kills for existing Chars

update Chars
set Chars.Money=sv.Money, Chars.K1=sv.K1, Chars.K2=sv.K2, Chars.K3=sv.K3, Chars.K4=sv.K4
from CharsSave sv
where sv.CharID=Chars.CharID

5) drop the temp table

drop table CharsSave

[.Curious.]

Thanks for the queries.

I wonder If SQL may have given an error and dropped those two files on its own? For example my GameData is 23 GB. I dont even count ChatLog and GameLog cos I am restoring them every week with empty ones as they are only logs. The size of the data might have cause an error and it forces some tables to drop? Is it possible? Because I really dont have a weak point in my server to enable someone to access my SQL.

[castor4878]

I don't know any settings that result in an auto-drop of tables.

When MSSQL runs "out of resources" (such as disk space, or unique identifiers) the table refuses any further insertion or update - it switches in a "read only" mode - but it doesn't delete itself. still it's safe to truncate all ""not vital"" tables (such as logs).

you had list some possible reasons, one can imagine several others but without knowledge of your actual context, it won't be an useful game. I mean:
do you have a lot of admins ?
coz one of them may have wanted to play bad boy ...
oh you're alone, so do you have some "web services" ?
coz if one form is not protected an SQL insertion can result in lost of table ...
oh you haven't ... etc etc

[KarmaIsMe]

Ignoring your previous comment castor as I have already contacted the person about the issue and will inform him of more content as he replies. I will go out on a limb here and assume the reason for the tables being dropped and before I say something out of line I will continue to work privately since people seem to be so wrapped up in drama and insults on these forums as well as picking apart others works.

*decides to dignify your insult with a response.

So yes if I choose to help someone in private it is my right. As for the dropped tables it is not because of a auto drop. The tables were dropped intentionally and if my theory is correct I will assist him in a fix.

Karma

[JohnHeatz]

There isn't an actual "fix" for the already dropped ones, it was either SQL Injection, someone getting access to the host, or a human mistake in a query.

[.Curious.]

It happened again. I need a solution .