[Tool] MSSQL Commands

[Ace-SG1-]

4 MSSQL Scripts.

Just like to say Thank you to DarkZONE for telling me some of this info.
(DB cleaner and Log cleaner)

Ask if you need any help.

PS: Remove "SA" and replace it with your own DB's name.

IP Checker - Checks if a players IP address is in the Server more than 1x.

SELECT ip, acct_name count
FROM ip_log
GROUP BY ip, acct_name
HAVING count(*) > 1
ORDER BY ip 

AUTH/DB Cleaner - This restores your AUTH/DB Tables back before your server started.

TRUNCATE TABLE "banned_ips"
TRUNCATE TABLE "BuffRemain"
TRUNCATE TABLE "Event"
TRUNCATE TABLE "Friend"
TRUNCATE TABLE "Guild"
TRUNCATE TABLE "GuildAlliance"
TRUNCATE TABLE "GuildCastle"
TRUNCATE TABLE "GuildMember"
TRUNCATE TABLE "GuildWar"
TRUNCATE TABLE "Item"
TRUNCATE TABLE "ItemRestored"
TRUNCATE TABLE "Log"
TRUNCATE TABLE "Mail"
TRUNCATE TABLE "MLM"
TRUNCATE TABLE "MLMMsg"
TRUNCATE TABLE "NameChanged"
TRUNCATE TABLE "PKBulletin"
TRUNCATE TABLE "Player"
TRUNCATE TABLE "PlayerDeleted"
TRUNCATE TABLE "Quest"
TRUNCATE TABLE "ReservedName"
TRUNCATE TABLE "Shortcut"
TRUNCATE TABLE "Skill"
TRUNCATE TABLE "Statistics"
TRUNCATE TABLE "Teleport"
TRUNCATE TABLE "user_ips"
TRUNCATE TABLE "AuthStatistics"
TRUNCATE TABLE "CLogin"
TRUNCATE TABLE "CNum"
TRUNCATE TABLE "ExpTable"
TRUNCATE TABLE "GuildRank"
TRUNCATE TABLE "IP"
TRUNCATE TABLE "iplog"
TRUNCATE TABLE "ItemBuy"
TRUNCATE TABLE "ItemBuy_backup"
TRUNCATE TABLE "ItemDetail"
TRUNCATE TABLE "ItemInfo"
TRUNCATE TABLE "Log"-
TRUNCATE TABLE "Login"
TRUNCATE TABLE "LoginDeleted"
TRUNCATE TABLE "NMLogin"
TRUNCATE TABLE "NMLogin2"
TRUNCATE TABLE "Notice"
TRUNCATE TABLE "PrizeWinner"
TRUNCATE TABLE "Rank"
TRUNCATE TABLE "ResetPK"
TRUNCATE TABLE "Statistics" 

Log Cleaner - You know them 1GB files? well this cleans them out.

To Clear Logs.

Use Master
go

Backup Log [SA] with truncate_only
go

Use [SA]
go

DBCC shrinkfile ([SA_Log], 2)
go

-- data
Use Master
go

Backup Log [SA] with truncate_only
go

Use [SA]
go

DBCC shrinkfile ([SA_Data], 1)
go

-- Get fileid
use [SA]
go

select * from sysfiles 

DB Back up to more than 1 file at a time.

BACKUP DATABASE [SA] TO
DISK = N'C:\databace\SA.bak',  
DISK = N'C:\SA.bak',  
DISK = N'D:\SA.bak' 

WITH INIT, FORMAT,  NAME = N'SA-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO 

[MentaL]

[DeathArt]

AUTH/DB Cleaner - This restores your AUTH/DB Tables back before your server started.

No, it trunicates your tables. Restore means to restore data in them, not delete data.

And you should explain precisely WHAT log you're cleaning. Because the transaction log you're cleaning then removes the ability to do a rollback.

[SAL-HOAX]

No, it trunicates your tables. Restore means to restore data in them, not delete data.

And you should explain precisely WHAT log you're cleaning. Because the transaction log you're cleaning then removes the ability to do a rollback.

Father-DeathArt :-DD hehe... GURU of MSSQL etc...

[Ace-SG1-]

hey hey hey... i was told it was some logs... thats it.

[DeathArt]

lol.. you're releasing SQL scripts, not knowing what they do...

[TrueDarKZONE]

hes right ace if some 1 was to run the log cleaner on there db with out knowing what the hell it does it will fuck it up

[cristy_abaddon]

ty ace ;) ya and dont use that if u dont know what to do :D

[Ace-SG1-]

ty ace ;) ya and dont use that if u dont know what to do :D

sooo right... last night was fun... LOL... restoring a fucked up DB useing the MDFs... LOL...

and thank you.