Migrate / Restore from backup DB dump to SQL fails Follow

Hello Ranjith,

which version of TeamCity software do you use?
What kind of OS - Unix-like one or Windows?

Could ypu please provide me with the 'teamcity-maintenance.log' files covered this case (both backup and restores actions)? could you please provide me also with the destination database properties file (without password, of course)?

Thank you,

Leonid

Do you solved this issue?

I have such error with migration to MSSQL 2008, will be very appreciate if you  can help with it.

What is the version of your TC installation?

This problem can occur if you're using maintainDB version that is mismatch the version of your database when both versions less than 6.0.

Before TC 6.0, the maintainDB tool could work only with database which version is exactly same as the tool version.

Just updated to Teamcity 6.5.1 (build 17834), previous 6.0.1 behaves same.

System configuration:

Windows 2008 server Web server edition SP2

MSSQL 2008

.BuildServer/version.txt:

Version: 5.1.2

Build number: 13430

Database version: 378

Can’t find “Extended information during migration execution is logged into logs\teamcity-dbmt.log file. Also, logs\teamcity-dbmt-truncation.log contains extended information on possible data truncations during the migration process.” Specified in documentation? But found teamcity-maintenance.log, here is log records related to migration attempt:

[2011-07-08 00:51:56,688]   INFO - Command line arguments: migrate -T F:\Installs\databasen.properties

[2011-07-08 00:51:56,704]   INFO - Using TeamCity data directory: E:\TeamCity\BuildServer

[2011-07-08 00:51:56,731]   INFO - Using internal database.

[2011-07-08 00:51:56,733]   INFO - Using source database

            database type: HSQLDB

            database driver: org.hsqldb.jdbcDriver

            connection: jdbc:hsqldb:file:E:/TeamCity/BuildServer/system/buildserver

            login as: <empty>

[2011-07-08 00:51:57,774]   INFO - Connected to the Source DB successfully.

[2011-07-08 00:51:57,775]   INFO - Using explicitly specified target database settings: F:\Installs\databasen.properties

[2011-07-08 00:51:57,780]   INFO - Using target database

            database type: MSSQL

            database driver: <not specified>

            connection: jdbc:sqlserver://10.1.2.138:1433;databaseName=Teamcity

            login as: TeamCityUser

[2011-07-08 00:51:58,701]   INFO - Connected to the Target DB successfully.

[2011-07-08 00:51:58,832]   INFO - Intermediate backup file: E:\TeamCity\BuildServer\backup\TeamCity_Backup_20110708_005158.zip

[2011-07-08 00:51:58,840]   INFO - Starting backup

[2011-07-08 00:51:58,901]   INFO - Initializing...

[2011-07-08 00:51:58,908]   INFO - Will export all builds.

[2011-07-08 00:51:58,909]   INFO - Listing tables...

[2011-07-08 00:52:15,074]   INFO -             97 tables to backup, estimated 197.07Mb

[2011-07-08 00:52:15,075]   INFO - Listing TeamCity data directory files...

[2011-07-08 00:52:15,097]   INFO -             Config: 514 files to backup, estimated 1.98Mb

[2011-07-08 00:52:15,097]   INFO -             Build logs: 0 files to backup, estimated 0b

[2011-07-08 00:52:15,098]   INFO -             Personal Changes: 0 files to backup, estimated 0b

[2011-07-08 00:52:15,207]   INFO - Exporting Database...

[2011-07-08 00:52:15,207]   INFO - Starting exporting tables...

[2011-07-08 00:52:15,208]   INFO -             exporting table test_names (4 251 rows)

[2011-07-08 00:52:15,514]   INFO -             exporting table build_revisions (12 392 rows)

[2011-07-08 00:52:15,779]   INFO -             exporting table inspection_info (136 rows)

[2011-07-08 00:52:15,787]   INFO -             exporting table vcs_settings_hash (23 rows)

[2011-07-08 00:52:15,843]   INFO -             exporting table usergroups (4 rows)

[2011-07-08 00:52:15,843]   INFO -             exporting table usergroup_notification_events (18 rows)

[2011-07-08 00:52:15,844]   INFO -             exporting table deleted_build_types (13 rows)

[2011-07-08 00:52:15,845]   INFO -             exporting table backup_builds (9 582 rows)

[2011-07-08 00:52:15,908]   INFO -             exporting table user_notification_events (4 rows)

[2011-07-08 00:52:15,910]   INFO -             exporting table archived_projects (1 row)

[2011-07-08 00:52:15,910]   INFO -             exporting table agent_sources_version (17 rows)

[2011-07-08 00:52:15,920]   INFO -             exporting table duplicate_results (2 819 rows)

[2011-07-08 00:52:15,955]   INFO -             exporting table agent_type_vcs (21 rows)

[2011-07-08 00:52:15,956]   INFO -             exporting table paused_build_types (10 rows)

[2011-07-08 00:52:15,957]   INFO -             exporting table inspection_data (34 518 rows)

[2011-07-08 00:52:18,503]   INFO -             exporting table build_type_vcs_change (18 107 rows)

[2011-07-08 00:52:18,633]   INFO -             exporting table responsibilities (1 row)

[2011-07-08 00:52:18,634]   INFO -             exporting table ignored_tests (58 900 rows)

[2011-07-08 00:52:21,441]   INFO -             exporting table user_watch_type (4 rows)

[2011-07-08 00:52:21,442]   INFO -             exporting table stats_publisher_state (12 rows)

[2011-07-08 00:52:21,443]   INFO -             exporting table duplicate_diff (287 rows)

[2011-07-08 00:52:21,445]   INFO -             exporting table personal_build_relative_path (114 980 rows)

[2011-07-08 00:52:31,156]   INFO -             exporting table personal_vcs_history (4 738 rows)

[2011-07-08 00:52:31,263]   INFO -             exporting table remote_run_build_types (6 476 rows)

[2011-07-08 00:52:31,466]   INFO -             exporting table backup_info (11 rows)

[2011-07-08 00:52:31,469]   INFO -             exporting table inspection_results (421 507 rows)

[2011-07-08 00:54:37,762]   INFO -             exporting table server (1 row)

[2011-07-08 00:54:37,763]   INFO -             exporting table agent_type_param (457 rows)

[2011-07-08 00:54:37,774]   INFO -             exporting table build_dependency (5 189 rows)

[2011-07-08 00:54:37,845]   INFO -             exporting table audit_additional_object (133 rows)

[2011-07-08 00:54:37,850]   INFO -             exporting table build_checkout_rules (12 392 rows)

[2011-07-08 00:54:38,045]   INFO -             exporting table users (17 rows)

[2011-07-08 00:54:38,048]   INFO -             exporting table test_info (1 584 564 rows)

[2011-07-08 01:34:27,058]   INFO -             exporting table agent_type_info (4 rows)

[2011-07-08 01:34:27,060]   INFO -             exporting table stats (12 759 rows)

[2011-07-08 01:34:27,194]   INFO -             exporting table build_state (12 839 rows)

[2011-07-08 01:34:27,642]   INFO -             exporting table personal_vcs_changes (86 210 rows)

[2011-07-08 01:34:38,382]   INFO -             exporting table usergroup_roles (1 row)

[2011-07-08 01:34:38,383]   INFO -             exporting table vcs_root_instance (48 rows)

[2011-07-08 01:34:38,385]   INFO -             exporting table build_data_storage (66 126 rows)

[2011-07-08 01:34:41,525]   INFO -             exporting table inspection_stats (5 371 rows)

[2011-07-08 01:34:41,576]   INFO -             exporting table vcs_history (5 267 rows)

[2011-07-08 01:34:41,700]   INFO -             exporting table user_roles (4 rows)

[2011-07-08 01:34:41,701]   INFO -             exporting table agent_type (4 rows)

[2011-07-08 01:34:41,702]   INFO -             exporting table meta_file_line (1 079 rows)

[2011-07-08 01:34:41,711]   INFO -             exporting table duplicate_stats (137 rows)

[2011-07-08 01:34:41,713]   INFO -             exporting table inspection_diff (13 124 rows)

[2011-07-08 01:34:41,831]   INFO -             exporting table canceled_info (71 rows)

[2011-07-08 01:34:41,833]   INFO -             exporting table usergroup_users (30 rows)

[2011-07-08 01:34:41,833]   INFO -             exporting table single_row (1 row)

[2011-07-08 01:34:41,834]   INFO -             exporting table agent (3 rows)

[2011-07-08 01:34:41,834]   INFO -             exporting table user_blocks (168 rows)

[2011-07-08 01:34:41,836]   INFO -             exporting table fragments (299 rows)

[2011-07-08 01:34:41,838]   INFO -             exporting table history (12 798 rows)

[2011-07-08 01:34:42,395]   INFO -             exporting table comments (1 019 rows)

[2011-07-08 01:34:42,415]   INFO -             exporting table action_history (1 019 rows)

[2011-07-08 01:34:42,425]   INFO -             exporting table usergroup_watch_type (18 rows)

[2011-07-08 01:34:42,426]   INFO -             exporting table remember_me (16 rows)

[2011-07-08 01:34:42,427]   INFO -             exporting table compiler_output (3 688 rows)

[2011-07-08 01:34:42,452]   INFO -             exporting table agent_type_runner (73 rows)

[2011-07-08 01:34:42,453]   INFO -             exporting table user_property (226 rows)

[2011-07-08 01:34:42,455]   INFO -             exporting table server_property (2 rows)

[2011-07-08 01:34:42,456]   INFO -             exporting table project_files (36 rows)

[2011-07-08 01:34:42,457]   INFO -             exporting table duplicate_fragments (10 229 rows)

[2011-07-08 01:34:42,552]   INFO -             exporting table build_labels (104 rows)

[2011-07-08 01:34:42,554]   INFO -             exporting table vcs_changes (57 984 rows)

[2011-07-08 01:34:46,150]   INFO -             exporting table db_version (173 rows)

[2011-07-08 01:34:46,166]   INFO -             exporting table build_runtime_info (2 rows)

[2011-07-08 01:34:46,167]   INFO -             exporting table usergroup_subgroups (3 rows)

[2011-07-08 01:34:46,168]   INFO -             exporting table test_failure_rate (406 rows)

[2011-07-08 01:34:46,176]   INFO - Exporting TeamCity data directory: Exporting settings and configurations

[2011-07-08 01:34:46,382]   INFO - Finalizing export...

[2011-07-08 01:34:46,389]   INFO - Export has completed successfully.

[2011-07-08 01:34:46,389]   INFO - Backup file created: E:\TeamCity\BuildServer\backup\TeamCity_Backup_20110708_005158.zip

[2011-07-08 01:34:46,921]   INFO - Backup finished

[2011-07-08 01:34:55,249]   INFO - Starting restore

[2011-07-08 01:34:55,250]   INFO - Opening zip file: E:\TeamCity\BuildServer\backup\TeamCity_Backup_20110708_005158.zip

[2011-07-08 01:34:55,256]   INFO - Using character set UTF-8 for database import.

[2011-07-08 01:34:55,403]   INFO - Restoring Database

[2011-07-08 01:34:55,404]   INFO - Creating tables

[2011-07-08 01:34:55,621]   INFO - Importing data...

[2011-07-08 01:34:55,634]   WARN - The backup file contains tables that are unknown for the TeamCity core system:

            personal_vcs_changes

            usergroup_roles

            vcs_root_instance

            test_names

            build_data_storage

            build_revisions

            inspection_stats

            inspection_info

            usergroups

            vcs_settings_hash

            user_roles

            vcs_history

            usergroup_notification_events

            deleted_build_types

            agent_type

            backup_builds

            duplicate_stats

            meta_file_line

            canceled_info

            inspection_diff

            archived_projects

            user_notification_events

            single_row

            usergroup_users

            agent

            user_blocks

            agent_sources_version

            fragments

            duplicate_results

            agent_type_vcs

            history

            paused_build_types

            comments

            inspection_data

            build_type_vcs_change

            responsibilities

            ignored_tests

            action_history

            user_watch_type

            usergroup_watch_type

            duplicate_diff

            stats_publisher_state

            personal_build_relative_path

            remember_me

            compiler_output

            personal_vcs_history

            remote_run_build_types

            agent_type_runner

            backup_info

            inspection_results

            server_property

            user_property

            server

            duplicate_fragments

            project_files

            agent_type_param

            build_dependency

            build_labels

            audit_additional_object

            vcs_changes

            db_version

            build_runtime_info

            usergroup_subgroups

            build_checkout_rules

            test_failure_rate

            test_info

            users

            agent_type_info

            stats

            build_state

These tables will not be restored. Most probably the tables were used by plugins and the plugin data will be lost.

[2011-07-08 01:34:55,635]   INFO - Creating indices...

[2011-07-08 01:34:55,637]   INFO -             index agent_name_ui

[2011-07-08 01:34:55,642]   INFO -             index agent_host_address

[2011-07-08 01:34:55,644]   INFO -             index agent_authorization_token

[2011-07-08 01:34:55,646]   INFO -             index agent_agent_type_id

[2011-07-08 01:34:55,648]   INFO -             index running_state_id

[2011-07-08 01:34:55,650]   INFO -             index build_state_mod_id

[2011-07-08 01:34:55,653]   INFO -             index build_state_build_type_id

[2011-07-08 01:34:55,656]   INFO -             index build_state_build_id

[2011-07-08 01:34:55,658]   INFO -             index build_artif_dep_state_id

[2011-07-08 01:34:55,661]   INFO -             index final_artif_dep_state_id

[2011-07-08 01:34:55,663]   INFO -             index agent_build_type_access_ui

[2011-07-08 01:34:55,666]   INFO -             index agent_bt_access_agent_id

[2011-07-08 01:34:55,668]   INFO -             index agent_bt_access_bt_id

[2011-07-08 01:34:55,670]   INFO -             index vcs_history_root

[2011-07-08 01:34:55,672]   INFO -             index vcs_history_user

[2011-07-08 01:34:55,674]   INFO -             index build_type_vcs_change_ui

[2011-07-08 01:34:55,677]   INFO -             index build_type_vcs_change_btid

[2011-07-08 01:34:55,679]   INFO -             index build_type_vcs_change_brcid

[2011-07-08 01:34:55,683]   INFO -             index vcs_changes_index

[2011-07-08 01:34:55,685]   INFO -             index vcs_personal_changes_index

[2011-07-08 01:34:55,688]   INFO -             index mute_info_ak

[2011-07-08 01:34:55,694]   INFO -             index mute_test_in_proj_ie

[2011-07-08 01:34:55,698]   INFO -             index mute_test_in_bt_ie

[2011-07-08 01:34:55,701]   INFO -             index test_name_id_idx

[2011-07-08 01:34:55,704]   INFO -             index tit_name_id_idx

[2011-07-08 01:34:55,706]   INFO -             index build_queue_build_state_id

[2011-07-08 01:34:55,708]   INFO -             index remote_data_instance_id

[2011-07-08 01:34:55,710]   INFO -             index remote_data_instance_user_id

[2011-07-08 01:34:55,712]   INFO -             index remote_data_instance_change_id

[2011-07-08 01:34:55,714]   INFO -             index remote_data_instance_bt_id

[2011-07-08 01:34:55,716]   INFO -             index co_build_id_index

[2011-07-08 01:34:55,718]   INFO -             index ignored_tests_build_id

[2011-07-08 01:34:55,720]   INFO -             index downloaded_artifacts_target_id

[2011-07-08 01:34:55,722]   INFO -             index downloaded_artifacts_source_id

[2011-07-08 01:34:55,724]   INFO -             index users_ak

[2011-07-08 01:34:55,726]   INFO -             index user_property_ui

[2011-07-08 01:34:55,730]   INFO -             index user_blocks_ui

[2011-07-08 01:34:55,733]   INFO -             index user_projects_visibility_ui

[2011-07-08 01:34:55,736]   INFO -             index user_projects_order_ui

[2011-07-08 01:34:55,739]   INFO -             index notification_events_notifier

[2011-07-08 01:34:55,741]   INFO -             index notification_events_user_id

[2011-07-08 01:34:55,743]   INFO -             index user_watch_type_ui

[2011-07-08 01:34:55,746]   INFO -             index watch_type_rule_id

[2011-07-08 01:34:55,748]   INFO -             index user_roles_ui

[2011-07-08 01:34:55,751]   INFO -             index usergroups_name

[2011-07-08 01:34:55,754]   INFO -             index usergroup_users_ui

[2011-07-08 01:34:55,757]   INFO -             index usergroup_subgroups_ui

[2011-07-08 01:34:55,760]   INFO -             index usergroup_roles_ui

[2011-07-08 01:34:55,763]   INFO -             index usergroup_events_notifier

[2011-07-08 01:34:55,765]   INFO -             index usergroup_events_group_id

[2011-07-08 01:34:55,767]   INFO -             index usergroup_watch_type_ui

[2011-07-08 01:34:55,769]   INFO -             index group_watch_type_rule_id

[2011-07-08 01:34:55,771]   INFO -             index server_property_ak

[2011-07-08 01:34:55,774]   INFO -             index build_revisions_build

[2011-07-08 01:34:55,776]   INFO -             index build_revisions_vcs_root

[2011-07-08 01:34:55,778]   INFO -             index user_build_params_state_id

[2011-07-08 01:34:55,780]   INFO -             index def_build_params_state_id

[2011-07-08 01:34:55,782]   INFO -             index build_labels_build

[2011-07-08 01:34:55,784]   INFO -             index build_labels_vcs_root

[2011-07-08 01:34:55,786]   INFO -             index personal_build_relative_p_ak

[2011-07-08 01:34:55,790]   INFO -             index action_history_comment

[2011-07-08 01:34:55,792]   INFO -             index action_history_action

[2011-07-08 01:34:55,794]   INFO -             index action_history_object

[2011-07-08 01:34:55,796]   INFO -             index audit_a_o_comment

[2011-07-08 01:34:55,798]   INFO -             index audit_a_o_object_index

[2011-07-08 01:34:55,801]   INFO -             index vcs_root_instance_parent_idx

[2011-07-08 01:34:55,803]   INFO -             index vcs_root_instance_hash_idx

[2011-07-08 01:34:55,805]   INFO -             index agent_type_ak

[2011-07-08 01:34:55,808]   INFO -             index agent_type_runner_id_i

[2011-07-08 01:34:55,810]   INFO -             index agent_type_vcs_id_i

[2011-07-08 01:34:55,812]   INFO -             index responsibilities_reporter

[2011-07-08 01:34:55,814]   INFO -             index responsibilities_assignee

[2011-07-08 01:34:55,816]   INFO -             index inspection_results_hash_index

[2011-07-08 01:34:55,818]   INFO -             index inspection_results_buildhash_i

[2011-07-08 01:34:55,820]   INFO -             index inspection_data_file_index

[2011-07-08 01:34:55,822]   INFO -             index inspection_data_insp_index

[2011-07-08 01:34:55,824]   INFO -             index inspection_info_ak

[2011-07-08 01:34:55,827]   INFO -             index inspection_fixes_hash_index

[2011-07-08 01:34:55,829]   INFO -             index inspection_diff_ak

[2011-07-08 01:34:55,832]   INFO -             index inspection_diff_hash_index

[2011-07-08 01:34:55,834]   INFO -             index duplicate_results_build_id

[2011-07-08 01:34:55,837]   INFO -             index duplicate_fragments_index

[2011-07-08 01:34:55,839]   INFO -             index duplicate_fragments_file_index

[2011-07-08 01:34:55,841]   INFO -             index duplicate_results_file_index

[2011-07-08 01:34:55,843]   INFO -             index fragments_hash_index

[2011-07-08 01:34:55,845]   INFO -             index project_files_file_name_index

[2011-07-08 01:34:55,847]   INFO -             index tc_tags_id_index

[2011-07-08 01:34:55,849]   INFO -             index tc_tags_id_tag

[2011-07-08 01:34:55,851]   INFO -             index start_time_index

[2011-07-08 01:34:55,853]   INFO -             index build_type_id_index

[2011-07-08 01:34:55,855]   INFO -             index pinned_index

[2011-07-08 01:34:55,858]   INFO -             index status_index

[2011-07-08 01:34:55,861]   INFO -             index history_state_id

[2011-07-08 01:34:55,863]   INFO -             index history_agname_bfintimesrv_i

[2011-07-08 01:34:55,867]   INFO -             index history_finish_time_i

[2011-07-08 01:34:55,870]   INFO -             index history_build_number

[2011-07-08 01:34:55,872]   INFO -             index history_agt_b_i

[2011-07-08 01:34:55,875]   INFO -             index start_time_index_light

[2011-07-08 01:34:55,877]   INFO -             index build_type_id_index_light

[2011-07-08 01:34:55,880]   INFO -             index pinned_index_light

[2011-07-08 01:34:55,882]   INFO -             index status_index_light

[2011-07-08 01:34:55,884]   INFO -             index light_history_agname_bftime_i

[2011-07-08 01:34:55,890]   INFO -             index light_history_finish_time_i

[2011-07-08 01:34:55,895]   INFO -             index light_history_agt_b_i

[2011-07-08 01:34:55,898]   INFO -             index backup_info_file_i

[2011-07-08 01:34:55,906]   INFO -             index backup_builds_ak

[2011-07-08 01:34:55,909]   INFO -             index build_subst_root_index

[2011-07-08 01:34:55,912]   INFO - Finishing

[2011-07-08 01:34:56,250]   INFO - Restoring finished with warnings

[2011-07-08 01:34:56,252]   INFO - Changing default database connection in TeamCity configuration:

[2011-07-08 01:34:56,252]   INFO - Copying target database properties file

            from:   F:\Installs\databasen.properties

            to:        E:\TeamCity\BuildServer\config\database.properties

What is the collation of your new database (where you're attempting to import data)?

Thanks,
Leonid

How can I check maintaindb version?

SQL_Latin1_General_CP1_CI_AS

same for tempdb

.BuildServer/version.txt:

Version: 5.1.2

Build number: 13430

Database version: 378

It seems to me you're using migratedDB version 5.1.x for working with database version 6.0.x.

I would like to recommend you to download TeamCity 6.5.2, install it and then try to migrate/update.

Best regards,
Leonid

leofish wrote:

It seems to me you're using migratedDB version 5.1.x for working with database version 6.0.x.

How can it be, if I updated TeamCity to version 6.5.1 yesterday?

But well, I’ll update TeamCity to 6.5.2 and then try to migrate again.

Thank you.

Two additional moments:

1. Don't mix different version installations. Install each new version into an empty directory.
2. When using the 'maintainDB' tool, start it from the 'bin' directory where it located.

Best regards!

I has created backup from 6.5.1, has installed new 6.5.2 version into new folder and tried to restore 6.5.1 backup with maintaindb from new location. I've used database.properties configured to mssql and got the same error.
Any thoughts what's try next?