《sql语句实现附加数据库很简单（The SQL statement to achieve additional database is very simple）.doc》由会员分享，可在线阅读，更多相关《sql语句实现附加数据库很简单（The SQL statement to achieve additional database is very simple）.doc（19页珍藏版）》请在三一文库上搜索。
1、sql语句实现附加数据库很简单（The SQL statement to achieve additional database is very simple）First, the SQL statement implements the additional database simplyBecause I often manage the server, and often to the customer additional database or server problems, have to operate on the database,In the past one or tw
2、o, no matter what the problem is solved,Now the SQL Server 2000 database is cheap, and a bunch of people want the SQL database,As a result, if the server is to adjust or upgrade, there will be a variety of problems, and feel troublesome, especially when adding databases,And when there is the followi
3、ng article, the code is very short, but you can achieve many graphical operations, very difficult to achieve the operation, and SQL statements to achieve additional database operations, very convenient,In your use process, if press the following method can not, can leave a message for me, I will sol
4、ve as soon as possible- additional databasesSp_attach_db database name, database full path, database log full path GOUSE database name- add a login to specify the default database before logging inEXEC sp_addlogin login name, password, database nameGO- - handles the empty login name (so that the log
5、in user corresponds to the isolated user of the database, which is used when the user has an object)Sp_change_users_loginupdate_one, login name , login name GO- modify the logical file name (data) of the databaseALTER DATABASE NEWMODIFY FILE (NAME=Old_Data, NEWNAME=New_Data)GO- modify the logical fi
6、le name (log) of the databaseALTER DATABASE NEWMODIFY FILE (NAME=Old_Log, NEWNAME=New_Log)GO-Operations that may be used:- change the login name of the current database name dbo to ABCEXEC sp_changedbownerabc- delete a loginEXEC sp_droplogin login name- give this login access to the databaseEXEC sp_
7、adduser login name, user name,db_ownerManual implementation: error free version ofSQL Server 2000Using System;Using System.Collections.Generic;Using System.ComponentModel;Using System.Data;Using System.Drawing;Using System.Text;Using System.Windows.Forms;Using System.Data.SqlClient;Namespace SystemP
8、ublic, partial, class, Form1: FormPublic, Form1 ()InitializeComponent ();Public string ConnectionString;Private SqlConnection Conn;Private SqlCommand Comm;Public string StrSQL;Public string DataBaseName;Public string DataBase_MDF;database_ldf字符串；公共字符串DataBaseOfBackupName；公共字符串DataBaseOfBackupPath；pr
9、ivate void form1_load（object sender，EventArgs e）C；数据库名称=“日升”；database_mdf = “E： risheng_data .mdf”；database_ldf = “E： risheng_log .ldf”；adddatabase()； / / / /修改数据库和表的操作执行创建/公共无效databaseandtablecontrol()尝试conn =新的SqlConnection（类型）；康涅狄格州open()；公司为新sqlcommand()；通信连接= Conn；mandtext =如何；mandtype =文本命令；通信
10、executenonquery()；MessageBox显示（“数据库操作成功！”、“信息提示”，messageboxbuttons.ok，MessageBoxIcon。信息）；catch（异常前）MessageBox显示（ex.message，“信息提示”，messageboxbuttons.ok，MessageBoxIcon。信息）；最后康涅狄格州（）； / / /附加数据库/公共无效adddatabase()尝试conn =新的SqlConnection（类型）；康涅狄格州open()；公司为新sqlcommand()；通信连接= Conn；mandtext =“sp_attach_db
11、”；comm.parameters。添加（新SqlParameter（“北京”、SqlDbType。nvarchar）；comm.parameters ”在“价值=数据库；comm.parameters。添加（新SqlParameter（”filename1”、SqlDbType。nvarchar）；comm.parameters ”filename1”。价值= database_mdf；comm.parameters。添加（新SqlParameter（”filename2”、SqlDbType。nvarchar）；comm.parameters ”filename2价值= database_
12、ldf”；mandtype = commandtype.storedprocedure；通信executenonquery()；MessageBox显示（“附加数据库成功”、“信息提示”，messageboxbuttons.ok，MessageBoxIcon。信息）；catch（异常前）MessageBox显示（ex.message，“信息提示”，messageboxbuttons.ok，MessageBoxIcon。信息）；最后康涅狄格州（）； / / /分离数据库/公共无效deletedatabase()尝试conn =新的SqlConnection（类型）；康涅狄格州open()；公司为
13、新sqlcommand()；通信连接= Conn；mandtext =“”sp_detach_db”；comm.parameters。添加（新SqlParameter（“北京”、SqlDbType。nvarchar）；comm.parameters ”在“价值=数据库；mandtype = commandtype.storedprocedure；通信executenonquery()；MessageBox显示（“分离数据库成功”、“信息提示”，messageboxbuttons.ok，MessageBoxIcon。信息）；catch（异常前）MessageBox显示（ex.message，“信
14、息提示”，messageboxbuttons.ok，MessageBoxIcon。信息）；最后康涅狄格州（）； / / /备份数据库/公共无效backupdatabase()尝试conn =新的SqlConnection（类型）；康涅狄格州open ();comm = new sqlcommand ();comm. connection = conn;mandtext = use master; backup database dbname to disk = backupname;comm.parameters.add (new sqlparameter ( dbname , sqldbty
15、pe.nvarchar);comm.parameters dbname .value = databasename;comm.parameters.add (new sqlparameter ( backupname, sqldbtype.nvarchar);comm.parameters backupname.value = databaseofbackuppath + databaseofbackupname;mandtype = commandtype. text;comm.executenonquery ();messagebox.show (备份数据库成功, 信息提示, messag
16、eboxbuttons.ok, messageboxicon.information);catch (exception ex)messagebox.show (ex.message, 信息提示, messageboxbuttons.ok, messageboxicon.information);finallyconn.close ();/ / / / / / 还原数据库/ / / - / summarypublic void replacedatabase ()trystring backupfile = databaseofbackuppath + databaseofbackupname
17、;conn = new sqlconnection (connectionstring);conn.open ();comm = new sqlcommand ();comm. connection = conn;mandtext = use master restore database; databasename from disk = backupfile with replace;comm.parameters.add (new sqlparameter ( databasename, sqldbtype.nvarchar);comm.parameters databasename.v
18、alue = databasename;comm.parameters.add (new sqlparameter ( backupfile, sqldbtype.nvarchar);comm.parameters backupfile.value = backupfile;mandtype = commandtype. text;comm.executenonquery ();messagebox.show (还原数据库成功, 信息提示, messageboxbuttons.ok, messageboxicon.information);catch (exception ex)message
19、box.show (ex.message, 信息提示, messageboxbuttons.ok, messageboxicon.information);finallyconn.close ();/ / 还原数据库private void button0 _ click (object sender, eventargs (e)c;databasename = mydatabase;databaseofbackupname = back.bak;databaseofbackuppath = d: program files microsoft sql server mssql data ;r
20、eplacedatabase ();/ / 附加数据库private void button1 _ click (object sender, _ 1 eventargs (e)c;databasename = mydatabase;database _ mdf = d: program files microsoft sql server mssql data mydatabase _ data.mdf;database _ ldf = d: program files microsoft sql server mssql data mydatabase _ log.ldf;adddatab
21、ase ();/ / 备份数据库private void button2 _ click (object sender, eventargs (e)c;databasename = mydatabase;databaseofbackupname = back.bak;databaseofbackuppath = d: program files microsoft sql server mssql data ;backupdatabase ();/ / 分离数据库private void button3 _ click (object sender, eventargs (e)c;databasename = mydatabase;deletedatabase ();用sp _ attach _ db 將你的數據庫綁定上去mm-hmm.use masterexec sp _ attach _ db dbname = n pubsthe filename1 = n c: program files microsoft sql server mssql data pubs.mdf“filename2 = nc：程序文件微软SQL服务器MSSQL dataspxxglxt pubs_log LDF”。