?
Solved

Backup of SQL SERVER DATABASE FILE FROM C#.NET

Posted on 2011-02-23
5
Medium Priority
?
765 Views
Last Modified: 2012-05-11
I am creating windows application in c#.NET. and I want to take database backup daily and upload to Domain Server with providing FTP Details automatically.
0
Comment
Question by:Manoj Patil
  • 4
5 Comments
 
LVL 19

Accepted Solution

by:
elimesika earned 2000 total points
ID: 34959057
1) create a file named backup.sql with the following content(see code)
2) create a file named backup.cmd with the following content:  (assuming db name is mydb and that your backup directory is C:\DBBackup)

sqlcmd -dmaster -E -Q"exec backup_db N'C:\\DBBackup' ,N'mydb'"

3)  Open Windows Scheduler (Start-All Programs-Accessories-System Tools-Scheduled Tasks)
4)  Double click on "Add scheduled task"
5)  Press Next
6)  Press Browse... button and locate the backup.cmd file
7)  Select the schedule period and press next
8)  select time and other parameters and press next
9) Enter your machine admin user and password for job privileges and click the Finish button.
Backup Files will be created on the folder you have provided using this format:
<db name_yyyy_mm_dd_hh_mm_ss.bak

* you can easily add ftp command to copy the file to the script.

Enjoy




use master
go
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[backup_db]
@path nvarchar(max) ,
@db varchar(128)
as
declare @sql nvarchar(max)
DECLARE @backupTime DATETIME, @backupFile NVARCHAR(512);
set @backupTime = GETDATE()
select @backupFile = @path + '\' + @db + '_' +
replace(replace(CONVERT(NVARCHAR(25), @backupTime, 120), ' ', '_'), ':', '_') + '.bak'
set @sql = 'BACKUP DATABASE ' + @db +' TO DISK = + @backupFile + '
exec sp_executesql @sql

Open in new window

0
 
LVL 19

Author Comment

by:Manoj Patil
ID: 34959875
Thanks elimesika,

Its Helpfull, but is there any way to add scheduler in software, sh when he start software it will automatically start the task.  
0
 
LVL 19

Expert Comment

by:elimesika
ID: 34959895
sure , you can do that with the at command
see
http://technet.microsoft.com/en-us/library/cc755618%28WS.10%29.aspx
0
 
LVL 19

Expert Comment

by:elimesika
ID: 34959911
0
 
LVL 19

Expert Comment

by:elimesika
ID: 34960004
Example
assuming that your script is under c:\ this will run the task each day at 08:00

schtasks /create /tn "DBBackupJob" /tr c:\\backup.cmd /sc daily /st 08:00
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Summary: Persistence is the capability of an application to store the state of objects and recover it when necessary. This article compares the two common types of serialization in aspects of data access, readability, and runtime cost. A ready-to…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question