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.
Ïà¹ØÎĵµ£º
ÔÎÄת×Ô:http://tech.it168.com/a2009/0218/265/000000265868.shtml
Ë÷ÒýÊÇÒÔ±íÁÐΪ»ù´¡µÄÊý¾Ý¿â¶ÔÏó¡£Ë÷ÒýÖб£´æ×űíÖÐÅÅÐòµÄË÷ÒýÁУ¬²¢ÇҼͼÁËË÷ÒýÁÐÔÚÊý¾Ý¿â±íÖеÄÎïÀí´æ´¢Î»Öã¬ÊµÏÖÁ˱íÖÐÊý¾ÝµÄÂß¼ÅÅÐò¡£Í¨¹ýË÷Òý£¬¿ÉÒÔ¼Ó¿ìÊý¾ÝµÄ²éѯËٶȺͼõÉÙϵͳµÄÏìӦʱ¼ä;¿ÉÒÔʹ±íºÍ±íÖ®¼äµÄÁ¬½ÓËٶȼӿ졣
µ«ÊÇ£¬ ......
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 ......
1¡¢ËµÃ÷£º¸´ÖƱí(Ö»¸´Öƽṹ,Ô´±íÃû£ºa бíÃû£ºb) (Access¿ÉÓÃ)
·¨Ò»£ºselect * into b from a where 1<>1
·¨¶þ£ºselect top 0 * into b from a
2¡¢ËµÃ÷£º¿½±´±í(¿½±´Êý¾Ý,Ô´±íÃû£ºa Ä¿±ê±íÃû£ºb) (Access¿ÉÓÃ)
insert into b(a, b, c) select d,e,f from b;
3¡¢ËµÃ÷£º¿çÊý¾Ý¿âÖ®¼ä±íµÄ¿½±´(¾ßÌåÊý¾ÝʹÓþø¶Ô· ......
¼ÇÒ»¸ö×ÔÈÏΪдµÃÓе㸴ÔÓµÄsqlÓï¾ä£¬º¬ÒåÊÇ¿ç3ÕűíµÄselect£º
select table_name,column_name,data_type,data_length,data_scale from user_tab_columns where (data_type != 'NUMBER' or data_length != 22 or data_scale != 6) and (table_name,column_name) in (select upper(a.table_name_eng), upper(b.column_name_ ......
sql ´úÂë:
---------------------------------------------------------
/*
a ±íÃû
Èç¹ûÔÚsql ²éѯ·ÖÎöÆ÷µ±ÖгöÏÖ
" SQL Server ×èÖ¹Á˶Ô×é¼þ 'xp_cmdshell' µÄ ¹ý³Ì'sys.xp_cmdshell' µÄ·ÃÎÊ£¬ÒòΪ´Ë×é¼þÒÑ×÷Ϊ´Ë·þÎñÆ÷°²È«ÅäÖõÄÒ»²¿·Ö¶ø±»¹Ø± ......