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 ......
Problem in SQL backup code via network
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
2 answers
Sort by: Newest
-
Amr_Ali 136 Reputation points
2020-12-08T18:59:25.347+00:00 -
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.