[RESOLVED] How can I backup an OPEN Server database?

[DexterRose]

I am using VB 2010 Professional and Server 2008.

How can I backup an open Database? or is this even possible?

[szlamany]

Check this post and the link within

http://www.vbforums.com/showthread.p...=1#post2070901

[DexterRose]

My question really is about an OPEN DATABASE. Will "BackUp mydatabase to disk" work if the database is OPEN?

[szlamany]

BACKUP is the only way to BACKUP a MS SQL database - and it's designed to work when the DB is open. That's the cool thing about MS SQL - it's perfect like that.

If you don't do regular backup's your LOG file can grew like crazy. BACKUP is good.

Do you understand how the LOG file works in MS SQL?

[DexterRose]

Nope

[DexterRose]

THIS IS FROM A PREVIOUS POST WHICH I COULD NEVER RESOLVE.
DO YOU HAVE ANY IDEA WHAT IS WRONG WITH THE CODE BELOW?


Using VB 2010 Professional.

I am trying to backup my Server 2008 Database for the first time using the code below.

I am getting an error message" "Database AbcDatabase does not exist. Make sure the name is entered correctly." on the "SqlCmd.ExecuteNonQuery()" line.

AbcDatabase is the correct name. What am I doing wrong?

Dim AbcServerDB As String = "C:\Program Files\Microsoft Sql Server\MSSQL10.SqlExpress\MSSQL\DATA\AbcDatabase.mdf"
 
If File.Exists(AbcServerDB) Then
               Dim BackupCmptr As String = "AbcLaptop"
               Dim DestinationFolderAndFile As String = "\\" & BackupCmptr & "\BackupAbcHal\AbcDatabase.bak"
               Dim SqlConn As SqlConnection
               Dim SqlCmd As SqlCommand
               
Dim sMyConnectionString As String = "Data Source=" & PrimaryServer _
                      & "\SqlExpress; Database=C:\Program Files\Microsoft Sql   Server\MSSQL10.SqlExpress\MSSQL\DATA\AbcDatabase.mdf; " _
                      & "User Id=abc99; Password=abc99password;"
 
                SqlConn = New SqlConnection(sMyConnectionString)
                SqlConn.Open()
                
Dim ThisQuery As String = "backup database AbcDatabase to disk='\\NewAbcLaptop\BackupAbcHal\AbcDatabase.bak'"
 
                SqlCmd = New SqlCommand(ThisQuery, SqlConn)
                SqlCmd.ExecuteNonQuery()
 
                SqlConn.Close()

[szlamany]

Connection strings do not take the "path" to a MDF - the INSTANCE of MS SQL that is running knows where the file is physically.

Have you ever got a connection to this DB working - like elsewhere in your code?

[DexterRose]

Many different programs over many months. What's wrong with the connection string? ie, how would you suggest that I change it? I'm still fairly new at .Net.

[szlamany]

They look like this

"Data Source=10.42.0.41\sqlexpress;Initial Catalog=TCS; User Id=abc; Password=xyz"

SERVER name goes in DATA SOURCE and the DATABASE goes into INITIAL CATALOG.

Unless you are trying to mount the DB on the initial open - that's kind of like how LOCALDB works (I believe - I don't use that MS SQL feature).

Show a connection string from a program that is working.

[DexterRose]

Well, aren't you the early bird. Thanks for your help and your helpful attitude!!

I use this same connection string in all my programs that I have written so far and they all work just fine. I think I am connecting to the DB directly?.?

[DexterRose]

I got a log in failure on the string below in the TableAdapter.Fill statement. I do have have my connection string in my Load routine.

Private Sub BackupPictures_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try
            Me.LocationTableTableAdapter.Fill(Me.AbcLocationDBDataSet.LocationTable)
            PrimaryServer = txtLocPrimaryServer.Text
 
            'Dim sMyConnectionString As String = "Data Source=" & PrimaryServer _
            '       & "\SqlExpress; Database=C:\Program Files\Microsoft Sql Server\MSSQL10.SqlExpress\MSSQL\DATA\AbcDatabase.mdf; " _
            '       & "User Id=abc99; Password=abc99password;"
 
            Dim sMyConnectionString As String = "Data Source=" & PrimaryServer _
                    & "\sqlexpress;Initial Catalog=AbcDatabase; User Id=abc99; Password=abc99password"
 
            Me.DealershipTableTableAdapter.Connection.ConnectionString = sMyConnectionString
            Me.DealershipTableTableAdapter.Fill(Me.AbcDatabaseDataSet.DealershipTable)

[szlamany]

Do you use SQL Server Management Studio to work with this SQL instance? Since SQL normally runs as a SERVICE on a SERVER or workstation it is usually talked to with SSMS.

[DexterRose]

I have only been using SSMS to make changes to my database table. As you can probably tell, I am new at this and may be doing things incorrectly.

[szlamany]

If you are using SSMS then you have an object explorer on the left you can open and see a branch for your DB. If that is the case then the DB is already mounted to the service running.

Your connection string is not correct. Follow these three steps and make this same screen shot for me to see.

[DexterRose]

SZLAMANY, you are my hero.


Lo and behold, when I looked at the screen shot of my SSMS, perched in the Database Name field was "Master" and not AbcDatabase.

Backup Program works fine now with MASTER as the database name.

Thanks again for all of your help!

[techgnome]

GAAAAAH! MASTER? Noooooo! (slow motion leaping over chairs, reaching out)

The master database is the main master database that SQL Server uses to maintain the list of databases and other internal states... good gawd man! Please tell me you didn't put your tables or do development in the master database!

-tg

[szlamany]

I'm afraid...

[DexterRose]

Before we both panic, maybe I RCed the wrong line.

If I expand Databases in SSMS there are 2 items "System Databases" and "a DB symbol with the full path to my DB"

If I then RC the topmost line in the screen, that's where I see Master in the Database Name field.

If, however, I RC "the Symbol with the full path to my DB", Click Properties, and then Click "View Connection Properties", I get the full path to my DB in the Database Name field.

The Backup only works if I use Master as the Database.

[DexterRose]

I created my DB and Set up SSMS about a year and a half ago, when I first started learning .Net. I absolutely did not know what I was doing back then(self taught) and don't remember how I went about it.

I get the correct DB, "AbcDatabase", in the backup folder when I run my backup.



Dim ThisQuery As String = "backup database master to disk='\\" & BackupCmptr & "\BackupAbcHal\AbcDatabase.bak'"

[szlamany]

You are backing up a DATABASE called MASTER - so stop that immediately.

You need to get your connection string working as I said in several posts.

Until then you are simply masking problems.

[DexterRose]

I still don't know what Database Name I should use see attached ScreenShot

[szlamany]

I do not know how you got the DATABASE name to appear with a FOLDER in front of it.

And that picture is simply not readable - the forum here has really messed that up on us.

Argh!

[DexterRose]

Try this picture.

What Do you suggest that I do to change the database name at this point?

[DexterRose]

Should I just rename the Database here by right clicking the Database Name symbol shown under the "Server Databases" symbol, selecting Rename, and then changing the Database Name from "C:\Program Files............." to "AbcDatabase" ?

[techgnome]

Yup.
It probably got that way because of how you had the connection string in the first place and it got attached as a user instance or something. One way or another, it got created incorrectly.

-tg

[DexterRose]

Thanks to everyone for helping me solve this problem!!