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 ......
通常，你需要获得当前日期和计算一些其他的日期，例如，你的程序可能需要判断一个月的第一天或者最后一天。你们大部分人大概都知道怎样把日期进行分割（年、月、日等），然后仅仅用分割出来的年、月、日等放在几个函数中计算出自己所需要的日期！在这篇文章里，我将告诉你如何使用DATEADD和 DATEDIFF函数来计算出在你的程序 ......
一、 镜像简介
1、 简介
数据库镜像是将数据库事务处理从一个SQL Server数据库移动到不同SQL Server环境中的另一个SQL Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。
要进行数据库镜像所需的最小需求包括了两个不同的SQL Server运行环境。主服务器被称为“主机”，第二个服务器被 ......
第一种用法：
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 ......
