Backup cant find database name

I was trying to backup my database file using My.Computer.FileSystem.CopyFile but because SQL keeps the file locked this doesnt work (I am using VB.NET 2005 Express and SQL 2005 Express). It seems that the Backup command is what I want to do but it doesnt recognise my database name, which otherwise appears in the Database Explorer tab of the IDE

I wrote a function to use the Backup command as follows
Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
Debug.Print(sqlconn.Database)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE MyDatabase TO DISK = 'c:\Mydatabase.bak'")

Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()

End Sub

But this gives the error
Could not locate entry in sysdatabases for database 'Mydatabase'. No entry found with that name. Make sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally.

I have also tried with the .mdf extension, with the full path name, and with the name as given by sqlconn.database which is supposed to be the full database name, with and without single quotes surrounding it.

How can I find out which database name this function requires and why it is not finding the database? Other queries on it are working fine.

Thanks

Martin

hi,

the Transact SQL syntax is correct... but this works against "traditional" SQL Server (and SQLExpress) instances... are you perhaps working with a user instance (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp)?

if this is the case, the database name you see in the IDE (VS ide or SQL Server Management Studio IDE?) is not the same as the registered database name in the running SQLExpress user instance.. the name is composed with the file + path of the .Mdf file the database is composed of..

regards

|||

Andrea Montanari wrote:

hi,

the Transact SQL syntax is correct... but this works against "traditional" SQL Server (and SQLExpress) instances...

should be read as "but this works directly against "traditional" SQL Server (and SQLExpress) instances... user istances modify the database name as reported..

I apologise..

regards

|||Thanks Andrea for the link to the article on User Instances, I was using that method as it was the default method used in the connection string generated by the database wizard, so I did need the full path. But that was not the full story, because I still had problems getting SQL to recognise this database name.
In the end, the correct database name was given in the connection object sqlconn.database but because it had had its first few characters replaced with some sort of unique identifier which started with numbers and an E, the Backup command seemed to think it was a floating point number even though it was expecting a database name at this position in the command. Putting it in single quotes did not work, but after lots of guessing different syntax variations, I tried square brackets and it worked.

For anyone else with this problem, here is the form of my solution:

Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE [" & sqlconn.Database.ToString & "] TO DISK = 'c:\Mydatabase.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub|||

Hi, I'm trying to do exactly the same thing as you did. You solution is a great help to me. However, when I modify the sqlCommand for Restore operation, I won't allow me since the database is currently used by my application. Would you tell me how you did that? Thanks.

Public Sub RestoreOperation(ByVal restorePath As String)

Dim sqlconn As New SqlClient.SqlConnection(GlobalConnectionString)

Try

' the command for restoring the DB

Dim cmdBackup As New SqlClient.SqlCommand("RESTORE DATABASE [" & "Test.mdf" & _

"] FROM DISK = '" & restorePath & "'", sqlconn)

sqlconn.Open()

cmdBackup.ExecuteNonQuery()

Finally

sqlconn.Close()

End Try

End Sub

|||

hi,

modify the context of your connection via con.ChangeDatabase or close it and open another one referencing the master system database...

regards

|||

Hi Andrea, thanks for your suggestion. It seems very good to me. However, I have a problem. Since I'm new in ADO.net, I use the most basic solution (drag-and-drop from Data source Explorer) to setup my database. So my program have a bunch of forms and each have their own TableAdapters. So other than closing all the connections from each adapter, is there other way to do it? Can it be done by SQL? Appreciated.

This is my connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Test.mdf;Integrated Security=True;Connect Timeout=60;User Instance=True

|||

hi,

actually Inever use your approach, thus drag&drop ...but, anyway, all database connections against the database to be restored must be closed before restoring...

regards

|||Yeah, I guess I don't have much choice. Thanks for your reply.