Run Sql Backups/Archive DB Backup File / Copy archived file to another directory or FTP Directory

-
Get All available Databases using Select name from sys.databases (in 2005) or Select name from sysdatabases (in 2000).

SqlCommand objCmd = new SqlCommand(@"
BACKUP DATABASE @dbName TO DISK = @backupPath;
exec xp_cmdshell @backupCmd;", objConn);


Backup Database command takes back up for given DB.

Exec xp_cmdshell command runs dos batch files with given command.

@backupCmd paramater has actual batch command, which will do archive the file and move the file to specified destination.

For Archiving I have used Rar command, which is a tiny exe available on Internet.

@backupCmd param value is :

rar a -s -df filename.rar dbbackupname.bak && xcopy /Y filename.rar & del /F/Q filename.rar;

Above command does Three different functions,

1) Archiving using rar
2) xcopy - Moving Archiving file into given destination (Real time it would be FTP Directory)
3) Delete Archived file from Source Directory

&& - Represents If rar command executes successfully, it goes to xcopy.
& - Even if Xcopy fails, del command deletes rar file on source directory.


You can specify Backup command value in Web.Config as follows:

Comments

Venu said…
add key="BackupCmd" value ="rar a -s -df "{1}\{0}.rar" "{1}\{0}" && xcopy /Y "{1}\{0}.rar" "{2}\" & del /F/Q "{1}\{0}.rar""
September 11, 2008 at 4:53 PM
Post a Comment

Popular posts from this blog

Windows Azure Package Build Error: The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters.

-
Image
I often see when packaging Windows Azure project getting "The specified path, file name, or both are too long. The fully qualified file name must be less than 260 characters, and the directory name must be less than 248 characters." error. To fix this issue I had to change Cloud Project User options file add add ServiceOutputDirectory entry to the file. 1) Open your Cloud Project file in Windows Explorer from Visual Studio. 2) Find Cloud Project file in opened directory (*.ccproj file) 3) Open file in Notepad 4) Add c:\azure entry Now your cloud visual studio project user options file looks similar to below: 5) Save file and open your solution again. 6) Package Solution and it should package without above error.
Read more

Resource ID : 1. The request limit for the database is 180 and has been reached.

-
Image
How to find root cause for SQL Azure DB error - " Resource ID : 1. The request limit for the database is 180 and has been reached." I have ran the following two queries to find long running queries that causing connection limit issue. When you run below queries the result set 1 shows all current running query on DB with active session ids. If result set1 has non-zero values in “blocking_session_id” column, you can find same session id in 2 nd result set and see the query text. Based on query text, you can guess which job is running this query & kill/suspend the job/query to release the server resources. select r . session_id , r . blocking_session_id , r . wait_type , r . wait_time , r . wait_resource , r . total_elapsed_time , r . cpu_time , r . reads , r . writes , s . nt_user_name , s . program_name , s . total_elapsed_time from sys . dm_exec_requests r join sys . dm_exec_sessions s on s . session_id = r . session_id SELECT  
Read more

Update Excel Sheet using C#, Where Excel not Installed on Server

-
I am going to show two ways of Excel Sheet Update using C#. Oneway is Where MS Excel got installed on the Server. Steps to Update Excel File: 1) Get the File Name on the Server. 2) Create _Application calss Instance 3) Create _WorkBook calss Instace for _Application 4) Get the available Sheets for _WorkBook Object 5) Update Excel Sheet Columns with Desired Values. 6) Save the Workbook and Quit Application Add the following Referece to the Program: using Microsoft.Office.Interop.Excel; /// /// Takes Newly Generated ECR No and Updated ECR Form with ECR No, Description /// Need Excel Installed On Production Server using UpdateECRForm() method /// /// protected void UpdateECRForm( int _ECRNo) { string xlsFileName = Server.MapPath( ConfigurationManager .AppSettings[ "NewECRFormName" ].ToString() + _ECRNo + ".xlsx" ); _Application xl = new ApplicationClass (); _Workbook wb = xl.Workbooks.Open(xlsFileName, 0, false , 5, Missing .Value, Missing .Value, false , M
Read more