To attach database from c# coding

[Not Active]

NarendraSinghJTV wrote:

application which generates database through coding

How are you doing this? It would be the same way on a SQL Server.

---

I know the language. I've read a book. - _Madmatt

[NarendraSinghJTV]

I m using stored procedure for all creating new database, backup of that generated database. sometimes it shows the error like 'cannot open the backup device'.

Regards Narendra Singh (Jindal Tech Ventures)

[Henry Minute]

There is a built in stored procedure sp_attach_db which will accomplish the first part for you. Refer to BooksOnLine documentation or MSDN for more details. To accomplish the backup and for an example using sp_attach_db download the SQLServer 2008 samples from MSDN, if you haven't already, extract the files and then do a text search in the Samples\Engine\Programmability\SMO folder for the above stored proc and for 'backup' There is also a very good article on backups on MSSQLTips.com, just use the search on the home page for 'Backup and Restore SQL Server databases programmatically with SMO'. Good luck! :)

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[NarendraSinghJTV]

but i think sp_attach_db works only if that database is previously dettached from the server

Regards Narendra Singh (Jindal Tech Ventures)

[Henry Minute]

I have used it for new Databases. Worked fine!

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[NarendraSinghJTV]

can backup work fine after attach database to server, i have attached database to server but it is not finding the database file name.

Regards Narendra Singh (Jindal Tech Ventures)

[Henry Minute]

I am at the moment searching my own code for suitable example. Will get back to you in a while.

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[NarendraSinghJTV]

ok i m waiting for your positive reply, one more thing can we call sp_attach_db stored procedure programatically

Regards Narendra Singh (Jindal Tech Ventures)

[Henry Minute]

Here is a snippet which doesn't use sp_attach_db, since it seems to be giving you some problems.

    public static void AttachDatabase(Server server)
    {
        string databaseName;
        StringCollection dbFiles = new StringCollection();
        Dialogs.DBFilesDialog dbfd = new Dialogs.DBFilesDialog();
        if (dbfd.ShowDialog() == DialogResult.OK)
        {
            dbFiles.Add(dbfd.PrimaryFileName);
            dbFiles.AddRange(dbfd.DataFiles);
            dbFiles.AddRange(dbfd.LogFiles);
            databaseName = dbfd.DatabaseName;

            // Attach the database
            if ((dbFiles\[0\] != null) && (dbFiles\[0\] != string.Empty))
            {
                // Are you sure?  Default to No.
                if (MessageBox.Show(@"Do you really want to Attach the " + dbFiles\[0\] +
                    @" database?", Application.ProductName, MessageBoxButtons.YesNo,
                    MessageBoxIcon.Question,
                    MessageBoxDefaultButton.Button2) == DialogResult.No)
                {
                    return;
                }

                server.AttachDatabase(databaseName, dbFiles, AttachOptions.RebuildLog);
            }
        }
    }

The server object is an instance of the Server class from the Microsoft.SqlServer.Management.Smo namespace. Dialogs.DBFilesDialog is a self built Dialog which simply enables the user to select the primary files and log files for the database in one place. Basically it is two ListBoxes, each with an associated button which, when clicked, presents an OpenFileDialog filtered for *.mdf and *.ldf respectively. It has properties for the Primary File name (string), DataFiles (string[]) and LogFiles (string[]). The code is from a VS2005 SQLUtilities library of mine but should translate directly to VS2008(10?) with no problems.

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[NarendraSinghJTV]

thanks henry for your precious code but i m using object database not smo

Regards Narendra Singh (Jindal Tech Ventures)

[Henry Minute]

It's a pleasure. :) Good luck.

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”