Problem in SQL backup code via network

Amr_Ali 136 Reputation points
2020-12-08T10:01:37.033+00:00

Hi all ,
I have 2 machines (The first PC is "MSServer" that hosts a SQL Server Express 2017 and the second PC hosts my VB.net App.) every thing is Okay. But suddenly i realized that my backup code on the second PC is backed up a wrong data, But when i used the same code of backup on the first PC the backup process done well.
Here is my backup code 

Dim sqlConnectionString As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Marasydb;Data Source=MSServer\SQLEXPRESS2017,1433"  
        Using cn As New SqlConnection(sqlConnectionString)  
            cn.Open()  
  
            Using Command As New SqlCommand  
                Command.CommandType = CommandType.Text  
                Command.CommandText = "BACKUP DATABASE Marasydb TO DISK='D:\KMABooking\Backups\MainBackup\Daily\DBFiles\OnClose_" & Format(Now(), "dd_MM_yyyy_hhmmsstt") & ".bak'"     
                Command.Connection = cn  
                Command.ExecuteNonQuery()  
                cn.Close()  
            End Using  
        End Using

Then I searched to find a solution and i found this script 

echo off  
    cls  
    echo -- BACKUP DATABASE --  
    set DATABASENAME=Marasydb  
      
    :: filename format Name-Date (eg Marasydb-2020.07.09.bak)  
    set DATESTAMP=%DATE:~-4%.%DATE:~7,2%.%DATE:~4,2%  
    set BACKUPFILENAME=D:\KMABooking\Backups\MainBackup\Daily\DBFiles\%Marasydb%-%DATESTAMP%.bak  
    set SERVERNAME=MSServer  
    echo.  
      
    sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%BACKUPFILENAME%' WITH INIT , NOUNLOAD , NAME = N'%DATABASENAME% backup', NOSKIP , STATS = 10, NOFORMAT"  
    echo.  
    pause

My inquiries are
1- Why didn't my backup code work correctly on the second PC ?
2- Is the SQL script correct to perform the mission(Backup the database via my App on the second PC from MSServer)?
3- How can i change a DATESTAMP in the SQL script to get a backup file like this
('D:\KMABooking\Backups\MainBackup\Daily\DBFiles\OnClose_" & Format(Now(), "dd_MM_yyyy_hhmmsstt") & ".bak'") ?
Thanks for any suggestions
@Erland Sommarskog

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,706 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,568 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2020-12-08T15:54:19.007+00:00

    Without an explanation of what your problem actually is, an error message or something, it is impossible to guess.

    Backups occur on the server in the SQL Server service context. The backup location must exist and the path available to the SERVER. The SQL Server service account must also have full rights to the destination folder.

  2. Amr_Ali 136 Reputation points
    2020-12-08T18:59:25.347+00:00

    Thanks for fast reply and sorry for late .
    I think that i wrote the connection string like that
    "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Marasydb;Data Source=.\SQLEXPRESS2017,1433" without the server name , So it made me confused but when
    i replaced the "dot" by the server name everything after test works well
    But How didn't give me an error the client PC has no SQL server instance ?
    I don't know , but the problem solved
    Thanks again ......

    0 comments