How to Create Clean Databases 92.03/92.04/100.02

[Archgeus]

How to Create Clean Databases 92.03/92.04/100.02

Since there's so many dmca´s takedowns for dbs, follow this guide for create both Planet and Shared DB's


Create a database with the name "PlanetDB_2800"

Ejecute the following query on it

-- ------------------------------ Table structure for DbInfo-- ----------------------------

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[DbInfo]') AND type IN ('U'))

    DROP TABLE [dbo].[DbInfo]

GO

CREATE TABLE [dbo].[DbInfo] ([version] int NULL)

GO

ALTER TABLE [dbo].[DbInfo] SET (LOCK_ESCALATION = TABLE)

GO

-- ------------------------------ Records of DbInfo-- ----------------------------

INSERT INTO [dbo].[DbInfo] ([version]) VALUES (N'363898')

GO

-- ------------------------------ procedure structure for spGetPlanetDatabaseVersion-- ----------------------------

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetPlanetDatabaseVersion]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))

    DROP PROCEDURE[dbo].[spGetPlanetDatabaseVersion]

GO

CREATE PROCEDURE [dbo].[spGetPlanetDatabaseVersion]
AS
BEGIN
BEGIN TRY
BEGIN TRAN
SELECT version FROM DbInfo
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION  
print ERROR_MESSAGE()
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END

Create a database named "SharedDB" and run the following query on it

-- ------------------------------ Table structure for DbInfo-- ----------------------------

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[DbInfo]') AND type IN ('U'))

    DROP TABLE [dbo].[DbInfo]

GO

CREATE TABLE [dbo].[DbInfo] ( [version] int NULL)

GO

ALTER TABLE [dbo].[DbInfo] SET (LOCK_ESCALATION = TABLE)

GO

-- ------------------------------ Records of DbInfo-- ----------------------------

INSERT INTO [dbo].[DbInfo] ([version]) VALUES (N'347932')

GO

-- ------------------------------ procedure structure for spGetPlanetDatabaseVersion-- ----------------------------

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetPlanetDatabaseVersion]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))

    DROP PROCEDURE[dbo].[spGetPlanetDatabaseVersion]

GO

CREATE PROCEDURE [dbo].[spGetPlanetDatabaseVersion]
AS
BEGIN
BEGIN TRY
BEGIN TRAN
SELECT version FROM DbInfo
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION  
print ERROR_MESSAGE()
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END

100.02 ONLY

Create a database named "CollectionDB" and run the following query on it

-- ------------------------------ Table structure for DbInfo-- ----------------------------

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[DbInfo]') AND type IN ('U'))

    DROP TABLE [dbo].[DbInfo]

GO

CREATE TABLE [dbo].[DbInfo] ( [version] int NULL)

GO

ALTER TABLE [dbo].[DbInfo] SET (LOCK_ESCALATION = TABLE)

GO

-- ------------------------------ Records of DbInfo-- ----------------------------

INSERT INTO [dbo].[DbInfo] ([version]) VALUES (N'368275')

GO

-- ------------------------------ procedure structure for spGetPlanetDatabaseVersion-- ----------------------------

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetPlanetDatabaseVersion]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))

    DROP PROCEDURE[dbo].[spGetPlanetDatabaseVersion]

GO

CREATE PROCEDURE [dbo].[spGetPlanetDatabaseVersion]
AS
BEGIN
BEGIN TRY
BEGIN TRAN
SELECT version FROM DbInfo
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION 
print ERROR_MESSAGE()
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END

UPDATE

To make clean dbs for TERA 100.02 replace in PlannetDB version 363898 to 375960 and SharedDB version 347932 to 374749

That's all, when you start the arbiter server it will create every single database and functions

UPDATE 2

How to create clean LogDB Database

1. Create a database named LogDB_2800 in MSSQL
2. Execute the attached query on it


Enjoy

View attachment LogDB_2800_Procedures.txt

 

[Archgeus]

Updated with DB versions of 100.02!

 

[rinpy]

TERA has achievements that only the first person on the server can get, so I would like to use this to initialize the database,
but I'm not sure what to do.

What state do the PlanetDB_2800 and SharedDB databases need to be in when this query is executed?
When I ran this query in navicat it showed

> Msg 2714, Level 16, State 3, Server PC-NAME, Procedure spGetPlanetDatabaseVersion, Line 1
There is already an object named 'spGetPlanetDatabaseVersion' in the database.
> [42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'spGetPlanetDatabaseVersion' in the database. (2714)

> Time: 0s

Do I need to delete PlanetDB_2800 or SharedDB before running it?

 

[Archgeus]

TERA has achievements that only the first person on the server can get, so I would like to use this to initialize the database,
but I'm not sure what to do.

What state do the PlanetDB_2800 and SharedDB databases need to be in when this query is executed?
When I ran this query in navicat it showed

> Msg 2714, Level 16, State 3, Server PC-NAME, Procedure spGetPlanetDatabaseVersion, Line 1
There is already an object named 'spGetPlanetDatabaseVersion' in the database.
> [42S01] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'spGetPlanetDatabaseVersion' in the database. (2714)

> Time: 0s

Do I need to delete PlanetDB_2800 or SharedDB before running it?

The databases needs to be empty, after that execute the querys accordingly with the database names/versions (92.03/100.02)

 

[rinpy]

The databases needs to be empty, after that execute the querys accordingly with the database names/versions (92.03/100.02)

Sorry for the rudimentary question, How can I empty the database?

 

[Archgeus]

Sorry for the rudimentary question, How can I empty the database?

Delete and then create again. After that, goto "new query" and execute the query

 

[rinpy]

Delete and then create again. After that, goto "new query" and execute the query

Thanks to your help, I was able to initialize the database successfully!

It is important to note that the account_characters table in accountdb must also be deleted at this time.
Also, those who want to keep account_info need to log out and log in again at the launcher.
This process will make the server start completely from 0.

 

[Archgeus]

The post has been updated with the LogDB procedures.

 

[TieLay]

does exists scripts create clean databases for 100.02?

 

[Archgeus]

does exists scripts create clean databases for 100.02?

Yep, check the update

 

[TieLay]

Yep, check the update

How about script for recreation "CollectionDB"?

Me ask, me answer:
View attachment CollectionDB.sql.txt