Error in Backup

Hai,
In our application, i want to take database backup through
application. So I return code like this, i got a backup in another
folder. But, when i attach that new backup mdf file i am getting error
like this.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
database file. (Microsoft SQL Server, Error: 5171)
My code is
--
Private Sub BackUpSP()
Dim sQuery As String
Dim oServer As SQLDMO.SQLServer
Dim oCmd As New SqlCommand
Dim sCon As String
Dim sSqlCon As SqlConnection
sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
catalog=Master;"
sSqlCon = New SqlConnection(sCon)
sSqlCon.Open()
MsgBox("Master opened")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("Device Created")
sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
oCmd = New SqlCommand(sQuery, sSqlCon)
oCmd.ExecuteNonQuery()
oCmd.Dispose()
MsgBox("backup db")
sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
Call ConnectionExecute(sQuery)
sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
Call ConnectionExecute(sQuery)
MsgBox("Backup Log")
sQuery = "EXEC sp_dropdevice 'mydiskdump'"
Call ConnectionExecute(sQuery)
sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
Call ConnectionExecute(sQuery)
MsgBox("Backup completed successfully!")
End Sub
Please anybody give me the solution for this.
Regards,
Raj.Attach? did you mean RESTORE? Can you issue just RESTORE command?
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||Exactly when is the error occurring?
From the code you posted? If so, which line, and can you use Profiler to cat
ch the actual TSQL
statement executed and try them from a query window?
Or are you trying to attach (sp_attach_db) a database backup file? No can do
, a backup file need to
be restored.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"raju" <ponnurajs@.gmail.com> wrote in message
news:1166502932.510647.68420@.48g2000cwx.googlegroups.com...
> Hai,
> In our application, i want to take database backup through
> application. So I return code like this, i got a backup in another
> folder. But, when i attach that new backup mdf file i am getting error
> like this.
>
> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
> C:\Documents and Settings\user\Desktop\bak\TkmDb.mdf is not a primary
> database file. (Microsoft SQL Server, Error: 5171)
>
> My code is
> --
> Private Sub BackUpSP()
> Dim sQuery As String
> Dim oServer As SQLDMO.SQLServer
> Dim oCmd As New SqlCommand
> Dim sCon As String
>
> Dim sSqlCon As SqlConnection
> sCon = "Data Source=(LOCAL);integrated security=SSPI;initial
> catalog=Master;"
> sSqlCon = New SqlConnection(sCon)
> sSqlCon.Open()
> MsgBox("Master opened")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
>
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("Device Created")
>
> sQuery = "BACKUP DATABASE TkmDb TO mydiskdump with format"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
> MsgBox("backup db")
> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdumpLog', '" &
> Trim(txtPath.Text) & "\TkmDb_Log.ldf" & "'"
> Call ConnectionExecute(sQuery)
> sQuery = "BACKUP LOG TkmDb TO mydiskdumpLog"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup Log")
> sQuery = "EXEC sp_dropdevice 'mydiskdump'"
> Call ConnectionExecute(sQuery)
> sQuery = "EXEC sp_dropdevice 'mydiskdumplog'"
> Call ConnectionExecute(sQuery)
> MsgBox("Backup completed successfully!")
> End Sub
>
> Please anybody give me the solution for this.
> Regards,
> Raj.
>|||<snip>

> sQuery = "EXEC sp_addumpdevice 'disk', 'mydiskdump', '" &
> Trim(txtPath.Text) & "\TkmDb.mdf" & "'"
> oCmd = New SqlCommand(sQuery, sSqlCon)
> oCmd.ExecuteNonQuery()
> oCmd.Dispose()
<snip>
Did you look closely at your own logic? What is the actual filename you are
attempting to use as a dump device? Does the actual filename (and, most
notably, the extension) you are using shed any light on why you might be
experiencing problems? You did this same thing with the log file backup.
And why, exactly, do you need to create a dump device for a one time use?
Just backup the database directly to the file! Using an appropriate
filename and extension, of course.