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

这样所有的账户都改为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

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

%>

<%@ Page Language="C#" AutoEventWireup="true" %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="System.Net" %>

<%

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

// 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”;

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