search
HomeDatabaseMysql Tutorial技巧和诀窍: 如何上传一个.SQL文件到远程主机并且执行它来部署

Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database 【原文发表日期】 Thursday, January 11, 2007 12:20 AM 上个月 (英文),我讨论了新的(免费的)数据库发布向导,它是设计来充分简化在web主机环境中上传和部署 SQ

Tip/Trick: How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database
【原文发表日期】 Thursday, January 11, 2007 12:20 AM

上个月 (英文),我讨论了新的(免费的)数据库发布向导,它是设计来充分简化在web主机环境中上传和部署 SQL Express 和 SQL Server数据库的。

在 第一个关于数据库发布的贴子 (英文)里 ,我示范了你如何可以使用数据库发布向导自动生成 .SQL 安装文件,该文件中将包含重建你的数据库数据定义(表,视图,存储过程,触发器,全文索引目录等等)以及以你原来数据库中同样的数据填充新数据库所需的脚本。这提供了一个极其容易的方法来构造在另外一个服务器上完全复制你的数据库的 .SQL 脚本:

技巧和诀窍: 如何上传一个.SQL文件到远程主机并且执行它来部署

在我以前的帖子里,我提到主机供应商经常提供在线SQL管理工具,你可以用它来打开/运行你新生成的 .SQL 文件,或者会提供SQL查询工具允许你拷贝/粘贴 .SQL 文件的内容到一个查询执行窗口来运行这些SQL语句。但不幸的是,并不是所有的主机供应商支持类似的工具。即使在那些支持类似工具的主机供应商那里,你也会遇上一些情形,譬如你生成的 .SQL 文件是如此之大,把它拷贝/粘贴进一个文本框是行不通的(提交一个 200M 字节内容的表单一般都会造成超时)。

这个帖子示范了把 .SQL 文件部署到远程主机环境中去的另外一个方法,这个方法不要求你的主机供应商在后端为你配置和安装任何东西。

不通过使用管理工具把SQL数据库部署到远程SQL服务器的步骤

下面是把一个本地数据库不通过使用任何管理工具就部署到远程主机上去的步骤:

第一步:生成一个包含数据定义和数据的 .SQL文件

远程部署数据库到主机环境的第一步是生成一个 .SQL 文件,内含你数据库的数据定义和数据。按我的 第一个关于数据库发布的博客帖子 (英文)里的步骤,来了解如何为 SQL Express 或 SQL Server数据库生成一个 .SQL 文件。

第二步: 把 .SQL 文件FTP到你的远程主机上去

生成 .SQL 文件之后,使用FTP或者其他文件传输机制将它上传到你的远程主机上去。最好是把这个文件拷贝到一个远程用户不易访问的受保护的场所。一个建议,给这个文件一个随机的名字,然后将它上传到 /app_data 文件夹,因为在默认情形下,该文件里的内容是受保护的。

用FTP来上传这个文件的好处是,它不会强迫你限制 .SQL 文件的大小。有必要的话,该文件的大小可达几百个M字节之多。

第三步: 下载 RunSQL.aspx 辅助页面

访问这个页面,下载其上连接的 RunSQL.aspx 文件。

RunSQL.aspx文件是个ASP.NET页面,是SQL Server产品组编写的,支持2个参数: 1) .SQL 文件的名字, 以及 2) 数据库的连接字符串。运行RunSQL.aspx页面,它会打开指定的 .SQL 文件,在通过连接字符串指定的数据库中迭代执行文件中的每一句SQL语句。这就会把 .SQL 文件中定义的数据库配备到远程的目标数据库中去。

第四步: 编辑 RunSQL.aspx 辅助页面

在本地打开/编辑RunSQL.aspx文件,设置好你的 .SQL 文件名字,以及提供与你的主机供应商给予你的SQL数据库对应的连接字符串:

技巧和诀窍: 如何上传一个.SQL文件到远程主机并且执行它来部署

把 > 标记以及相关的连接字符串标记替换成你主机环境中的正确配置值。注意,除非你知道你的 .SQL 文件的完整路径,你大概需要使用ASP.NET中的Server.MapPath(fileName)方法来推算出你应用中与 .SQL 文件的相对路径相对应的绝对路径。譬如:

 

    // Filename of the T-SQL file you want to run
    
string fileName Server.MapPath("personal.SQL");    
    
    
// Connection string to the server you want to execute against
    
string connectionString @"Server=server123;User ID=user123;Password=password123;Initial Catalog=MyDBName123";  
    
    
// Timeout of batches (in seconds)
    
int timeout 600;

 

第五步: 把 RunSQL.aspx 辅助页面上传到你的远程主机

完成更改文件名和连接字符串值之后,把RunSQL.aspx文件上传到你的远程主机上去(譬如,使用FTP)。

为安全的原因,我建议你在上传时给该文件一个随机的文件名,这样,别人就不容易找到这个文件并执行它了。

第六步: 用浏览器访问 RunSQL.aspx 辅助页面

上传后,通过浏览器访问远程的RunSQL.aspx网页,这会导致你远程服务器上的网页分析 .SQL 文件,执行其中的所有SQL语句。 因为 .SQL 文件包含了重建数据库所需的数据库数据定义和数据填充的所有语句,网页执行完毕后,你就将一个一模一样的数据库部署到你的远程主机上了:

技巧和诀窍: 如何上传一个.SQL文件到远程主机并且执行它来部署

第七步: 删除 RunSQL.aspx 和 .SQL 文件

运行完你的 .SQL 脚本之后,把RunSQL.aspx网页和 .SQL 文件同时从你的远程主机服务器上删除。

为安全的原因,你想要任何其他人能够远程访问RunSQL.aspx网页,因为它也许会重建你的数据库,导致数据丢失。

第八步: 更新你应用的Web.Config文件来指向主机环境中的数据库

剩下的最后一步是更新你的web.config文件的 部分指向你的远程主机数据库的连接字符串值。之后,你的应用就应该在远程主机上正常工作了。

希望本文对你有所帮助,

Scott

附注:要阅读我写的其他的技巧,诀窍和Recipes帖子的话,请访问这个网页。

 

ASP.NET, .NET, Data, SQL Server, Tips and Tricks

 
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software