Create backup of Database of SQL server from Client and server
-
hi all, i want to create backup of my database (related to SQL server Express 2005) from .NET application. Application is install on SERVER and some Client computer(which is connected to SERVER). I want to create backup from SERVER and client. for backup purpose i use following statement
Dim cmd As SqlCommand cmd = New SqlCommand("Backup Database abc to disk = @BackUpPath") cmd.CommandType = CommandType.Text Dim RootPath1 As String = DestinationPath 'Path for create Backup Dim shellCommand As String = RootPath1 & "abc.bak" cmd.Parameters.AddWithValue("@BackUpPath", shellCommand) ExecuteRestoreCommandQuery(cmd, "master")and this is the procedure ExecuteRestoreCommandQueryPrivate Sub ExecuteRestoreCommandQuery(ByVal comm As SqlCommand, ByVal DatabaseName As String) Dim con1 As SqlConnection = Nothing Try con1 = New SqlConnection("Data Source=" & PublicServerName & "\" & PublicInstanceName & ";Initial Catalog=" & DatabaseName & ";User ID=sa;Password=password") comm.Connection = con1 con1.Open() comm.ExecuteNonQuery() con1.Close() Catch ex As Exception msgbox(ex.Message.ToString) If con1.State = ConnectionState.Open Then con1.Close() End If End Try End Subbut when i run above code then it Gives error "Can not open backup device" path ... Operating System Error 5(Access is denied.). BACKUP DATABASE is terminating abnormally." so how can i create backup from application which is installed on SERVER and CLIENT. -
hi all, i want to create backup of my database (related to SQL server Express 2005) from .NET application. Application is install on SERVER and some Client computer(which is connected to SERVER). I want to create backup from SERVER and client. for backup purpose i use following statement
Dim cmd As SqlCommand cmd = New SqlCommand("Backup Database abc to disk = @BackUpPath") cmd.CommandType = CommandType.Text Dim RootPath1 As String = DestinationPath 'Path for create Backup Dim shellCommand As String = RootPath1 & "abc.bak" cmd.Parameters.AddWithValue("@BackUpPath", shellCommand) ExecuteRestoreCommandQuery(cmd, "master")and this is the procedure ExecuteRestoreCommandQueryPrivate Sub ExecuteRestoreCommandQuery(ByVal comm As SqlCommand, ByVal DatabaseName As String) Dim con1 As SqlConnection = Nothing Try con1 = New SqlConnection("Data Source=" & PublicServerName & "\" & PublicInstanceName & ";Initial Catalog=" & DatabaseName & ";User ID=sa;Password=password") comm.Connection = con1 con1.Open() comm.ExecuteNonQuery() con1.Close() Catch ex As Exception msgbox(ex.Message.ToString) If con1.State = ConnectionState.Open Then con1.Close() End If End Try End Subbut when i run above code then it Gives error "Can not open backup device" path ... Operating System Error 5(Access is denied.). BACKUP DATABASE is terminating abnormally." so how can i create backup from application which is installed on SERVER and CLIENT. -
Are you sure that the account that SQL Server is running under has write permissions to wherever you are trying to back up to? Scott
scottgp wrote:
Are you sure that the account that SQL Server is running under has write permissions to wherever you are trying to back up to?
no idea about this thing. How can i find that whether any account have write permission? BTW i use sa account for create backup
-
scottgp wrote:
Are you sure that the account that SQL Server is running under has write permissions to wherever you are trying to back up to?
no idea about this thing. How can i find that whether any account have write permission? BTW i use sa account for create backup
You may be connecting as sa, but I believe the backup is actually executed by the account that the SQL Server service is executing under. What version of SQL Server are you using? If it's 2005, you can use the SQL Server Configuration Manager to view and change what account it's using, or you can use the Services tool under Administrative Tools. Scott
-
You may be connecting as sa, but I believe the backup is actually executed by the account that the SQL Server service is executing under. What version of SQL Server are you using? If it's 2005, you can use the SQL Server Configuration Manager to view and change what account it's using, or you can use the Services tool under Administrative Tools. Scott
scottgp wrote:
I believe the backup is actually executed by the account that the SQL Server service is executing under
how can i get the information about the account?
scottgp wrote:
If it's 2005, you can use the SQL Server Configuration Manager to view and change what account it's using,
yes it is, It is Built in Account under which network service is selected.but how it affect backup process?
-
scottgp wrote:
I believe the backup is actually executed by the account that the SQL Server service is executing under
how can i get the information about the account?
scottgp wrote:
If it's 2005, you can use the SQL Server Configuration Manager to view and change what account it's using,
yes it is, It is Built in Account under which network service is selected.but how it affect backup process?
-
I guess the question is - does the Network Service account have access to whatever RootPath1/DestinationPath is in your code? Is DestinationPath on a network somewhere? Scott
one more silly question
scottgp wrote:
does the Network Service account have access to whatever RootPath1/DestinationPath is in your code? Is DestinationPath on a network somewhere?
how can i find whether it is have access to given path? however an Access denied message is appear when i select any path except Backup folder of SQL Server
-
one more silly question
scottgp wrote:
does the Network Service account have access to whatever RootPath1/DestinationPath is in your code? Is DestinationPath on a network somewhere?
how can i find whether it is have access to given path? however an Access denied message is appear when i select any path except Backup folder of SQL Server
The normal way that you check permissions in Windows. Right-click on the directory in Windows Explorer to bring up the context menu and select properties. Then select the security tab. You can then add the Network Service account and grant it write permissions. Alternatively, you could run SQL Server under another account - one that already has write permissions. Scott