Solved

Backup of SQL SERVER DATABASE FILE FROM C#.NET

Posted on 2011-02-23
5
732 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 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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