locked
close Connection in sql server 2005 express RRS feed

  • Question

  • Hello.

    i want close the connection from my database then copy the database files(mdf and ldf files) to another directory.

    how can close the connection?

    i tried this code but didnt work.

            bool DCDatabase()
            {
                SqlConnection objConnection = new SqlConnection();
                objConnection.ConnectionString = ConectionString.FilePath;
    
                SqlCommand objCommand = new SqlCommand();
                objCommand.Connection = objConnection;
                objCommand.CommandText = "CLOSE DATABASE";
                objCommand.CommandType = CommandType.Text;
    
                SqlDataAdapter sqlDataAdaptoObj = new SqlDataAdapter();
                sqlDataAdaptoObj.SelectCommand = objCommand;
    
    
                try
                {
                    objConnection.Open();
                    objCommand.ExecuteNonQuery();
                    objConnection.Close();
                    return true;
                }
                catch (Exception) 
                {
                    return false;
                }
            }

    Friday, March 30, 2012 3:52 PM

Answers

All replies

  • It sounds like you are talking about two different things here.  From your client code, "closing the connection" refers to just that, closing the original connection in your code that you made to your running/online database instance.  To copy .mdf and .ldf files, you are much better off letting SQL Server handle the necessary steps of ensuring your database will be in a good state.  You will want to use backup/restore on the server or detach the database and then copy the .mdf/.ldf files.  Copying the .mdf/.ldf files from an online database is not good practice and can leave the DB and the copied files in an uncertain state.

    Thanks,
    Sam Lester (MSFT)


    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.



    Friday, March 30, 2012 4:07 PM
  • Hello,

    You can't "close" the database this way, you have to detach the database, move it an attach the database again, see http://msdn.microsoft.com/en-us/library/ms190794.aspx


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Naomi N Friday, March 30, 2012 7:21 PM
    Friday, March 30, 2012 4:08 PM
  • Thanks olaf.

    as you say i should detach the database.

    i use sql express 2005. i red your link.for detaching database this sp offer:

    sp_detach_db [ @dbname= ] 'database_name' 
        [ , [ @skipchecks= ] 'skipchecks' ] 
        [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ] 

    but sp didnt work!

    where is the problem?


    • Edited by Bouki Friday, March 30, 2012 7:00 PM
    Friday, March 30, 2012 6:46 PM
  • but sp didnt worked! where is the problem?

    What does "didn't worked"? Do you get an error message or ... ?

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Friday, March 30, 2012 6:53 PM
  • yes.

    i use this sp exactly:

    CREATE PROCEDURE sp_detach_db
    as
        [ @dbname= ]
        [ , [ @skipchecks= ] 'skipchecks' ] 
        [ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ] 
    return

    this error happen:

    incorrect syntax near '@dbname='


    Friday, March 30, 2012 6:58 PM
  • You shall not create the procedure, it's a system stored procedure which already exists. You simply have to use it like:

    USE master;
    GO
    ALTER DATABASE YourDatabaseName
    SET SINGLE_USER;
    GO
    
    EXEC sp_detach_db @dbname='YourDatabaseName'
    

    Ensure that no user is connected to the database, even not you / your connection, otherwise it would fail. The statement above switches the database to single user mode to ensure no one is connected.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by Naomi N Friday, March 30, 2012 7:22 PM
    Friday, March 30, 2012 7:10 PM
  • is this query?

    i really confused!

    i use vs interface to using database. is this my problem?

    Friday, March 30, 2012 7:29 PM
  • Yes, that's the complete query (= CommandText) to detach the database.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Friday, March 30, 2012 8:27 PM
  • thanks but as i say didnt work.

    because i use Visual Studio server explorer.

    is there another way?

    Friday, March 30, 2012 8:35 PM
  • I thought you want to do this with your .NET code; so why are you now using VS server explorer?

    You could use SSMS = SQL Server Management Studio as well, there is also a free Express Edition of SSMS available.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Friday, March 30, 2012 8:44 PM
  • really?

    can you give me the download link?

    Friday, March 30, 2012 9:02 PM
  • Every web search engine should give you the link ...

    http://www.microsoft.com/download/en/details.aspx?id=22985


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Bouki Saturday, March 31, 2012 5:56 AM
    Friday, March 30, 2012 9:08 PM