MSSQL Queries to help you search through your databases easier

[cWAR]

Here are some more MSSQL Commands for your kal servers.


How to search for a player in your database - Click on Kal.db and then Create a new Query -

Code:
SELECT * From dbo.Player WHERE Name = 'playersname'
Execute the query:
It should then show you the player and you can search for it and edit it in your DB if need be.


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

Code:
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.
(Deletes all the data inside those tables.)

Code:
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.
WARING, THIS WILL DELETE ALL YOUR BACK UP FILES. YOU WILL NOT BEABLE TO
ROLL BACK YOUR DB.
To Clear Logs:
Code:
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.

Code:
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

~~~~~~~~~~~~~~~~

The best times to run a "Auto" back up when you are sleeping or no GMs are
online to do a back up of your DBs (AUTH and DB).

Code:
Full Backup Sunday 4:00PM
Differnential Backup Monday - Saturday 8:00AM, 12:00PM, 5:00PM, 8:00PM
Transaction log Backup Monday - Saturday 9:00AM - 7:00PM

 

[localarea]

nice thanks for that,
found that helpful