Solved

Backup of SQL SERVER DATABASE FILE FROM C#.NET

Posted on 2011-02-23
5
724 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 19

Expert Comment

by:elimesika
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

20 Experts available now in Live!

Get 1:1 Help Now