none
BACKUP LOCAL DATABASE RRS feed

  • Question

  • I have the following code:

     Dim BackupQuery As String
            BackupQuery = "Backup database Requirements.mdf To Disk=g:\test.bak"
            Using conn As New SqlConnection(My.Settings.RequirementsConnectionString)

                Using comm As New SqlCommand()
                    With comm
                        .Connection = conn
                        .CommandType = CommandType.Text
                        .CommandText = BackupQuery
                    End With
                    Try
                        conn.Open()
                        comm.ExecuteNonQuery()
                        MsgBox("Backup Completed!", MsgBoxStyle.OkOnly, "Successful")
                        conn.Close()
                    Catch ex As SqlException
                        MessageBox.Show(ex.Message.ToString(), "Error Message")
                    Finally
                        conn.Dispose()
                    End Try
                End Using
            End Using

    What ever a try I always get the same error: Incorrect syntax near '.' What am I doing wrong??

    Friday, January 17, 2020 7:06 AM

All replies

  • Dim sqlcon As New SqlConnection
            Dim sqlcmd As New SqlCommand
            Dim dbname As String = "Requirements.mdf"

            'connection string voor database PermitFees
            sqlcon.ConnectionString = My.Settings.RequirementsConnectionString & ";Initial Catalog=CustomerDatabase;Integrated Security=True"

            'destination folder
            Dim destdir As String = "G\test.bak"

            sqlcon.Open()

            Dim strQuery = "Backup database Requirements.mdf To Disk=G:\test.bak"
            sqlcmd = New SqlCommand(strQuery, sqlcon)


            sqlcmd.ExecuteNonQuery()
            sqlcon.Close()
    Friday, January 17, 2020 7:32 AM
  • Is there some reason you are not using SSMS to backup the database?
    Friday, January 17, 2020 7:45 AM
  • I want the backup to be executed by clicking on a button in the program.

    Friday, January 17, 2020 7:59 AM
  • Hello,

    >"Backup database Requirements.mdf To Disk=g:\test.bak"

    Backup database [Requirements.mdf] To Disk=N'g:\test.bak'

    Not sure about [Requirements.mdf] - normaly used name in database engine and it not the same as name of the file. 

    Simple way to get required statement - use SSMS to connect to selected database and get script for backup operation. 


    Sincerely, Highly skilled coding monkey.


    Friday, January 17, 2020 10:10 AM
  • Hi,

    Because the previous SQL statement did have problems.

    There are two errors in your code.

    First, the database name is wrong. Because I don't know what your database connection string is, I can't help you find the name of your database. You can find it out by the code as follows:

    Imports System.Data.SqlClient
    
    Module Module1
        Sub Main()
            Try
                Dim connectString As String =
                 "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\juliex\Desktop\Book.mdf"
    
                Dim builder As New SqlConnectionStringBuilder(connectString)
                Console.WriteLine("Original: " & builder.ConnectionString)
    
                ' Normally, you could simply set the InitialCatalog
                ' property of the SqlConnectionStringBuilder object. This
                ' example uses the default Item property (the C# indexer)
                ' and the "Database" string, simply to demonstrate that
                ' setting the value in this way results in the same
                ' connection string.
                builder("Database") = "AdventureWorks"
                Console.WriteLine("builder.InitialCatalog = " _
                    & builder.InitialCatalog)
                Console.WriteLine("Modified: " & builder.ConnectionString)
    
                Using connection As New SqlConnection(builder.ConnectionString)
                    connection.Open()
                    ' Now use the open connection.
                    Console.WriteLine("Database = " & connection.Database)
                End Using
    
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try
    
            Console.WriteLine("Press any key to finish.")
            Console.ReadLine()
        End Sub
    End Module

    Change the database connectionstring to yours.

    Second, here you need to change Dim destdir As String = "G \ test.bak" to Dim destdir As String = "G: \ test.bak".

    Try to use my code as follows:

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'Dim sqlcon As New SqlConnection
            Dim sqlcmd As New SqlCommand
    
            'destination folder
            Dim destdir As String = "D:\book.bak"
            Using sqlcon = New SqlConnection(My.Settings.RequirementsConnectionString)
                sqlcon.Open()
    
                Dim strQuery = "BACKUP DATABASE AdventureWorks TO DISK = '" & "D:\MyDatabase.bak" & "' with FORMAT"
                sqlcmd = New SqlCommand(strQuery, sqlcon)
                sqlcmd.ExecuteNonQuery()
                MsgBox("successful!")
            End Using
    
        End Sub

    Useful screenshot:


    Hope it be helpful.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 17, 2020 10:10 AM
    Moderator
  • This is my connection string:

     (LocalDB) \ MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Requirements.mdf;Integrated Security=True

    I have tried your example,but same error occured if I change name of database in something else then I get error that database can not been found. So I assume that the name of the database is correct.

    Friday, January 17, 2020 10:50 AM
  • Hello,

    As DA924x suggested, SMO is an excellent choice for backing up a database, no connection string or query required.

    The following code sample came from Microsoft with small modifications by me. In this case my database name is TaxDatabase and the expiration on the backup is November 5, 2020

    Public Sub BackupDatabase()
        'Connect to the local, default instance of SQL Server.  
        Dim srv = New Server()
    
        Dim db As Database
        db = srv.Databases("TaxDatabase")
    
        'Store the current recovery model in a variable.  
        Dim recoverymod As Integer
        recoverymod = db.DatabaseOptions.RecoveryModel
    
        'Define a Backup object variable.   
        Dim bk As New Backup
    
        'Specify the type of backup, the description, the name, and the database to be backed up.  
        bk.Action = BackupActionType.Database
        bk.BackupSetDescription = "Full backup of TaxDatabase"
        bk.BackupSetName = "TaxDatabase Backup"
        bk.Database = "TaxDatabase"
        bk.PercentCompleteNotification = 10
        AddHandler bk.PercentComplete, AddressOf BackupCompleted
    
        'Declare a BackupDeviceItem by supplying the backup device file name in the constructor, 
        'and the type of device is a file.  
        Dim bdi As BackupDeviceItem
        bdi = New BackupDeviceItem("Test_Full_Backup1", DeviceType.File)
    
        'Add the device to the Backup object.  
        bk.Devices.Add(bdi)
    
        'Set the Incremental property to False to specify that this is a full database backup.  
        bk.Incremental = False
    
        'Set the expiration date.  
        Dim backupdate = New Date(2020, 10, 5)
        bk.ExpirationDate = backupdate
    
        'Specify that the log must be truncated after the backup is complete.  
        bk.LogTruncation = BackupTruncateLogType.Truncate
    
        'Run SqlBackup to perform the full database backup on the instance of SQL Server.  
        bk.SqlBackup(srv)
    
    End Sub
    ''' <summary>
    ''' This could also be used to set a progress bar percent done
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub BackupCompleted(sender As Object, e As PercentCompleteEventArgs)
        Console.WriteLine($"{e.Percent}%")
    End Sub
    


    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.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 17, 2020 10:53 AM
    Moderator
  • This is my connection string:

     (LocalDB) \ MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Requirements.mdf;Integrated Security=True. Can I use Requirements.mdf as name for the database in SMO?

    Friday, January 17, 2020 11:59 AM
  • This is my connection string:

     (LocalDB) \ MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Requirements.mdf;Integrated Security=True. Can I use Requirements.mdf as name for the database in SMO?

    Although I don't have an example here are the details.

    The connection string part |DataDirectory| can't be used. The Server object (as per my example) has an overload constructor which you can pass in a ServerConnection which has a overload to pass in a SqlConnection which is where you set the actual connection string.

    The connection string below should work but may need tweaking as I did this without Visual Studio open in Notepad.

    Dim DatabaseName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory,"Requirements.mdf")
    $"(LocalDB)\MSSQLLocalDB;AttachDbFilename={DatabaseName};Integrated Security=True. 

    So with the above and what I presented is a great start, work with it and if you get stuck let us know.

    One last thing, what may prove to be difficult is getting the proper SMO references which is easily done when SSMS is installed, do a add reference in your project and traverse to the folder

    C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies

    Where 130 may be 120, 140 etc. Get the references there.


    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.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 17, 2020 12:22 PM
    Moderator
  • I want the backup to be executed by clicking on a button in the program.

    This is what happens when a software developer with this nonsense  tries to be a DBA. 
    Friday, January 17, 2020 11:17 PM
  • This worked perfectly.You where right, the name of the database was wrong. Your code helped me finding the right name. Thank you very much.
    Saturday, January 18, 2020 2:40 AM
  • Hi,

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, January 18, 2020 2:45 AM
    Moderator