Restore into empty MSSQL database with maintainDB.cmd fails detects two tables.

I made a backup VIA the web UI  2008 MSSQL backend and and attempting to restore with the command line to 2012 MSSQL and the restore fails due to detecting two system tables :
D:\TeamCity\bin>maintainDB.cmd restore -F D:\ProgramData\JetBrains\TeamCity\backup\TeamCity_Backup_20130805_085302 -T D:\software\prodbackup\TeamCity_Backup_20130805_085302\config\database.properties
TeamCity maintenance tool. Copyright 2010 JetBrains s.r.o. All Rights Reserved.

Command line arguments: restore -F D:\ProgramData\JetBrains\TeamCity\backup\Team
City_Backup_20130805_085302 -T D:\software\prodbackup\TeamCity_Backup_20130805_0
85302\config\database.properties
Using TeamCity data directory: D:\ProgramData\JetBrains\TeamCity
Restoring from backup file: D:\ProgramData\JetBrains\TeamCity\backup\TeamCity_Ba
ckup_20130805_085302.zip
Backup created by TeamCity version:
        Version: 6.0.2
        Build number: 15857
        Data format version: 418
Using explicitly specified target database settings: D:\software\prodbackup\Team
City_Backup_20130805_085302\config\database.properties
Using target database
        database type: MSSQL
        database driver: <not specified>
        connection: jdbc:sqlserver://DBASQLD02;databaseName=TeamCity;integratedS
ecurity=true
        login as: <empty>
Connected to the Target DB successfully.
Cannot proceed with 'restore' command: Target database is not empty. Found 2 tab
les: trace_xe_action_map, trace_xe_event_map.
Critical error has occurred during command execution.

I have tried in 90 compatablity mode same results, this is system tables that this is detecting I am not able to drop those tables.
Any help would be appreciated.

This is a 2012 issue, I was able to restore to 2005 with no issues.

Message was edited by: Kyle Jones

6 comments
Comment actions Permalink

Kyle,

TeamCity version 6.0 you are using, was released two years earlier than SQL Server 2012, so they are not compatible.
We support SQL Server 2012 starting from TeamCity version 7.1.

You need to perform upgrade and migration in two distinct steps:

  1. Upgrade TeamCity from 6.0 to 8.0 in place, connecting it to the same database as before.
    We support direct upgrade between any versions, so you can jump directly from 6.0 to 8.0. The procedure is described at Upgrade documentation page. Also pay attention to Upgrade Notes - there are few configuration changes that require additional handling.
  2. Migrate database to SQL Server 2012
    The procedure is described in Migrating to an External Database article.
0
Comment actions Permalink

Hi Kyle,

you can also migrate without changing/upgrading the current installed software.
In order to get it, you can:

1. Backup data (both configuration files and database tables) using the maintainDB tool on the current installation (see http://confluence.jetbrains.com/display/TCD6/Creating+Backup+via+maintainDB+command-line+tool for details)

2. Make a fresh installation of TC 8.0.x, and create an empty database on the MS SQL 2012 server.

3. Restore data from backup using the maintainDB tool (see http://confluence.jetbrains.com/display/TCD8/Restoring+TeamCity+Data+from+Backup for details).

This way is possible because TC versions 6.0 - 8.x work with the same backup format.

Hope it helps!

--
Leonid

0
Comment actions Permalink

Leonid,

Backup from 6.0.2 and restore into 8.0.2

I am still having issues getting this to work,  I get an error during the restore:
-------------------------------------------------------------------------------------------------------------------------------------
D:\TeamCity\bin>maintainDB.cmd restore -F D:\software\prodbackup\TeamCity_Backup
_20130808_090936 -T D:\software\prodbackup\TeamCity_Backup_20130808_090936\confi
g\database.properties
Java executable is found: 'D:\TeamCity\jre\bin\java.exe'.
Using logs directory D:\TeamCity\bin\..\logs
TeamCity maintenance tool. Copyright 2013 JetBrains s.r.o. All Rights Reserved.

Command line arguments: restore -F D:\software\prodbackup\TeamCity_Backup_201308
08_090936 -T D:\software\prodbackup\TeamCity_Backup_20130808_090936\config\datab
ase.properties
TeamCity Data Directory path is determined using the environment variable TEAMCI
TY_DATA_PATH
Using TeamCity data directory: D:\ProgramData\JetBrains\TeamCity
Restoring from backup file: D:\software\prodbackup\TeamCity_Backup_20130808_0909
36.zip
Backup created by TeamCity version:
        Version: 6.0.2
        Build number: 15857
        Data format version: 418
Using explicitly specified target database settings: D:\software\prodbackup\Team
City_Backup_20130808_090936\config\database.properties
Using target database
        database type: MSSQL
        database driver: <not specified>
        connection: jdbc:sqlserver://DBASQLD02;databaseName=TeamCity;integratedS
ecurity=true
        login as: <empty>
Connected to the Target DB successfully.
Starting restore
Opening zip file: D:\software\prodbackup\TeamCity_Backup_20130808_090936.zip
Using character set UTF-8 for database import.
Restoring Configuration
Restoring Database
Creating tables
Importing data...
        importing table: action_history
Finishing
Exception in thread "RESTORING" java.lang.NoClassDefFoundError: com/microsoft/sq
lserver/jdbc/SQLServerCallableStatement
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareCall(SQLServe
rConnection.java:2177)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareCall(SQLServe
rConnection.java:1861)
        at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData$CallableHandle
s.prepare(SQLServerDatabaseMetaData.java:54)
        at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getCallableSta
tementHandle(SQLServerDatabaseMetaData.java:253)
        at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetFr
omStoredProc(SQLServerDatabaseMetaData.java:281)
        at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getResultSetWi
thProvidedColumnNames(SQLServerDatabaseMetaData.java:304)
        at com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData.getColumns(SQL
ServerDatabaseMetaData.java:623)
        at jetbrains.buildServer.serverSide.maintenance.BackupDatabaseUtil.getTa
bleColumnKinds(BackupDatabaseUtil.java:70)
        at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor$3.run(R
estoreProcessor.java:565)
        at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor$3.run(R
estoreProcessor.java:562)
        at jetbrains.buildServer.serverSide.db.DBFacade.withDB(DBFacade.java:297
)
        at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.process
Table(RestoreProcessor.java:561)
        at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.process
Tables(RestoreProcessor.java:514)
        at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.restore
Database(RestoreProcessor.java:435)
        at jetbrains.buildServer.serverSide.maintenance.RestoreProcessor.runProc
ess(RestoreProcessor.java:115)
        at jetbrains.buildServer.serverSide.maintenance.AbstractMaintenanceProce
ss.run(AbstractMaintenanceProcess.java:152)
        at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.ClassNotFoundException: Class 'com.microsoft.sqlserver.jdbc
.SQLServerCallableStatement' was not found
        at jetbrains.buildServer.plugins.classLoaders.TeamCityClassLoader.doLoad
Class(TeamCityClassLoader.java:80)
        at jetbrains.buildServer.plugins.classLoaders.TeamCityClassLoader.loadCl
ass(TeamCityClassLoader.java:37)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        ... 17 more
Restoring failed
Restoring finished successfully
Critical error has occurred during command execution.
-----------------------------------------------------------------------------------------------------------------------------
teamcity-maintenance.log
------------------------------------------------------------------------------------------------------------------------------
[2013-08-08 09:48:32,536]   INFO - Command line arguments: restore -F D:\software\prodbackup\TeamCity_Backup_20130808_090936 -T D:\software\prodbackup\TeamCity_Backup_20130808_090936\config\database.properties
[2013-08-08 09:48:32,551]   INFO - TeamCity Data Directory path is determined using the environment variable TEAMCITY_DATA_PATH
[2013-08-08 09:48:32,583]   INFO - Using TeamCity data directory: D:\ProgramData\JetBrains\TeamCity
[2013-08-08 09:48:32,583]   INFO - Restoring from backup file: D:\software\prodbackup\TeamCity_Backup_20130808_090936.zip
[2013-08-08 09:48:32,911]   INFO - Backup created by TeamCity version:
 Version: 6.0.2
 Build number: 15857
 Data format version: 418
[2013-08-08 09:48:32,958]   INFO - Using explicitly specified target database settings: D:\software\prodbackup\TeamCity_Backup_20130808_090936\config\database.properties
[2013-08-08 09:48:33,067]   INFO - Using target database
 database type: MSSQL
 database driver: <not specified>
 connection: jdbc:sqlserver://DBASQLD02;databaseName=TeamCity;integratedSecurity=true
 login as: <empty>
[2013-08-08 09:48:33,645]   INFO - Connected to the Target DB successfully.
[2013-08-08 09:48:33,723]   INFO - Starting restore
[2013-08-08 09:48:33,723]   INFO - Opening zip file: D:\software\prodbackup\TeamCity_Backup_20130808_090936.zip
[2013-08-08 09:48:33,927]   INFO - Using character set UTF-8 for database import.
[2013-08-08 09:48:33,942]   INFO - Restoring Configuration
[2013-08-08 09:48:43,333]   INFO - Restoring Database
[2013-08-08 09:48:43,333]   INFO - Creating tables
[2013-08-08 09:48:43,599]   INFO - Importing data...
[2013-08-08 09:48:43,614]   INFO -  importing table: action_history
[2013-08-08 09:48:43,646]   INFO - Finishing
[2013-08-08 09:48:43,724]   INFO - Restoring failed
[2013-08-08 09:48:43,724]   INFO - Restoring finished successfully
----------------------------------------------------------------------------------------------------------------------------------------
teamcity-server.log
----------------------------------------------------------------------------------------------------------------------------------------
[2013-08-08 10:00:36,904]   INFO -  jetbrains.buildServer.STARTUP - ===========================================================
[2013-08-08 10:00:36,904]   INFO -  jetbrains.buildServer.STARTUP - Starting TeamCity server
[2013-08-08 10:00:37,154]   INFO -  jetbrains.buildServer.STARTUP - TeamCity version: 8.0.2 (build 27482), data format version 628
[2013-08-08 10:00:37,154]   INFO -  jetbrains.buildServer.STARTUP - OS: Windows Server 2008 R2, JRE: 1.7.0_21-b11
[2013-08-08 10:00:37,154]   INFO -  jetbrains.buildServer.STARTUP - Servlet real path: D:\TeamCity\bin\..\webapps\ROOT\
[2013-08-08 10:00:37,154]   INFO -  jetbrains.buildServer.STARTUP - Current stage: Looking for the TeamCity Data Directory
[2013-08-08 10:00:37,154]   INFO -  jetbrains.buildServer.STARTUP - TeamCity Data Directory path is determined using the environment variable TEAMCITY_DATA_PATH
[2013-08-08 10:00:37,154]   INFO -  jetbrains.buildServer.STARTUP - Data Directory "D:\ProgramData\JetBrains\TeamCity" exists
[2013-08-08 10:00:37,232]   INFO -  jetbrains.buildServer.STARTUP - The main configuration file "D:\ProgramData\JetBrains\TeamCity\config\main-config.xml" exists
[2013-08-08 10:00:37,232]   INFO -  jetbrains.buildServer.STARTUP - The database properties file "D:\ProgramData\JetBrains\TeamCity\config\database.properties" exists
[2013-08-08 10:00:37,232]   INFO -  jetbrains.buildServer.STARTUP - The internal database data file "D:\ProgramData\JetBrains\TeamCity\system\buildserver.data" doesn't exist
[2013-08-08 10:00:37,310]   INFO -  jetbrains.buildServer.STARTUP - Data Directory version: 418
[2013-08-08 10:00:37,310]   INFO -  jetbrains.buildServer.STARTUP - Current stage: Looking for the database configuration
[2013-08-08 10:00:37,310]   INFO -  jetbrains.buildServer.STARTUP - Database properties file "D:\ProgramData\JetBrains\TeamCity\config\database.properties" exists
[2013-08-08 10:00:37,310]   INFO -  jetbrains.buildServer.STARTUP - Internal HSQL database file (D:\ProgramData\JetBrains\TeamCity\system\buildserver.data) doesn't exist
[2013-08-08 10:00:37,310]   INFO -  jetbrains.buildServer.STARTUP - Database connection URL: jdbc:sqlserver://DBASQLD02;databaseName=TeamCity;integratedSecurity=true
[2013-08-08 10:00:37,310]   INFO -  jetbrains.buildServer.STARTUP - Using database connection URL from the database properties file. The URL is: jdbc:sqlserver://DBASQLD02;databaseName=TeamCity;integratedSecurity=true
[2013-08-08 10:00:37,310]   INFO -  jetbrains.buildServer.STARTUP - Using external (MSSQL) database
[2013-08-08 10:00:37,310]   INFO -  jetbrains.buildServer.STARTUP - Current stage: Connecting to the database
[2013-08-08 10:00:37,873]   INFO -  jetbrains.buildServer.STARTUP - Connected to the database successfully
[2013-08-08 10:00:37,873]   INFO -  jetbrains.buildServer.STARTUP - Current stage: Checking the database
[2013-08-08 10:00:38,060]   INFO -  jetbrains.buildServer.STARTUP - Using schema: dbo
[2013-08-08 10:00:38,076]   INFO -  jetbrains.buildServer.STARTUP - Schema contains no tables.
[2013-08-08 10:00:38,076]  ERROR -  jetbrains.buildServer.STARTUP - Data parts are inconsistent: the Data Directory exists (from another version of TeamCity) but the database does not.
[2013-08-08 10:00:38,076]   INFO -  jetbrains.buildServer.STARTUP - Current stage: TeamCity server startup error
[2013-08-08 10:00:38,076]   INFO -  jetbrains.buildServer.STARTUP - Administrator login is required from web UI using authentication token: 1616803436881616080
[2013-08-08 10:01:58,672]   INFO -  jetbrains.buildServer.STARTUP - Successful authentication token entry from IP address: /172.18.16.38
----------------------------------------------------------------------------------------------------------------------------------------
The database does exist and the tables but  data is not  loaded....

I don't know where to go from here?

0
Comment actions Permalink

Hi Kyle,

could you please take a list of files in the D:\ProgramData\JetBrains\TeamCity\lib\jdbc directory with their sizes?
Did you add any jars into the D:\TeamCity\webapps\ROOT\WEB-INF\lib directory?

0
Comment actions Permalink

This is the issue, I made the updated version of sqljdbc4.jar read only, the restore was laying down an earlyer version and blowing the thing up. I have successfully restored the data. The only error I get now is the access denied when the restore attempts to restore the older version of the jar. Thanks for you help in this matter, consider it closed.

0
Comment actions Permalink

I'm glad to hear that you've managed to upgrade TeamCity. And thank you for the interesting bug, I posted it: http://youtrack.jetbrains.com/issue/TW-31321.

Don't hesitate to contact me on further questions!

--
Leonid

0

Please sign in to leave a comment.