数据库还原前，杀死所有需还原数据库的所有链接。请问该如何操作？
protected void newslistGrid_RowCommand1(object sender, GridViewCommandEventArgs e)
{
string chaozuo = e.CommandName.ToString();
int id = Convert.ToInt32(e.CommandArgument.ToString());
int flag = 0;
if (chaozuo == "sc")
{
if (dDB.deleteData(id))
{
BindDatas();
}
else
{ }
}
else
{
DataVO dataVO = new DataVO(id);
string path = @Server.MapPath("/datas/") + dataVO.DatasPath;
string cmdText = "use master;restore database school from disk='" + path + "';";
SqlCommand sqlCommand = new SqlCommand(cmdText, connDB.sqlConn);
connDB.sqlConn.Open();
flag = sqlCommand.ExecuteNonQuery();
connDB.sqlConn.Close();
}
}
请问我这段代码该如何更改？ --------------------编程问答-------------------- 关闭某个数据库当前的所有连接
USE master
--EXEC sp_who
--kill 68
SELECT spid,hostname,loginame FROM sysprocesses WHERE DBID=DB_ID('R_080903_080903_MR_Svr_Nbr')
declare @s varchar(1000)
select @s=isnull(@s,'')+' Kill ' +rtrim(spID) from master..sysprocesses where dbid=db_id('test')
select @s
或者
USE MASTER
DECLARE @i INT
SELECT @i=1
DECLARE @sSPID VARCHAR(100)
DECLARE KILL_CUR SCROLL CURSOR FOR
SELECT SPID FROM sysprocesses WHERE DBID=DB_ID('你要KILL连接的数据库名字')
OPEN KILL_CUR
IF @@CURSOR_ROWS=0 GOTO END_KILL_CUR
FETCH FIRST FROM KILL_CUR INTO @sSPID
EXEC('KILL '+@sSPID)
WHILE @i<@@CURSOR_ROWS
BEGIN
FETCH NEXT FROM KILL_CUR INTO @sSPID
EXEC('KILL '+@sSPID)
SELECT @i=@i+1
END
END_KILL_CUR:
CLOSE KILL_CUR
DEALLOCATE KILL_CUR
或者
use master
go
declare @spid varchar(20),@dbname varchar(20)
declare #spid cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id('tt2')
open #spid
fetch next from #spid into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #spid into @spid
end
close #spid
deallocate #spid
设置数据库的用户模式
exec sp_dboption 'tt2','single user', 'true'
或者
ALTER DATABASE tt2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
分离数据库
EXEC sp_detach_db 'tt2', 'true'
创建数据库
A. Create a database that specifies the data and transaction log files
This example creates a database called Sales. Because the keyword PRIMARY is not used, the first file (Sales_dat) becomes the primary file. Because neither MB or KB is specified in the SIZE parameter for the Sales_dat file, it defaults to MB and is allocated in megabytes. The Sales_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.
USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
B. Create a database specifying multiple data and transaction log files
This example creates a database called Archive with three 100-MB data files and two 100-MB transaction log files. The primary file is the first file in the list and is explicitly specified with the PRIMARY keyword. The transaction log files are specified following the LOG ON keywords. Note the extensions used for the files in the FILENAME option: .mdf is used for primary data files, .ndf is used for the secondary data files, and .ldf is used for transaction log files.
USE master
GO
CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Arch3,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat3.ndf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog1.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Archlog2,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog2.ldf',
SIZE = 100MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
C. Create a simple database
This example creates a database called Products and specifies a single file. The file specified becomes the primary file, and a 1-MB transaction log file is automatically created. Because neither MB or KB is specified in the SIZE parameter for the primary file, the primary file is allocated in megabytes. Because there is no <filespec> for the transaction log file, the transaction log file has no MAXSIZE and can grow to fill all available disk space.
USE master
GO
CREATE DATABASE Products
ON
( NAME = prods_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\prods.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1 )
GO
D. Create a database without specifying files
This example creates a database named mytest and creates a corresponding primary and transaction log file. Because the statement has no <filespec> items, the primary database file is the size of the model database primary file. The transaction log is the size of the model database transaction log file. Because MAXSIZE is not specified, the files can grow to fill all available disk space.
CREATE DATABASE mytest
E. Create a database without specifying SIZE
This example creates a database named products2. The file prods2_dat becomes the primary file with a size equal to the size of the primary file in the model database. The transaction log file is created automatically and is 25 percent of the size of the primary file, or 512 KB, whichever is larger. Because MAXSIZE is not specified, the files can grow to fill all available disk space.
USE master
GO
CREATE DATABASE Products2
ON
( NAME = prods2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\prods2.mdf' )
GO
F. Create a database with filegroups
This example creates a database named sales with three filegroups:
The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increments for these files is specified as 15 percent.
A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2.
A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.
CREATE DATABASE Sales
ON PRIMARY
( NAME = SPri1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri1dat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
( NAME = SPri2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SPri2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 15% ),
FILEGROUP SalesGroup1
( NAME = SGrp1Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp1Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG1Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
FILEGROUP SalesGroup2
( NAME = SGrp2Fi1_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi1dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 ),
( NAME = SGrp2Fi2_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\SG2Fi2dt.ndf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
G. Attach a database
Example B creates a database named Archive with the following physical files:
c:\program files\microsoft sql server\mssql\data\archdat1.mdf
c:\program files\microsoft sql server\mssql\data\archdat2.ndf
c:\program files\microsoft sql server\mssql\data\archdat3.ndf
c:\program files\microsoft sql server\mssql\data\archlog1.ldf
c:\program files\microsoft sql server\mssql\data\archlog2.ldf
The database can be detached using the sp_detach_db stored procedure, and then reattached using CREATE DATABASE with the FOR ATTACH clause:
sp_detach_db Archive
GO
CREATE DATABASE Archive
ON PRIMARY (FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf')
FOR ATTACH
GO
H. Use raw partitions
This example creates a database called Employees using raw partitions. The raw partitions must exist when the statement is executed, and only one file can go on each raw partition.
USE master
GO
CREATE DATABASE Employees
ON
( NAME = Empl_dat,
FILENAME = 'f:',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'g:',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
I. Use mounted drives
This example creates a database called Employees using mounted drives pointing to raw partitions. This feature is available only in Microsoft® Windows® 2000 Server. The mounted drives and raw partitions must exist when the statement is executed, and only one file can go on each raw partition. When creating a database file on a mounted drive, a trailing backslash (\) must end the drive path.
USE master
GO
CREATE DATABASE Employees
ON
( NAME = Empl_dat,
FILENAME = 'd:\sample data dir\',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'd:\sample log dir\',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO --------------------编程问答-------------------- 我比较愚钝，请将我的那段代码改下，前面加上杀链接的。
难道还需要重新调用一个杀链接的类。 --------------------编程问答-------------------- 1楼的意思，是改SqlCommand 执行的sql语句 --------------------编程问答-------------------- 把一楼的代码封装一下，植入你那段判断代码。 --------------------编程问答-------------------- 我比较愚钝，还是不知道代码该如何写。能给我一个关闭数据库链接的类吗？ --------------------编程问答--------------------
SqlConnection con=new SqlConnection("数据库连接字符串");
con.open();//打开数据库
con.Close();//关闭数据库
--------------------编程问答-------------------- 还有LZ你看你那个在进程中吗
这是关闭进程的方法
------------------
--------------------编程问答-------------------- 楼上的没有明白我的意思。
public void KillLine()
{
System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses();
foreach (System.Diagnostics.Process thisprocess in allProcess)
{
string processName = thisprocess.ProcessName;
if (processName.ToLower() == "链接")
{
try
{
thisprocess.Kill();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
return;
}
}
}
}
我说的是在还原数据库前，强行杀死该数据库的所有链接。 --------------------编程问答-------------------- 顶上去，求答案 --------------------编程问答-------------------- 顶上去求回答。
补充：.NET技术 , C#
