CN105373631A - SQLSever-based automatic upgrading method for database structure - Google Patents
SQLSever-based automatic upgrading method for database structure Download PDFInfo
- Publication number
- CN105373631A CN105373631A CN201510950276.0A CN201510950276A CN105373631A CN 105373631 A CN105373631 A CN 105373631A CN 201510950276 A CN201510950276 A CN 201510950276A CN 105373631 A CN105373631 A CN 105373631A
- Authority
- CN
- China
- Prior art keywords
- database
- redaction
- legacy version
- script
- sqlsever
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/07—Responding to the occurrence of a fault, e.g. fault tolerance
- G06F11/14—Error detection or correction of the data by redundancy in operation
- G06F11/1402—Saving, restoring, recovering or retrying
- G06F11/1446—Point-in-time backing up or restoration of persistent data
- G06F11/1448—Management of the data involved in backup or backup restore
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/28—Databases characterised by their database models, e.g. relational or object models
- G06F16/284—Relational databases
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Data Mining & Analysis (AREA)
- Quality & Reliability (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses an SQLSever-based automatic upgrading method for a database structure. The method comprises the following steps: generating a new version of database script by an SQLSever database tool; traversing an old version of database table and the new version of database script, carrying out comparison processing on the new version of database script and the old version of database table, and obtaining the processed data; and modifying the old version of database table according to the processed data. According to the SQLSever-based automatic upgrading method for the database structure, database upgrading can be automatically carried out; and the problem that a user cannot operate an sqlsever tool to carry out backup, restoration and upgrading is solved.
Description
Technical field
The present invention relates to database upgrade technical field, particularly relate to a kind of database structure automatically updating method based on SQLSever.
Background technology
Set of system, in its whole life cycle, with client's increment demand and the change realizing logic, usually needs to upgrade.Comprise software upgrading and database upgrade.Database upgrade mainly considers that increment is upgraded, and decrement upgrading is then nonsensical.Mainly comprise table increase, field increase, field type and length change etc.
Summary of the invention
The technical problem to be solved in the present invention is to provide a kind of database structure automatically updating method based on SQLSever, to solve prior art Problems existing.
For solving the problems of the technologies described above, the present invention by the following technical solutions:
Based on the database structure automatically updating method of SQLSever, comprising:
SQLSever data base tool is used to generate the database script of redaction;
Traversal legacy version storehouse table and redaction database script, carry out contrast to the database script of redaction and the database table of legacy version and process, and obtains the data after process, modifies according to the database table of data to legacy version after process.
, the database table of the described database script to redaction and legacy version carries out contrast and processes, and obtains the data after process, modifies, comprising according to the database table of data to legacy version after the process obtained:
The table name of the database script of traversal redaction claims to claim with the table name of legacy data storehouse table, carry out contrast one by one to them to compare, judge whether it is identical title, filter out in the database script of redaction the table name newly increased compared with showing with legacy data storehouse to claim, the table name for newly increasing in the database table of legacy version claims to set up new database table structure.
To the table object with identical table title, judge that whether the field type of each field that each table is relative in the database script of redaction and the database table of legacy version is consistent further successively, inconsistent, use the field type of the database script of redaction to replace the field type of the database table of legacy version, the database table of legacy version is upgraded, does not unanimously then replace.
To the table object with identical table title, whether each field length that each table of database table of the further database script contrast legacy version judging redaction is corresponding has increment, if have increment, the field data of the database script of redaction is used to replace the field data of the database table of legacy version, the database table of legacy version is upgraded, not there is increment and then do not replace.
Beneficial effect of the present invention: the present invention can carry out database upgrade problem automatically, solves user and can not operate the problem that sqlsever instrument carries out backup and reduction and upgrading.
Accompanying drawing explanation
Fig. 1 is process flow diagram of the present invention.
Embodiment
When the invention provides the upgrading of a kind of database structure automatically updating method based on SQLSever as shown in Figure 1, first it use SQLSever data base tool to generate the database script of redaction; Then the database table of legacy version is traveled through, travel through redaction database script simultaneously, carry out contrast to the database script of redaction and the database table of legacy version to process, obtain the data after process, modify according to the database table of data to legacy version after the process obtained, carry out the upgrading of database table.
Specifically, first the database script of redaction and the database of legacy version is traveled through, obtain the database script of redaction and the table number in legacy data storehouse and table name, carry out contrast one by one to the table name of the database script of redaction and the database of legacy version to compare, judge whether it is identical table name, after judging one by one, filter out in the database script of redaction the table name newly increased compared with showing with legacy data storehouse, then in the database table of legacy version, new database table structure is set up to the table name newly increased, wherein identical table title compares field number, increment interpolation is carried out for different field title, the field having new edition not have for old edition is then ignored.Such as say, there are 10 table names in the database of legacy version, and in new database script, there are 13 table names, contrast screening is carried out one by one to them, find out in the database script of redaction 3 table names be not present in legacy version, then be these three the newly-built list structures of newly-increased table in the database of legacy version, namely the database table of legacy version upgraded.
After newly-increased table has increased, to in the database script of redaction with the table object in legacy data storehouse with identical table name, to the database script of redaction and legacy data storehouse all, to arrange with one be a field, judge that whether the field type of each field that each table is corresponding in the database script of redaction and the database table of legacy version is consistent successively, inconsistent, use the field type of the database script of redaction to replace the field type of the database table of legacy version, the database table of legacy version is upgraded, does not unanimously then replace.
When the field type shown in database judges, also need to judge whether the length of identical field corresponding with the table of same names in legacy data storehouse in the database script of redaction has variation, if there is variation, uses the field data of the database script of redaction to replace the field data of the database table of legacy version, not there is variation and then do not change.
In the process of carrying out table name judgement, field type judgement, field length judgement, according to new edition database script, old edition database is modified simultaneously, complete auto-update.
In order to safety and follow-up the checking of data, first the present invention carried out the backup of data before province-level data storehouse, and after upgrading, the reduction of data can also be carried out, DB Backup and reduction realize based on the backup-and-restore API of Sqlserver enterprise manager.Specific as follows:
One, backup database
1). specify backup database, in internal code, perform sql order BACKUPDATABASEDBNameTODISK='C: DBName.bak'WITHFORMAT; Acquiescence backs up completely, i.e. partial data library backup, can back up whole database, comprises all database objects such as subscriber's meter, system table, index, view and storing process.This is the mode that most people is conventional, and security is good, but the Time and place needing cost relatively many.
Two, restoring data storehouse
1). need to provide database backup file, DBName.bak file;
2). according to bak backup file, perform in code sql order RESTOREDATABASEDBNameFROMDISK='C: DBName.bak'WITHREPLACE, MOVEDBNameTO'C: DBName.mdf', MOVEDBName_log'TO'C: DBName.ldf'
Such as:--return the result set be made up of the database comprised in backup set and journal file list
Return the result set be made up of the database comprised in backup set and journal file list
RESTOREFILELISTONLYFROMDISK='c:\DBName.bak'
--the database of also reason BACKUP backup
Also reason RESTOREDATABASEDBNameFROMDISK='c: DBName.bak'
--specify the database physical file name after reduction and path, here must in SQLServer data base management system (DBMS),
First specify the database physical file name after reduction and path, in data base management system (DBMS), specify the database physical file name after reduction and path
Creation database is called the database of Test, database, and specify mdf file and ldf file under C dish test file. under file. otherwise, when reduction, can not find the path of specifying. when reduction, can not find the path of specifying.
RESTOREDATABASETestFROMDISK='c: DBName.bak'WITHMOVE'DBName'TO'c: test testdb.mdf', MOVE'DBName_log'TO'c: test testdb.ldf'MOVE'logical_file_name'TO'operating_system_fi le_name' specify given logical_file_name should be moved on to operating_system_file_name.
Under default situations, if logical_file_name will revert to its original position. use RESTORE statement by database replication on identical or different server, then may need to use MOVE option to reorientate database file to avoid and existing file conflict. can each logical file in different MOVE statements in specified database.
--force reduction, add REPLACE parameter, then on existing database basis, force reduction. force reduction, parameter, then on existing database basis, force reduction.
Force reduction
RESTOREDATABASETestFROMDISK='c:\DBName.bak'WITHREPLACE,MOVE'DBName'TO'c:\test\testdb.mdf',MOVE'DBName_log'TO'c:\test\testdb.ldf'。
Claims (4)
1., based on the database structure automatically updating method of SQLSever, it is characterized in that, comprising:
SQLSever data base tool is used to generate the database script of redaction;
Traversal legacy version storehouse table and redaction database script, carry out contrast to the database script of redaction and the database table of legacy version and process, and obtains the data after process, modifies according to the database table of data to legacy version after process.
2. the database structure automatically updating method based on SQLSever according to claim 1, it is characterized in that, the database table of the described database script to redaction and legacy version carries out contrast and processes, obtain the data after process, modify according to the database table of data to legacy version after the process obtained, comprising:
The table name of the database script of traversal redaction claims to claim with the table name of legacy data storehouse table, carry out contrast one by one to them to compare, judge whether it is identical title, filter out in the database script of redaction the table name newly increased compared with showing with legacy data storehouse to claim, the table name for newly increasing in the database table of legacy version claims to set up new database table structure.
3. the database structure automatically updating method based on SQLSever according to claim 2, it is characterized in that: to the table object with identical table title, judge that whether the field type of each field that each table is relative in the database script of redaction and the database table of legacy version is consistent further successively, inconsistent, use the field type of the database script of redaction to replace the field type of the database table of legacy version, the database table of legacy version is upgraded, does not unanimously then replace.
4. the database structure automatically updating method based on SQLSever according to claim 2, it is characterized in that: to the table object with identical table title, whether each field length that each table of database table of the further database script contrast legacy version judging redaction is corresponding has increment, if have increment, the field data of the database script of redaction is used to replace the field data of the database table of legacy version, the database table of legacy version is upgraded, not there is increment and then do not replace.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510950276.0A CN105373631A (en) | 2015-12-18 | 2015-12-18 | SQLSever-based automatic upgrading method for database structure |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510950276.0A CN105373631A (en) | 2015-12-18 | 2015-12-18 | SQLSever-based automatic upgrading method for database structure |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN105373631A true CN105373631A (en) | 2016-03-02 |
Family
ID=55375829
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201510950276.0A Pending CN105373631A (en) | 2015-12-18 | 2015-12-18 | SQLSever-based automatic upgrading method for database structure |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN105373631A (en) |
Cited By (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106599167A (en) * | 2016-12-09 | 2017-04-26 | 郑州云海信息技术有限公司 | System and method capable of supporting incremental upgrading of database |
| CN108614868A (en) * | 2018-04-16 | 2018-10-02 | 北京酷我科技有限公司 | A kind of database automatically updating method |
| CN109597802A (en) * | 2018-12-07 | 2019-04-09 | 江苏满运软件科技有限公司 | Database assertion data generation method, system, equipment and medium |
| CN110908840A (en) * | 2019-12-02 | 2020-03-24 | 杭州安恒信息技术股份有限公司 | Method, device, equipment and storage medium for restoring bak backup files |
| CN113626049A (en) * | 2021-06-28 | 2021-11-09 | 上海市精神卫生中心（上海市心理咨询培训中心） | Program upgrading method and device suitable for hospital local area network environment |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20060085465A1 (en) * | 2004-10-15 | 2006-04-20 | Oracle International Corporation | Method(s) for updating database object metadata |
| CN102096671A (en) * | 2009-12-14 | 2011-06-15 | 深圳速浪数字技术有限公司 | Method and equipment for generating business and operation support system (BOSS) database upgrading script |
| CN102402559A (en) * | 2010-09-16 | 2012-04-04 | 中兴通讯股份有限公司 | Database upgrade script generating method and device |
| CN103810257A (en) * | 2014-01-24 | 2014-05-21 | 华为技术有限公司 | Method and device for upgrading software database and equipment |
-
2015
- 2015-12-18 CN CN201510950276.0A patent/CN105373631A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20060085465A1 (en) * | 2004-10-15 | 2006-04-20 | Oracle International Corporation | Method(s) for updating database object metadata |
| CN102096671A (en) * | 2009-12-14 | 2011-06-15 | 深圳速浪数字技术有限公司 | Method and equipment for generating business and operation support system (BOSS) database upgrading script |
| CN102402559A (en) * | 2010-09-16 | 2012-04-04 | 中兴通讯股份有限公司 | Database upgrade script generating method and device |
| CN103810257A (en) * | 2014-01-24 | 2014-05-21 | 华为技术有限公司 | Method and device for upgrading software database and equipment |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106599167A (en) * | 2016-12-09 | 2017-04-26 | 郑州云海信息技术有限公司 | System and method capable of supporting incremental upgrading of database |
| CN108614868A (en) * | 2018-04-16 | 2018-10-02 | 北京酷我科技有限公司 | A kind of database automatically updating method |
| CN109597802A (en) * | 2018-12-07 | 2019-04-09 | 江苏满运软件科技有限公司 | Database assertion data generation method, system, equipment and medium |
| CN109597802B (en) * | 2018-12-07 | 2020-12-01 | 江苏满运软件科技有限公司 | Database assertion data generation method, system, device, and medium |
| CN110908840A (en) * | 2019-12-02 | 2020-03-24 | 杭州安恒信息技术股份有限公司 | Method, device, equipment and storage medium for restoring bak backup files |
| CN113626049A (en) * | 2021-06-28 | 2021-11-09 | 上海市精神卫生中心（上海市心理咨询培训中心） | Program upgrading method and device suitable for hospital local area network environment |
| CN113626049B (en) * | 2021-06-28 | 2024-05-28 | 上海市精神卫生中心（上海市心理咨询培训中心） | Program upgrading method and device suitable for hospital LAN environment |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US8782604B2 (en) | Sandbox support for metadata in running applications | |
| US10353913B2 (en) | Automating extract, transform, and load job testing | |
| US7933869B2 (en) | Method and system for cloning a tenant database in a multi-tenant system | |
| US10437795B2 (en) | Upgrading systems with changing constraints | |
| US7917607B2 (en) | Software management systems and methods, including use of such systems and methods in a provider-tenant environment | |
| US7689593B2 (en) | Systems and methods for accessing a shared space in a provider-tenant environment | |
| US7698284B2 (en) | Systems and methods for deploying a tenant in a provider-tenant environment | |
| US7680825B2 (en) | Systems and methods for generating tenant-specific properties for use in a provider-tenant environment | |
| US7739348B2 (en) | Systems and methods for accessing a shared space in a provider-tenant environment by using middleware | |
| CN105373631A (en) | SQLSever-based automatic upgrading method for database structure | |
| US20070156849A1 (en) | Systems and methods for delivering software upgrades in a provider-tenant environment | |
| US20080162509A1 (en) | Methods for updating a tenant space in a mega-tenancy environment | |
| EP3049968B1 (en) | Master schema shared across multiple tenants with dynamic update | |
| US9971595B1 (en) | Techniques for automated database deployment | |
| US20080162622A1 (en) | Systems and methods to implement extensibility of tenant content in a provider-tenant environment | |
| US9892122B2 (en) | Method and apparatus for determining a range of files to be migrated | |
| US20070156902A1 (en) | Systems and methods for implementing a tenant space in a provider-tenant environment | |
| US20070156901A1 (en) | Generation and use of table links in a provider-tenant environment | |
| US20070156699A1 (en) | Systems and methods for generating tenant templates for use in a provider-tenant environment | |
| US20070162451A1 (en) | Systems and methods for linking a tenant to a provider | |
| US20070156714A1 (en) | Systems and methods for analyzing tenant-specific properties for use in a provider-tenant environment | |
| US20080162536A1 (en) | Systems and methods for extending shared data structures with tenant content in a provider-tenant environment | |
| US20080162490A1 (en) | Methods and systems for automatic registration during deployment of a tenant | |
| US20200050593A1 (en) | Automatically setting an auto-purge value to multiple tables within a database | |
| CN104298761A (en) | Implementation method for master data matching between heterogeneous software systems |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20160302 |