Solved

Backup of SQL SERVER DATABASE FILE FROM C#.NET

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
System.Security 2 27
User Authentication using Digital Certificate 2 44
Code works but it's slow 28 70
How can i use linq for parsing string datetime to real datetime? 16 44
Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now