Sunday, February 1, 2009

Back UP of Database on System in vb.net

Imports System.Data.SqlClient
Imports shopping.classConnection
Partial Class bkup

Inherits System.Web.UI.Page
Dim objConnection As New shopping.classConnection()
Dim cmd As New SqlCommand()
Dim adp As New SqlDataAdapter() '("select * from tblCat", objConnection.con)
Dim ds As New Data.DataSet()
Dim dtreader As SqlDataReader
Dim strSql As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Put user code to initialize the page here
If Session("username") = "" Then
Session.Abandon()
Response.Redirect("error.aspx")
End If

End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
Dim cmd As New SqlCommand
cmd.CommandText = "bkup"
cmd.Connection = objConnection.createConnection(Session("site"))
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.ExecuteNonQuery()
cmd.Dispose()
lblresult.Text = "Backup of Database is being taken sucessfully"
Catch ex As Exception
lblresult.Text = "Sorry there is sum problem in taking database backup try again"
End Try
End Sub
'Private Sub btnDownload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDownload.Click
' Dim mystreamwriter As System.IO.TextWriter
' mystreamwriter = Response.Output
' mystreamwriter.WriteLine("This is a test download text file")
' mystreamwriter.Write(Date.Now.ToLongDateString() & " " & Date.Now.ToLongTimeString())
' mystreamwriter.Close()
' Response.AddHeader("content-disposition", "attachmentfilename=download.txt")
'End Sub
End Class




ALTER PROCEDURE bkup
as
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name ='goelco'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

No comments:

Post a Comment