How do I release sql express database?
Questions
I have a simple app that
uses an SQL Express 2005 database. When the user closes the app, I want
to give the option to back up the database by making a copy in another
directory. However, when I try to do it, I get "The process cannot
access the file '...\Pricing.MDF' because it is being used by another
process." I closed the connection, disposed the connection, set it to
nothing, and GC.Collect(), but it makes no difference. My connection
string is "Data
Source=.\SQLEXPRESS2005;AttachDbFilename=|DataDirectory|\Pricing.mdf;Integrated
Security=True; User Instance=True" and I just keep using the same
connection throughout. I didn't see where I could detach the database
to counter the attach in the connection string.
1 - How do I RELEASE the thing? 2 - Is there a better way than just
copying the database? The app is for my husband only, so I will be able
to handle it if he actually does need to restore from backup.
Thanks!
--------------------------------------------------------------------------------------------------
Answer
You don't want to copy the mdf directly because SQL keeps most of
the changes in the transaction log, take a look at the modified time
after running some queries, it doesn't write directly to the file. I
noticed this while setting up an rsync job.
Having SQL generate the backup is much safer and more desirable,
single-user or multi-user. You can provide a link to a function calling
the T-SQL which you can completely automate as far as source db and
destination folder:
BACKUP DATABASE [mydatabasename]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Scheduled Task Backups\mydatabasename-backup' WITH NOFORMAT, NOINIT, NAME = N'mydatabasename-Full Data
SQL 2005 had introduced another T-SQL syntax to do this, for the life
of me I can't find it. But there are ways to do it through M$$SQL
without having the full blown database server.
相关文档：
ALTER FUNCTION [dbo].[fun_tongji]()
RETURNS @t1 table (
yue int ,
money int
)
AS
begin
Declare @i int
set @i=1
-- declare @t1 table (
-- yue int ,
-- money int
-- )
while (@i<=12)&n ......
如何远程判断Oracle数据库的安装平台
select * from v$version;
查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES ......
一、 镜像简介
1、 简介
数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。
要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”，第二个服务器被 ......
SQL Server 2005数据库镜像简介
自从SQL Server 2000以来，你已经能够通过使用复制来创建一个备用的服务器、传输日志，以及备份和重新存储了——但是现在微软又引入了一个内建的工具，它可以实现自动的错误恢复。数据库镜像是SQL Server 2005的一个新特性，它允许你将一个SQL Server中的数据库内容镜像到另一个S ......
第一种用法：
SELECT (CASE type WHEN 'u' THEN '用户表' WHEN 's' THEN '系统表' ELSE '其他' END) AS TABLEKIND,* from SYSOBJECTS
第二中用法：
SELECT (CASE WHEN crdate<'2006-01-01' THEN 'early' WHEN crdate<'2009-01-01' THEN 'nearly' ELSE 'now' END) AS PERIOD,* from SYSOBJECTS ......
