sql如何附加数据库

网站建设1380 更新时间：2025-06-08 04:43:28

2023年6月21日发(作者：)

SQL语句实现附加数据库很简单

--附加数据库

sp_attach_db '数据库名','数据库全路径','数据库日志全路径'

GO

sp_attach_db 'Asset_National','...dataAsset_','...dataAsset_National_'

GO

USE 数据库名

--添加一个登录前指定默认数据库

EXEC sp_addlogin '登录名','密码','数据库名'

GO

--处理空登录名(使登录用户和数据库的孤立用户对应起来，在这个用户有对象时用)

sp_change_users_login 'update_one','登录名','登录名'

GO

--修改数据库的逻辑文件名（数据）

ALTER DATABASE NEW

MODIFY FILE(NAME='Old_Data',NEWNAME='New_Data')

GO

--修改数据库的逻辑文件名（日志）

ALTER DATABASE NEW

MODIFY FILE(NAME='Old_Log',NEWNAME='New_Log')

GO

--------------------------------------------------------------------------------

可能会用到的操作:

--更改当前数据库名称为dbo的登录名为abc

EXEC sp_changedbowner 'abc'

--删除一个登录

EXEC sp_droplogin '登录名'

--赋予这个登录访问数据库的权限

EXEC sp_adduser '登录名','用户名','db_owner'

手动实现方法：[无错版]SQL Server 2000

using System;

using c;

using entModel;

using ;

using g;

using ;

using ent;

namespace System

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

public string ConnectionString;

private SqlConnection Conn;

private SqlCommand Comm;

public string StrSQL;

public string DataBaseName;

public string DataBase_MDF;

public string DataBase_LDF;

public string DataBaseOfBackupName;

public string DataBaseOfBackupPath;

private void Form1_Load(object sender, EventArgs e)

{

C;

DataBaseName = "risheng";

DataBase_MDF = @ "E:risheng_";

DataBase_LDF = @ "E:risheng_";

AddDataBase();

}

///

/// 执行创建/修改数据库和表的操作

///

public void DataBaseAndTableControl()

{

try

{

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = StrSQL;

dType = ;

eNonQuery();

( "数据库操作成功！", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

///

/// 附加数据库

///

public void AddDataBase()

{

try

{

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = "sp_attach_db";

(new SqlParameter(@ "dbname",

ar));

ters[@ "dbname"].Value = DataBaseName;

(new SqlParameter(@ "filename1",

ar));

ters[@ "filename1"].Value = DataBase_MDF;

(new SqlParameter(@ "filename2",

ar));

ters[@ "filename2"].Value = DataBase_LDF;

dType = Procedure;

eNonQuery();

( "附加数据库成功", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

///

/// 分离数据库

///

public void DeleteDataBase()

{

try

{

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = @ "sp_detach_db";

(new SqlParameter(@ "dbname",

ar));

ters[@ "dbname"].Value = DataBaseName;

dType = Procedure;

eNonQuery();

( "分离数据库成功", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

///

/// 备份数据库

///

public void BackupDataBase()

{

try

{

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = "use master;backup database @dbname to disk =

@backupname;";

(new SqlParameter(@ "dbname",

ar));

ters[@ "dbname"].Value = DataBaseName;

(new SqlParameter(@ "backupname",

ar));

ters[@ "backupname"].Value = @DataBaseOfBackupPath +

@DataBaseOfBackupName;

dType = ;

eNonQuery();

( "备份数据库成功", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

///

/// 还原数据库

///

public void ReplaceDataBase()

{

try

{

string BackupFile = @DataBaseOfBackupPath + @DataBaseOfBackupName;

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = "use master;restore database @DataBaseName From

disk = @BackupFile with replace;";

(new SqlParameter(@ "DataBaseName",

ar));

ters[@ "DataBaseName"].Value = DataBaseName;

(new SqlParameter(@ "BackupFile",

ar));

ters[@ "BackupFile"].Value = BackupFile;

dType = ;

eNonQuery();

( "还原数据库成功", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

// 还原数据库

private void button0_Click(object sender, EventArgs e)

{

C;

DataBaseName = "MyDatabase";

DataBaseOfBackupName = @ "";

DataBaseOfBackupPath = @ "D:Program FilesMicrosoft SQL

ServerMSSQLData";

ReplaceDataBase();

}

// 附加数据库

private void button1_Click_1(object sender, EventArgs e)

{

C;

DataBaseName = "MyDatabase";

DataBase_MDF = @ "D:Program FilesMicrosoft SQL

ServerMSSQLDataMyDatabase_";

DataBase_LDF = @ "D:Program FilesMicrosoft SQL

ServerMSSQLDataMyDatabase_";

AddDataBase();

}

// 备份数据库

private void button2_Click(object sender, EventArgs e)

{

C;

DataBaseName = "MyDatabase";

DataBaseOfBackupName = @ "";

DataBaseOfBackupPath = @ "D:Program FilesMicrosoft

ServerMSSQLData";

BackupDataBase();

}

// 分离数据库

private void button3_Click(object sender, EventArgs e)

{

C;

DataBaseName = "MyDatabase";

DeleteDataBase();

}

用sp_attach_db 將你的數據庫綁定上去

-----------------------

use master

EXEC sp_attach_db @dbname = N'pubs',

@filename1 = N'c:Program FilesMicrosoft SQL ',

@filename2 = N'c:Program FilesMicrosoft SQL ServerMSSQLDatapubs_'

SQL

2023年6月21日发(作者：)

SQL语句实现附加数据库很简单

--附加数据库

sp_attach_db '数据库名','数据库全路径','数据库日志全路径'

GO

sp_attach_db 'Asset_National','...dataAsset_','...dataAsset_National_'

GO

USE 数据库名

--添加一个登录前指定默认数据库

EXEC sp_addlogin '登录名','密码','数据库名'

GO

--处理空登录名(使登录用户和数据库的孤立用户对应起来，在这个用户有对象时用)

sp_change_users_login 'update_one','登录名','登录名'

GO

--修改数据库的逻辑文件名（数据）

ALTER DATABASE NEW

MODIFY FILE(NAME='Old_Data',NEWNAME='New_Data')

GO

--修改数据库的逻辑文件名（日志）

ALTER DATABASE NEW

MODIFY FILE(NAME='Old_Log',NEWNAME='New_Log')

GO

--------------------------------------------------------------------------------

可能会用到的操作:

--更改当前数据库名称为dbo的登录名为abc

EXEC sp_changedbowner 'abc'

--删除一个登录

EXEC sp_droplogin '登录名'

--赋予这个登录访问数据库的权限

EXEC sp_adduser '登录名','用户名','db_owner'

手动实现方法：[无错版]SQL Server 2000

using System;

using c;

using entModel;

using ;

using g;

using ;

using ent;

namespace System

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

public string ConnectionString;

private SqlConnection Conn;

private SqlCommand Comm;

public string StrSQL;

public string DataBaseName;

public string DataBase_MDF;

public string DataBase_LDF;

public string DataBaseOfBackupName;

public string DataBaseOfBackupPath;

private void Form1_Load(object sender, EventArgs e)

{

C;

DataBaseName = "risheng";

DataBase_MDF = @ "E:risheng_";

DataBase_LDF = @ "E:risheng_";

AddDataBase();

}

///

/// 执行创建/修改数据库和表的操作

///

public void DataBaseAndTableControl()

{

try

{

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = StrSQL;

dType = ;

eNonQuery();

( "数据库操作成功！", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

///

/// 附加数据库

///

public void AddDataBase()

{

try

{

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = "sp_attach_db";

(new SqlParameter(@ "dbname",

ar));

ters[@ "dbname"].Value = DataBaseName;

(new SqlParameter(@ "filename1",

ar));

ters[@ "filename1"].Value = DataBase_MDF;

(new SqlParameter(@ "filename2",

ar));

ters[@ "filename2"].Value = DataBase_LDF;

dType = Procedure;

eNonQuery();

( "附加数据库成功", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

///

/// 分离数据库

///

public void DeleteDataBase()

{

try

{

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = @ "sp_detach_db";

(new SqlParameter(@ "dbname",

ar));

ters[@ "dbname"].Value = DataBaseName;

dType = Procedure;

eNonQuery();

( "分离数据库成功", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

///

/// 备份数据库

///

public void BackupDataBase()

{

try

{

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = "use master;backup database @dbname to disk =

@backupname;";

(new SqlParameter(@ "dbname",

ar));

ters[@ "dbname"].Value = DataBaseName;

(new SqlParameter(@ "backupname",

ar));

ters[@ "backupname"].Value = @DataBaseOfBackupPath +

@DataBaseOfBackupName;

dType = ;

eNonQuery();

( "备份数据库成功", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

///

/// 还原数据库

///

public void ReplaceDataBase()

{

try

{

string BackupFile = @DataBaseOfBackupPath + @DataBaseOfBackupName;

Conn = new SqlConnection(ConnectionString);

();

Comm = new SqlCommand();

tion = Conn;

dText = "use master;restore database @DataBaseName From

disk = @BackupFile with replace;";

(new SqlParameter(@ "DataBaseName",

ar));

ters[@ "DataBaseName"].Value = DataBaseName;

(new SqlParameter(@ "BackupFile",

ar));

ters[@ "BackupFile"].Value = BackupFile;

dType = ;

eNonQuery();

( "还原数据库成功", "信息提示",

, ation);

}

catch (Exception ex)

{

(e, "信息提示", ,

ation);

}

finally

{

();

}

// 还原数据库

private void button0_Click(object sender, EventArgs e)

{

C;

DataBaseName = "MyDatabase";

DataBaseOfBackupName = @ "";

DataBaseOfBackupPath = @ "D:Program FilesMicrosoft SQL

ServerMSSQLData";

ReplaceDataBase();

}

// 附加数据库

private void button1_Click_1(object sender, EventArgs e)

{

C;

DataBaseName = "MyDatabase";

DataBase_MDF = @ "D:Program FilesMicrosoft SQL

ServerMSSQLDataMyDatabase_";

DataBase_LDF = @ "D:Program FilesMicrosoft SQL

ServerMSSQLDataMyDatabase_";

AddDataBase();

}

// 备份数据库

private void button2_Click(object sender, EventArgs e)

{

C;

DataBaseName = "MyDatabase";

DataBaseOfBackupName = @ "";

DataBaseOfBackupPath = @ "D:Program FilesMicrosoft

ServerMSSQLData";

BackupDataBase();

}

// 分离数据库

private void button3_Click(object sender, EventArgs e)

{

C;

DataBaseName = "MyDatabase";

DeleteDataBase();

}

用sp_attach_db 將你的數據庫綁定上去

-----------------------

use master

EXEC sp_attach_db @dbname = N'pubs',

@filename1 = N'c:Program FilesMicrosoft SQL ',

@filename2 = N'c:Program FilesMicrosoft SQL ServerMSSQLDatapubs_'

SQL

本文发布于:2023-06-21，感谢您对本站的认可！

本文链接:http://torson.com.cn/wangzhan/1687350220a3361.html

sql如何附加数据库

发布评论取消回复

最近发表

相关推荐

标签列表

sql如何附加数据库

发布评论 取消回复

最近发表

相关推荐

标签列表

发布评论取消回复