使用publishing转移MS SQL数据(2)[图]_MySQL

    接下来，用写字板打开，搜索数据库所有者都更改为dbo

这样所有的账户都改为dbo，即可。

下一步，把脚本命名为sqlscript.txt， 最好不要叫sqlscript.sql，下面会介绍。

然后通过ftp把脚本放到网站的空间。

编写脚本，例如命名为runsql.aspx ，然后运行该脚本即可还原数据库

    // Sample code for executing a T-SQL file using an ASP.NET page

    // Copyright (C) Microsoft Corporation, 2007.  All rights reserved.

   

    // Written as a sample with use in conjuction with the SQL Server Database Publishing Wizard

    // For more information visit http://www.codeplex.com/sqlhost/

   

    // **************************************************************************

    // Note: Please ensure that you delete this page once your database has been published to the remote server

    // **************************************************************************

     

     %>

 

 

 

    // **************************************************************************

    // Update these variables here

    // **************************************************************************

   

    // Url of the T-SQL file you want to run

    string fileUrl = @"http://www.sohu.com/sqlscript.txt";   

   

    // Connection string to the server you want to execute against

string connectionString = @"Data Source=11.1.1.1;

User ID=hdd;Password=dd;Initial Catalog=s603";

   

    // Timeout of batches (in seconds)

    int timeout = 20000;

 

 

 %>

 

 

   

   

   

   

   

   

   

        SqlConnection conn = null;                  

        try

        {

            this.Response.Write(String.Format("Opening url {0}
", fileUrl));

           

            // read file

            WebRequest request = WebRequest.Create(fileUrl);

            using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))

            {

                this.Response.Write("Connecting to SQL Server database...
");

               

                // Create new connection to database

                conn = new SqlConnection(connectionString);              

               

                conn.Open();

 

                while (!sr.EndOfStream)

                {

                    StringBuilder sb = new StringBuilder();

                    SqlCommand cmd = conn.CreateCommand();

                   

                    while (!sr.EndOfStream)

                    {

                        string s = sr.ReadLine();

                        if (s != null && s.ToUpper().Trim().Equals("GO"))

                        {

                            break;

                        }

                       

                        sb.AppendLine(s);

                    }

 

                    // Execute T-SQL against the target database

                    cmd.CommandText = sb.ToString();

                    cmd.CommandTimeout = timeout;

 

                    cmd.ExecuteNonQuery();

                }

 

            }

            this.Response.Write("T-SQL file executed successfully");

        }

        catch (Exception ex)

        {

            this.Response.Write(String.Format("An error occured: {0}", ex.ToString()));

        }

        finally

        {

            // Close out the connection

            //

            if (conn != null)

            {

                try

                {

                    conn.Close();

                    conn.Dispose();

                }

                catch (Exception e)

                {

                    this.Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e.ToString()));

                }

            }

        }                      

               

       

         %>

 

需要注意

string fileUrl = @“http://www.sohu.com/sqlscript.txt”;

是用户脚本地址，因为很多空间禁止获取sql，所以，改成这样

string fileUrl = @“http://www.sohu.com/sqlscript.sql”;

系统可能无法运行。这样，就完成了数据库转移。