Answered by:
Backing up DataBase using VB.net

Question
-
I have an application and Database that I have created in Visual Express 2015.
I'm able to connect and query the Data Base, but when I try to Backup the data base, I get the following error.
"Database 'TestDataBase' does not exist. I Made sure that the name is entered correctly. BACKUP DATABASE is terminating abnormally."
I can connect and query the Database, so I'm sure connection string is OK.
Public SQLCon As New SqlConnection With {.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\users\jeff\documents\visual studio 2015\Projects\Database Backup\Database Backup\TestDataBase.mdf;Integrated Security=True"} Public Sub Backup() Dim stgBackupQuery As String = "BACKUP DATABASE TestDataBase TO DISK='C:\TestDataBase.bak'" Try SQLCon.Open() SQLCmd = New SqlCommand(stgBackupQuery, SQLCon) SQLCmd.ExecuteNonQuery() SQLCon.Close() Catch ex As Exception MsgBox(ex.Message) If SQLCon.State = ConnectionState.Open Then SQLCon.Close() End If End Try End Sub
I tried to add a path to the Database within the stgBackupQuery string, but I got syntax errors.
Not sure what the problem is. I wonder if it could be due the program being run in debug mode in Visual Studio. But I'm not sure.
Thanks
- Edited by 1JRoberts Sunday, March 6, 2016 7:06 AM Grammer
Sunday, March 6, 2016 4:23 AM
Answers
-
Okay I did research on this topic and found that the backup process works (as mentioned before) on databases that are attached to your SQL-Server and not a physically un-attached database such as in your connection string e.g.
TestDataBase.mdf
And set via AttachDbFilename in the connection string.
My database is attached to the server which is why the backup works unlike an attached database
Data Source=KARENS-PC;Initial Catalog=CustomerDatabase;Integrated Security=True
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
- Marked as answer by 1JRoberts Wednesday, March 9, 2016 3:37 AM
Monday, March 7, 2016 2:33 PM
All replies
-
Hello,
I did a search, found code in C#, tried it and worked fine. Here is the vb.net version
Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim connectionString = My.Settings.ConnectionString ' read backup folder from config file ("C:MyBackUps") Dim backupFolder = My.Settings.BackupFolder ' ConfigurationManager.AppSettings["BackupFolder"]; Dim Builder = New SqlConnectionStringBuilder(connectionString) ' set backupfilename (you will get something like: "C:\temp\MyDatabase-2013-12-07.bak") Dim backupFileName = String.Format("{0}{1}_KP1.bak", backupFolder, Builder.InitialCatalog) Console.WriteLine(backupFileName) Using connection = New SqlConnection(Builder.ConnectionString) Dim query = String.Format("BACKUP DATABASE {0} TO DISK='{1}'", Builder.InitialCatalog, backupFileName) Using command = New SqlCommand(query, connection) connection.Open() command.ExecuteNonQuery() End Using End Using End Sub End Class
I setup connection and backup folder as follows and note I am using a full version of SQL-Server so my data source is KARENS-PC, not (local) or .\SQLEXPRESS. Backup folder must exists.
<?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" > <section name="WindowsFormsApplication1.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" /> </sectionGroup> </configSections> <connectionStrings> <add name="WindowsFormsApplication1.Properties.Settings.ConnectionString" connectionString="Data Source=KARENS-PC;Initial Catalog=CustomerDatabase;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /> </startup> <userSettings> <WindowsFormsApplication1.Properties.Settings> <setting name="BackupFolder" serializeAs="String"> <value>C:\Data\Backup\</value> </setting> </WindowsFormsApplication1.Properties.Settings> </userSettings> </configuration>
Note to makes many copies you could manipulate the file name format rather than hard code it as shown here.
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
- Edited by KareninstructorMVP Sunday, March 6, 2016 11:37 AM
Sunday, March 6, 2016 11:35 AM -
Karen,
Thank you for your reply.
I'm trying to understand your code and I have a few questions.
First, The second bit of code that starts with <?xml. Where does that come from?
I see code that kinda looks like that in the Solution Explorer in a file called "Application.myapp". Do I paste that portion of the code into that file? (Customizing it to my configuration from the machine.config file?). When I tried typing "My.Settings.ConnectionString", IntelliSense didn't like the word ".ConnectionString". My guess it's looking for that xml code.
Second, The first part of code looks fairly similar to the code that I'm using, except the Connection string, Query string and Backup file name are "Built", for lack of a better word, at run time, rather than hard coded at design time.
I tried a different connection string that I got from the machine.config file.
<connectionStrings> <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/> </connectionStrings> Which produced a connection string of: Public SQLCon As New SqlConnection With {.ConnectionString = "Data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true"}
Which didn't work at all. I couldn't even query the Database. It just locked up the program.
You stated that the code worked fine. Was this in Visual Studio's debug mode?
The reason I ask is because I read that Visual studios makes a copy of the DataBase in Debug mode, and works off the copy. I was thinking maybe that is why it's not working.
Everything else in the program associated with Database works, just not the backup.
Thanks for your help.
https://visualstudiomagazine.com/Blogs/Tool-Tracker/2012/05/Dealing-with-Local-Databases-or-Why-Your-Updates-Dont-Stick.aspx
Monday, March 7, 2016 2:55 AM -
To obtain that which I presented (in app.config file in the project), using the data source window in the ide, select a database, select a table in the add new data source. Drag the table onto a form, many components and controls are added to the form. Once completed remove all those components and controls.
What this gave you (I do the above process by hand coding but note this is done from experience) are the settings I showed in app.config.
Now all the above is against a database on SQL-Server, not an attached database where an attached database is more likely not going to work as I have not seen any indication this procedure will work unless the database is attached to sql server and not local e.g. resides in your application folder.
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
- Proposed as answer by elisch123 Sunday, September 27, 2020 4:48 PM
Monday, March 7, 2016 3:17 AM -
Now all the above is against a database on SQL-Server, not an attached database where an attached database is more likely not going to work as I have not seen any indication this procedure will work unless the database is attached to sql server and not local e.g. resides in your application folder.
I created the database within Visual Studios.
I looked at installed programs on my computer and it has "Microsoft SQL Server 2014 Express Local DB". So I'm assuming that SQL server was installed with Visual Studios.
Do I need to download SQL Server Express with tools or advanced services. Do I also need Server Management Studio?
Sorry if these are dumb questions. This is my first Database application.
Thanks.
Monday, March 7, 2016 4:30 AM -
As per the screenshot below via SQL-Server Management Studio AdventureWorks can be backed up but not NORTHWND.MDB unless it is "attached" inside of SQL-Server Management Studio but not when not attached.
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
Monday, March 7, 2016 10:05 AM -
Robert,
I've never seen this going. For a backup of most databases, the database has to be disconnected.
But as soon that you do it from a program while there is a connection that is not the case.
Of course can you send a sql message to the server using a Batch file and start that with command
Success
CorMonday, March 7, 2016 10:38 AM -
Cor this is possible while being connected where the connection is in app.config in this case shown in a prior reply.
Imports System.Data.SqlClient ''' <summary> ''' Requires VS2015 or higher for string interpolation, otherwise VS2010 or higher ''' </summary> ''' <remarks> ''' Plenty of assertion done, feel free to use less if so desired ''' </remarks> Public Class SqlServerOperations Private BackupFileName As String Public ReadOnly Property FileName As String Get Return BackupFileName End Get End Property ''' <summary> ''' Location for creating backup of database ''' </summary> ''' <returns></returns> Public Property FolderName As String ''' <summary> ''' Connection string to work with our database ''' </summary> ''' <returns></returns> Public Property ConnectionString As String ''' <summary> ''' Make an exception available outside this class ''' </summary> ''' <returns></returns> Public Property Exception As Exception ''' <summary> ''' Determine if an exception has been recorded ''' </summary> ''' <returns></returns> Public ReadOnly Property HasError As Boolean Get Return Exception IsNot Nothing End Get End Property ''' <summary> ''' Provides next increment ''' </summary> ''' <returns></returns> Public ReadOnly Property NextIncrementValue As Integer Get If String.IsNullOrWhiteSpace(FolderName) Then Throw New Exception("Missing FolderName") End If If String.IsNullOrWhiteSpace(ConnectionString) Then Throw New Exception("Missing connection string") End If If Not IO.Directory.Exists(FolderName) Then Throw New Exception($"'{FolderName}' not located") End If Dim nextValue As Integer = 0 Try Dim connectionString = Me.ConnectionString Dim Builder = New SqlConnectionStringBuilder(connectionString) ' ' Assumes extension is .bak and the file name convention is ' FileNameN.bak ' where N is a numeric value e.g. 1,2,3 etc ' Return CInt(IO.Directory.GetFiles(FolderName, "*.bak") _ .Select(Function(file) IO.Path.GetFileName(file)) _ .Where(Function(file) file.StartsWith(Builder.InitialCatalog)) _ .Select(Function(file) file.Replace(Builder.InitialCatalog, "").Replace(".bak", "")).Last) + 1 Catch ex As Exception Throw New Exception("Failed to get increment for backup database") End Try Return nextValue End Get End Property ''' <summary> ''' Do the actual backup ''' </summary> ''' <returns></returns> Public Function Backup() As Boolean Dim Success As Boolean = False Dim Builder = New SqlConnectionStringBuilder(ConnectionString) Try BackupFileName = $"{FolderName}{Builder.InitialCatalog & NextIncrementValue.ToString}.bak" Catch ex As Exception Exception = ex Return False End Try Try Using connection = New SqlConnection(Builder.ConnectionString) Dim query = String.Format("BACKUP DATABASE {0} TO DISK='{1}'", Builder.InitialCatalog, backupFileName) Using command = New SqlCommand(query, connection) connection.Open() command.ExecuteNonQuery() End Using End Using Success = True Catch ex As Exception Exception = ex Success = False End Try If Success Then If IO.File.Exists(backupFileName) Then Return True Else Return False End If Else Return False End If End Function End Class
Demo
Public Class Form1 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim operations As New SqlServerOperations With { .FolderName = My.Settings.BackupFolder, .ConnectionString = My.Settings.ConnectionString } If operations.Backup() Then MessageBox.Show($"Created {operations.FileName}") Else If operations.HasError Then MessageBox.Show($"Failed: {operations.Exception}") Else MessageBox.Show("Failed to create backup") End If End If End Sub End Class
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
Monday, March 7, 2016 10:56 AM -
Okay I did research on this topic and found that the backup process works (as mentioned before) on databases that are attached to your SQL-Server and not a physically un-attached database such as in your connection string e.g.
TestDataBase.mdf
And set via AttachDbFilename in the connection string.
My database is attached to the server which is why the backup works unlike an attached database
Data Source=KARENS-PC;Initial Catalog=CustomerDatabase;Integrated Security=True
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
- Marked as answer by 1JRoberts Wednesday, March 9, 2016 3:37 AM
Monday, March 7, 2016 2:33 PM -
https://visualstudiomagazine.com/Blogs/Tool-Tracker/2012/05/Dealing-with-Local-Databases-or-Why-Your-Updates-Dont-Stick.aspx
This link deals with something else and to see more
https://msdn.microsoft.com/en-us/library/ms246989.aspx?f=255&MSPPError=-2147217396
https://code.msdn.microsoft.com/Working-with-Copy-to-11fbc251
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator
Monday, March 7, 2016 2:35 PM -
I installed SQL Server 2014 Express and Management Studio, and now I can backup the Database thru my application. After working with Management Studio, however, I discovered I could just use that to backup the Database, instead of through the application. However, I worked hard on the code so I'm using it. :)
One problem that I did run into, that might help someone else that might come across this, is that the backup file referenced in the Query string
Dim stgBackupQuery As String = "BACKUP DATABASE TestDataBase TO DISK='C:\TestDataBase.bak'"
needs to already exist, or you will get an error. So you will need to backup the DB first in Management Studio, and move the backup file into the desired location. (I also tried just renaming a text file to "TestDataBase.bak" and then letting BACKUP overwrite the file, and the seemed to work also.) Since the file (name) needs to already exist, it appears that concatenating a date onto the backup file's name won't work. Unfortunate, but I can live with that.
Thanks again for your help.
Much appreciated.
Wednesday, March 9, 2016 4:45 AM -
Karen,
I just stumbled on this as a possible solution to what I am trying to accomplish. I am seeking a backup to my existing database in VS express. I need this backup so that I can restore on my remote server to initialize the DB. I have installed SSMS and figured out how to connect to the engine, and I thought it would be simple from here on, but its not for me. Would appreciate your input.
Sunday, September 27, 2020 4:18 PM -
Karen,
I just stumbled on this as a possible solution to what I am trying to accomplish. I am seeking a backup to my existing database in VS express. I need this backup so that I can restore on my remote server to initialize the DB. I have installed SSMS and figured out how to connect to the engine, and I thought it would be simple from here on, but its not for me. Would appreciate your input.
Generally one backs up via SSMS as per below, once the dialog appears click OK.
Code wise there is SMO for full editions of SQL-Server, unsure if it will work with SQLEXPRESS (I use the full edition of SQL-Server) as per Microsoft docs.
See also my Microsoft TechNet article on SMO (with source). The only thing I really do with SMO is copy an existing database with tables, data, indices etc.
In closing
- SMO if a consideration will take time and may be frustrating at first.
- SMO is dependent on the version of SSMS so if you can SSMS edition and uninstall a earlier version this will break code but if you leave the earlier version zero issues. I have two versions of SSMS installed.
Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
NuGet BaseConnectionLibrary for database connections.
Sunday, September 27, 2020 4:51 PM -
Thank you Karen, but a few of the statements are too advanced for me. "using the data source window in the ide, select a database, select a table in the add new data source". The configuration statements go where? I'm committed to getting this done in this way but can you just tell me if the codeless way that I used 4 years ago, went away with Windows 10. It seems that I was able to get a satisfactory backup on windows 7 back then but I cant replicate that now.
Thanks again.
Monday, September 28, 2020 7:47 PM