Member Avatar for shaanmugavel

I have completed my project with front-end as vb.net 2008 and back-end as SQL server 2005. I have to backup and restore my project in the following extensions:
1. .bak
2. .xls
3. .mdb
Please tell me how can i store the backup project in my own path.

  • 3 Contributors
  • 5 Replies
  • 217 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by samsylvestertty

Dani AI

Generated

Short summary and what’s missing from the thread: backing up to three different file types really means three different workflows. A .bak is a SQL Server backup image (use SQL Server backups / T‑SQL or SMO to create it); .xls is an exported spreadsheet; .mdb is a Microsoft Access file or Access-format export. ’s VB.NET approach shows how to trigger a server backup programmatically; the extra things you should plan for are where the backup runs (server vs client), how you verify the backup, how you restore into a different path/name, and how to export data safely for Excel/Access. (learn.microsoft.com)

Where the file is created and remote-server gotcha: the BACKUP/RESTORE operations execute on the SQL Server host and run under the SQL Server service account — the server resolves the file path. If you specify a local path in your code or SSMS it’s the server’s disk; if you want a network location use a UNC share and give the SQL Server service account (or a suitable domain/GMSA account) write access. This is the usual root cause when backups fail with “Access is denied.” (learn.microsoft.com)

Safe restore checklist + handy T‑SQL (use these to automate or script verification and relocation): always verify the backup before a full restore; query the backup for logical file names, then restore with MOVE to place .mdf/.ldf where you want them. For example:

RESTORE VERIFYONLY FROM DISK = 'C:\backups\MyDB.bak';
RESTORE FILELISTONLY FROM DISK = 'C:\backups\MyDB.bak';

-- use logical names returned by FILELISTONLY
RESTORE DATABASE MyDB_New
  FROM DISK = 'C:\backups\MyDB.bak'
  WITH MOVE 'MyDB_Data' TO 'D:\MSSQL\Data\MyDB_New.mdf',
       MOVE 'MyDB_Log'  TO 'E:\MSSQL\Log\MyDB_New.ldf',
       RECOVERY;

These RESTORE helpers and options are documented in the RESTORE statement reference. (learn.microsoft.com)

Exporting to .xls and .mdb: use the SQL Server Import and Export Wizard, SSIS, or bcp/CSV exports rather than trying to write Excel file binaries yourself. Excel providers and Access (ACE/JET) drivers are bitness-sensitive — prefer CSV or modern .xlsx when possible, or install the Access Database Engine and match the provider bitness to your tool/process. For migrating data into or out of Access consider Microsoft’s tooling (Import/Export Wizard, SSIS, SSMA) rather than raw file-copy. (learn.microsoft.com)

Quick troubleshooting checklist: verify SQL service account permissions on target folder; use RESTORE VERIFYONLY after backup; keep at least one copy off the server (different drive or network/cloud); be aware you cannot restore a backup taken on a newer SQL Server to an older instance. If backups are initiated from a client app, either back up to a server-accessible UNC share or let the server create the .bak and then copy it to the client. (learn.microsoft.com)

Recommended Answers

All 5 Replies

Member Avatar for poojavb

To take back up in .bak extension file
1. Right click on ur database name
2. Tasks -> back up -> Set the backup location and then click on OK.

To restore the .bak extension file
1. Right click on ur database name
2. Tasks ->Restore -> Database
3. Set the database name if the textbox is empty
4. Source for restore -> From database / from device
5. if from device...select the radio button and then click on browse
6. Click on Add -> then select the .bak file from ur correct path
7. Clck OK
8. Select the check box of the appropriate database from the list and click OK.

if u want to add the same in code...then do the following

Try
   If Not Directory.Exists("D:\Backup") Then
      Directory.CreateDirectory("D:\Backup")
   End If
   Dim MySQL_Connection As SqlConnection = New SqlConnection()
   MySQL_Connection.ConnectionString = "Data Source = LocalHost;Initial Catalog = Datasourcename;Integrated Security  = True"
   MySQL_Connection.Open()
   Dim myCommand As SqlCommand
   myCommand = New SqlCommand(" BACKUP DATABASE [Datasourcename] TO  DISK = N'D:\Backup\Datasourcename_full.bak' WITH FORMAT ", MySQL_Connection)
   myCommand.ExecuteNonQuery()
   myCommand = New SqlCommand(" BACKUP DATABASE [Datasourcename] TO  DISK = N'D:\Backup\Datasourcename_diff.bak' WITH DIFFERENTIAL ,FORMAT ", MySQL_Connection)
   myCommand.ExecuteNonQuery()
   MySQL_Connection.Close()
Catch ex As Exception
   Msgbox(ex.message)                      
End Try

Actually I dont know about the other two formats....

Edited by poojavb
Member Avatar for shaanmugavel

i hate this web site because asking more and more questions repeatedly

Member Avatar for shaanmugavel

hello Poojavb thanks dear,
In your code first three line for created folder by static method,but i need dynamically....pls help me....

Member Avatar for poojavb

this database backup will get created on basis of date....u can also use Application.Startup path or any dynamic path that needs to be created....

 Try
    If Not Directory.Exists("D:\" + Date.Today.ToString("dd.MM.yyyy") + "\Backup") Then
       Directory.CreateDirectory("D:\" + Date.Today.ToString("dd.MM.yyyy") + "\Backup")
    End If
    Dim MySQL_Connection As SqlConnection = New SqlConnection()
    MySQL_Connection.ConnectionString = "Data Source = LocalHost;Initial Catalog = Test;Integrated Security = True"
    MySQL_Connection.Open()
    Dim myCommand As SqlCommand
    myCommand = New SqlCommand(" BACKUP DATABASE [Test] TO DISK =N'D:\" + Date.Today.ToString("dd.MM.yyyy") + "\Backup\Test_full.bak' WITH FORMAT ", MySQL_Connection)
    myCommand.ExecuteNonQuery()
    myCommand = New SqlCommand(" BACKUP DATABASE [Test] TO DISK = N'D:\" + Date.Today.ToString("dd.MM.yyyy") + "\Backup\Test_diff.bak' WITH DIFFERENTIAL ,FORMAT ", MySQL_Connection)
    myCommand.ExecuteNonQuery()
    MsgBox("Backup Successfull")
    MySQL_Connection.Close()
Catch ex As Exception
    MsgBox(ex.Message)
End Try
Member Avatar for samsylvestertty

What if the SQL Server is hosted on a remote system? This code is for local system I hope.....
tnx.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.