sql如何附加數(shù)據(jù)庫(kù)

1、SQL 語(yǔ)句實(shí)現(xiàn)附加數(shù)據(jù)庫(kù)很簡(jiǎn)單-附加數(shù)據(jù)庫(kù)sp_attach_db '數(shù)據(jù)庫(kù)名 ','數(shù)據(jù)庫(kù)全路徑 ','數(shù)據(jù)庫(kù)日志全路徑 GO sp_attach_db 'Asset_National','.dataAsset_National.mdf','.dataAsset_National_log.LDF' GOUSE 數(shù)據(jù)庫(kù)名-添加一個(gè)登錄前指定默認(rèn)數(shù)據(jù)庫(kù)EXEC sp_addlogin '登錄名 ','密碼','數(shù)據(jù)庫(kù)名 'GO-處理空登錄名 （使登錄用戶和數(shù)據(jù)

2、庫(kù)的孤立用戶對(duì)應(yīng)起來(lái)，在這個(gè)用戶有對(duì)象時(shí)用）sp_change_users_login 'update_one',登錄名','登錄名'GO-修改數(shù)據(jù)庫(kù)的邏輯文件名（數(shù)據(jù)）ALTER DATABASE NEWMODIFY FILE（NAME='Old_Data',NEWNAME='New_Data'）GO-修改數(shù)據(jù)庫(kù)的邏輯文件名（日志）ALTER DATABASE NEWMODIFY FILE（NAME='Old_Log',NEWNAME='New_Log'）GO可能會(huì)用到的操作 :-更改當(dāng)前

3、數(shù)據(jù)庫(kù)名稱為 dbo 的登錄名為 abcEXEC sp_changedbowner 'abc'-刪除一個(gè)登錄EXEC sp_droplogin ' 登錄名 '-賦予這個(gè)登錄訪問(wèn)數(shù)據(jù)庫(kù)的權(quán)限EXEC sp_adduser '登錄名 ','用戶名 ','db_owner' 手動(dòng)實(shí)現(xiàn)方法： 無(wú)錯(cuò)版 SQL Server 2000 using System;using System.Collections.Generic; using System.ComponentModel;using System.Data;usin

4、g System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.SqlClient;namespace Systempublic partial class Form1 : Formpublic Form1()InitializeComponent();public string ConnectionString;private SqlConnection Conn;private SqlCommand Comm;public string StrSQL;public string DataBase

5、Name;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_Data.MDF"DataBase_LDF = "E:risheng_Log.LDF

6、"AddDataBase();/ <summary>/ 執(zhí)行創(chuàng)建 /修改數(shù)據(jù)庫(kù)和表的操作/ </summary>public void DataBaseAndTableControl()tryConn = new SqlConnection(ConnectionString);Conn.Open();Comm = new SqlCommand();Comm.Connection = Conn;Comm.CommandText = StrSQL;Comm.CommandType = CommandType.Text;Comm.ExecuteNonQuery();

7、MessageBox.Show( " 數(shù) 據(jù) 庫(kù) 操 作 成 功 ！ ", " 信 息 提 示 " MessageBoxButtons.OK, MessageBoxIcon.Information);catch (Exception ex)MessageBox.Show(ex.Message, " 信 息 提 示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);finallyConn.Close();/ <summary>/ 附加數(shù)據(jù)庫(kù)/ </summary&

8、gt;public void AddDataBase() tryConn = new SqlConnection(ConnectionString);Conn.Open();Comm = new SqlCommand();Comm.Connection = Conn;Comm.CommandText = "sp_attach_db""dbname","filename1","filename2",息 提 示 ",Comm.Parameters.Add(new SqlParameter(SqlDbType.

9、NVarChar);Comm.Parameters "dbname".Value = DataBaseName;Comm.Parameters.Add(new SqlParameter(SqlDbType.NVarChar);Comm.Parameters "filename1".Value = DataBase_MDF;Comm.Parameters.Add(new SqlParameter(SqlDbType.NVarChar);Comm.Parameters "filename2".Value = DataBase_LDF;Co

10、mm.CommandType = CommandType.StoredProcedure;Comm.ExecuteNonQuery();MessageBox.Show( " 附 加 數(shù) 據(jù) 庫(kù) 成 功 ", " 信MessageBoxButtons.OK, MessageBoxIcon.Information);catch (Exception ex)MessageBox.Show(ex.Message, " 信 息 提 示 ", MessageBoxButtons.OK,MessageBoxIcon.Information);finallyC

11、onn.Close();/ <summary>/ 分離數(shù)據(jù)庫(kù)/ </summary>public void DeleteDataBase()tryConn = new SqlConnection(ConnectionString);Conn.Open();Comm = new SqlCommand();Comm.Connection = Conn;Comm.CommandText = "sp_detach_db"Comm.Parameters.Add(new SqlParameter( "dbname",SqlDbType.NVa

12、rChar);Comm.Parameters "dbname".Value = DataBaseName;Comm.CommandType = CommandType.StoredProcedure;Comm.ExecuteNonQuery();MessageBox.Show( " 分 離 數(shù) 據(jù) 庫(kù) 成 功 ", " 信 息 提 示 ",MessageBoxButtons.OK, MessageBoxIcon.Information);catch (Exception ex)MessageBox.Show(ex.Message, &

13、quot; 信 息 提 示 ", MessageBoxButtons.OK,MessageBoxIcon.Information);finallyConn.Close();/ <summary>/ 備份數(shù)據(jù)庫(kù)/ </summary>public void BackupDataBase()tryConn = new SqlConnection(ConnectionString);Conn.Open();Comm = new SqlCommand();Comm.Connection = Conn;Comm.CommandText = "use maste

14、r;backup database dbname to disk = backupname;"Comm.Parameters.Add(new SqlParameter( "dbname", SqlDbType.NVarChar);Comm.Parameters "dbname".Value = DataBaseName;Comm.Parameters.Add(new SqlParameter( "backupname", SqlDbType.NVarChar);Comm.Parameters "backupname

15、".Value = DataBaseOfBackupPath + DataBaseOfBackupName;Comm.CommandType = CommandType.Text;Comm.ExecuteNonQuery();MessageBox.Show( " 備 份 數(shù) 據(jù) 庫(kù) 成 功 ", " 信 息 提 示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);catch (Exception ex)MessageBox.Show(ex.Message, " 信 息 提 示

16、", MessageBoxButtons.OK, MessageBoxIcon.Information);finallyConn.Close();/ <summary>/ 還原數(shù)據(jù)庫(kù)/ </summary>public void ReplaceDataBase()trystring BackupFile = DataBaseOfBackupPath + DataBaseOfBackupName;Conn = new SqlConnection(ConnectionString);Conn.Open();Comm = new SqlCommand();Comm.

17、Connection = Conn;Comm.CommandText = "use master;restore database DataBaseName From disk = BackupFile with replace;"Comm.Parameters.Add(new SqlParameter( "DataBaseName", SqlDbType.NVarChar);Comm.Parameters "DataBaseName".Value = DataBaseName;Comm.Parameters.Add(new SqlP

18、arameter( "BackupFile", SqlDbType.NVarChar);Comm.Parameters "BackupFile".Value = BackupFile;Comm.CommandType = CommandType.Text;Comm.ExecuteNonQuery();MessageBox.Show( " 還 原 數(shù) 據(jù) 庫(kù) 成 功 ", " 信 息 提 示 "MessageBoxButtons.OK, MessageBoxIcon.Information);catch (Excep

19、tion ex)MessageBox.Show(ex.Message, " 信 息 提 示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);finallyConn.Close();/ 還原數(shù)據(jù)庫(kù)private void button0_Click(object sender, EventArgs e)C;DataBaseName = "MyDatabase"DataBaseOfBackupName = "back.bak"FilesMicrosoft SQLFilesMicrosoftSQLFilesMicrosoftSQLDataBaseOfBackupPath = "D:ProgramServerMSSQLData"ReplaceDataBase();/ 附加數(shù)據(jù)庫(kù) private void button1_Click_1(object sender, EventArgs e) C;DataBaseName = "MyDatabase" DataBase_MDF="D:ProgramServerMSSQLDataMyDatabase_Data.MDF"DataBas