How to SQL Server database AutoBackup and Zip or .Rar (.bak) File and Transfer using FTP?

You have to follow these three steps to back up your SQL Server databases by using Windows Task Scheduler:

Step A: Use SQL Server Management Studio Express or Sqlcmd to create the following stored procedure in your master database:

USE [master] 

GO 

/****** Object:  StoredProcedure [dbo].[sp_BackupDatabases] ******/ 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

-- ============================================= 

-- Author: Microsoft 

-- Create date: 2010-02-06

-- Description: Backup Databases for SQLExpress

-- Parameter1: databaseName 

-- Parameter2: backupType F=full, D=differential, L=log

-- Parameter3: backup file location

-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]  

            @databaseName sysname = null,

            @backupType CHAR(1),

            @backupLocation nvarchar(200) 

AS 

       SET NOCOUNT ON; 

           

            DECLARE @DBs TABLE

            (

                  ID int IDENTITY PRIMARY KEY,

                  DBNAME nvarchar(500)

            )

           

             -- Pick out only databases which are online in case ALL databases are chosen to be backed up

             -- If specific database is chosen to be backed up only pick that out from @DBs

            INSERT INTO @DBs (DBNAME)

            SELECT Name FROM master.sys.databases

            where state=0

            AND name=@DatabaseName

            OR @DatabaseName IS NULL

            ORDER BY Name

           

            -- Filter out databases which do not need to backed up

            IF @backupType='F'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')

                  END

            ELSE IF @backupType='D'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE IF @backupType='L'

                  BEGIN

                  DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')

                  END

            ELSE

                  BEGIN

                  RETURN

                  END

           

            -- Declare variables

            DECLARE @BackupName varchar(100)

            DECLARE @BackupFile varchar(100)

            DECLARE @DBNAME varchar(300)

            DECLARE @sqlCommand NVARCHAR(1000) 

        DECLARE @dateTime NVARCHAR(20)

            DECLARE @Loop int                  

                       

            -- Loop through the databases one by one

            SELECT @Loop = min(ID) FROM @DBs

      WHILE @Loop IS NOT NULL

      BEGIN

-- Database Names have to be in [dbname] format since some have - or _ in their name

      SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

-- Set the current date and time n yyyyhhmmss format

      SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

-- Create backup filename in path\filename.extension format for full,diff and log backups

      IF @backupType = 'F'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'D'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'

      ELSE IF @backupType = 'L'

            SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

-- Provide the backup a name for storing in the media

      IF @backupType = 'F'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime

      IF @backupType = 'D'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime

      IF @backupType = 'L'

            SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

-- Generate the dynamic SQL command to be executed

       IF @backupType = 'F' 

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'

                  END

       IF @backupType = 'D'

                  BEGIN

               SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

       IF @backupType = 'L' 

                  BEGIN

               SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+  ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'        

                  END

-- Execute the generated SQL command

       EXEC(@sqlCommand)

-- Goto the next database

SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END

Step B: In a text editor, create a batch file that is named Sqlbackup.bat, and then copy the text from one of the 

following examples into that file, depending on your scenario:

Example1: Full backups of all databases in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

 sqlcmd -S .\EXPRESS –E -Q "EXEC sp_BackupDatabases
 @backupLocation='D:\SQLBackups\', @backupType='F'" 

 Example2: Differential backups of all databases in the local named instance of SQLEXPRESS by using a SQLLogin and its password

// Sqlbackup.bat

sqlcmd -U SQLLogin -P password -S .\SQLEXPRESS -Q "EXEC sp_BackupDatabases  @backupLocation ='D:\SQLBackups', @BackupType=’D’"

 Note: The SQLLogin shouldhave at least the Backup Operator role in SQL Server.

Example 3: Log backups of all databases in local named instance of SQLEXPRESS by using Windows Authentication

 // Sqlbackup.bat

sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\',@backupType='L'"

Example 4: Full backups of the database USERDB in the local named instance of SQLEXPRESS by using Windows Authentication

// Sqlbackup.bat

 sqlcmd -S .\SQLEXPRESS -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQLBackups\', @databaseName=’USERDB’, @backupType='F'"

Similarly, you can make a differential backup of USERDB by pasting in 'D' for the @backupType parameter and a log backup of USERDB by pasting in 'L' for the @backupType parameter.

Step c: Set .Rar or .zip File Name as Current Date


for /F "tokens=1-4 delims=/ " %%A in ('date/t') do (
set DateDay=%%C
set DateMonth=%%B
set DateYear=%%D
)

set CurrentDate=%DateDay%-%DateMonth%-%DateYear%

step D: Create .Rar File


set path="C:\Program Files (x86)\WinRAR\";%path%

winrar.exe a -ep1 C:\backup\%CurrentDate%.rar C:\backup\


Step E:Transfer .Rar File using FTP


echo user Username>> ftpcmd.dat
echo Password>> ftpcmd.dat
echo cd backup_Folder>> ftpcmd.dat
echo put "C:\backup\*.rar">> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat  255.255.255.255


Example:Full Script


DEL /F /S /Q /A "C:\backup\*.bak"
DEL /F /S /Q /A "C:\backup\*.rar"

sqlcmd -S  Server\SQLEXPRESS -U Sa -P server -Q "EXEC sp_BackupDatabases @backupLocation='C:\backup\', @backupType='F'"

@REM ------- BEGIN xpi.bat ----------------
@setlocal
@echo off


for /F "tokens=1-4 delims=/ " %%A in ('date/t') do (
set DateDay=%%C
set DateMonth=%%B
set DateYear=%%D
)

set CurrentDate=%DateDay%-%DateMonth%-%DateYear%

set path="C:\Program Files (x86)\WinRAR\";%path%

winrar.exe a -ep1 C:\backup\%CurrentDate%.rar C:\backup\

echo user Username>> ftpcmd.dat
echo Password>> ftpcmd.dat
echo cd backup_Folder>> ftpcmd.dat
echo put "C:\backup\*.rar">> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat  255.255.255.255


REM ------- END xpi.bat ------------------

Read More

How to Increasing “MaxStringContentLength” for connecting to webservice(WCF) ?

Today I discovered the some configuration impacts from my choice to return the XML document as a string property.  Because I am using a string property to return the XML generated by my service. I had to adjust three properties in the service and client configuration.

MaxBufferSize property - (From MSDN) Gets or sets the maximum size of the buffer to use. For buffered messages this value is the same as MaxReceivedMessageSize. For streamed messages, this value is the maximum size of the SOAP headers, which must be read in buffered mode.

MaxReceivedMessageSize must also match what you put in the MaxBufferSize.  Default is 65536.

MaxStringContentLength - (From MSDN) Gets and sets the maximum string length returned by the reader. Default is 8192.

for example:

System.ServiceModel.BasicHttpBinding ad = new System.ServiceModel.BasicHttpBinding();
            ad.ReaderQuotas.MaxStringContentLength = 2147483647;
            ad.MaxReceivedMessageSize = 2147483647;
            ad.MaxBufferSize = 2147483647;
        

  XYZervices.XYZServiceClient proxy = new XYZServices.XYZServiceClient(ad, new
System.ServiceModel.EndpointAddress("http://localhost:58297/XYZService.svc"));

            var Data = proxy.GetBookingData("ABX", "TTT", 900);

Read More

How To split string in Sql Server?

Split String Function

Sometime we need to split string in sql  server like Comma sparated(,) or colon(:) or dot(.) or (#)

For examples:


declare @Pname Nvarchar(MAX)
SET @Pname ='XZY,TTT,YYY,UUU'

select *
    FROM [dbo].[SplitString] (@Pname,',')

You can Use Following Function to Split Sting in Sql Sever


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[SplitString](@text varchar(8000), @delimiter varchar(1) = ',')
-- This function splits a string of CSV values and creates a table variable with the values.
-- Returns the table variable that it creates
RETURNS @Strings TABLE
(
    position int IDENTITY PRIMARY KEY,
    member_id varchar(8000)
)

AS

BEGIN
    Declare @index int  
    Set @index = -1  
 
    WHILE (LEN(@text) > 0)  
       BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0) AND (LEN(@text) > 0)
               BEGIN
               INSERT INTO @Strings VALUES (@text)
            BREAK
           END
 
        IF (@index > 1)
                 BEGIN
            INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
           END
            ELSE  
            SET @text = RIGHT(@text, (LEN(@text) - @index))
 
       END  
    RETURN
END

Read More

Closing Stock Store Procedure

Friends Today i share With You Closing Stock Report Example


ALTER PROCEDURE [dbo].[ClosingStock]

      -- Add the parameters for the stored procedure here

      @startdate datetime,

      @enddate datetime,

      @than nvarchar(1)='<'

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      select itemname ,opening,inwardqty,outwardqty ,

(isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0)) as closing

from (

select m.Itemname as itemname,sum(isnull(openinward,0)-isnull(openoutward,0)) as opening,

isnull((inwardqty),0)as inwardqty,isnull((outwardqty),0)as outwardqty

from itemmaster m

--------Opening---------------------------------------

left outer join(select id.itemid, isnull(sum(id.qty),0) as openinward  from inwarddetail id

left outer join inward i on i.inwardid = id.inwardid

where  inwarddate < @startdate

 group by itemid) k

on m.itemid = k.itemid left outer join

( select od.itemid, isnull(sum(od.qty),0) as openoutward  from outwarddetail od

left outer join outward i on i.outwardid = od.outwardid

where  outwarddate < @startdate

 group by itemid

) oo

on  m.itemid = oo.itemid

-----------Inward qty---------------------------------------

left outer join(

select itemid, sum(qty) as inwardqty  from inwarddetail id

left outer join inward i on i.inwardid = id.inwardid

where inwarddate >= @startdate  and inwarddate <= @enddate

 group by itemid

) i

 on m.itemid = i.itemid

------------------Outward qty--------------------------------

left outer join( select itemid, sum(qty) as outwardqty  from outwarddetail od

left outer join outward i on i.outwardid = od.outwardid

where outwarddate >= @startdate and outwarddate <= @enddate 

 group by itemid

) o

on  m.itemid = o.itemid

group by m.Itemname,inwardqty,outwardqty ) t

where

(case when @than ='<' Then (isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0))End) <= 0 

or

(case when @than ='>' Then (isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0))End) > 0

End

Read More

CenterWise Stock Store Procedure…

ALTER PROCEDURE [dbo].[CenterWiseStock]

      -- Add the parameters for the stored procedure here

      @startdate datetime,

      @enddate datetime,

      @centerid int,

      @than nvarchar(1)='<'

AS

BEGIN

      select      itemname,

                  opening,

                  inwardqty,

                  InwardTransfer,

                  outwardqty,

                  OutwardTransfer,

(isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-

isnull(outwardqty,0)-isnull(OutwardTransfer,0) ) as closing

From (

select m.Itemname as itemname,

sum(isnull(openinward,0) + isnull(ITopen,0) - isnull(openoutward,0)- isnull(OTopen,0))

 as opening,

isnull((inwardqty),0)as inwardqty,

isnull((TInward),0)as InwardTransfer,

isnull((outwardqty),0)as outwardqty,isnull((TOutward),0)as OutwardTransfer

from itemmaster m

--------Opening---------------------------------------

left outer join(select id.itemid, isnull(sum(id.qty),0) as openinward  from inwarddetail id

left outer join inward i on i.inwardid = id.inwardid

where  inwarddate < @startdate And i.CenterId =@centerid

 group by itemid) k

on m.itemid = k.itemid left outer join

( select od.itemid, isnull(sum(od.qty),0) as openoutward  from outwarddetail od

left outer join outward i on i.outwardid = od.outwardid

where  outwarddate < @startdate And i.CenterId =@centerid

 group by itemid

) oo

on  m.itemid = oo.itemid left outer join

(

select itemid, sum(qty) as OTopen  from Transfer T

left outer join TransferDetail TD on TD.TransterId = T.TransferId

where  Transferdate < @startdate And T.SourceCenterId =@centerid

 group by itemid

) ITO

on  m.itemid = ITO.itemid

left outer join

(

select itemid, sum(qty) as ITopen  from Transfer T

left outer join TransferDetail TD on TD.TransterId = T.TransferId

where  Transferdate < @startdate And T.DestinationCenterId =@centerid

 group by itemid

) OTo

on  m.itemid = OTo.itemid

left outer join

-------------------inwardqty----------------------------

(

select itemid, sum(qty) as inwardqty  from inwarddetail id

left outer join inward i on i.inwardid = id.inwardid

where inwarddate >= @startdate  and inwarddate <= @enddate And i.CenterId =@centerid

group by itemid

) i

 on m.itemid = i.itemid left outer join

--------------------------outwardqty----------------------

( select itemid, sum(qty) as outwardqty  from outwarddetail od

left outer join outward i on i.outwardid = od.outwardid

where outwarddate >= @startdate and outwarddate <= @enddate And i.CenterId =@centerid

 group by itemid

) o

on  m.itemid = o.itemid left outer join

--------------------------Transfer outward------------------

(

select itemid, sum(qty) as TOutward  from Transfer T

left outer join TransferDetail TD on TD.TransterId = T.TransferId

where Transferdate >= @startdate and Transferdate <= @enddate And T.SourceCenterId =@centerid

 group by itemid

) IT

on  m.itemid = IT.itemid left outer join

------------------------Transfer Inward-------------------------

(

select itemid, sum(qty) as TInward  from Transfer T

left outer join TransferDetail TD on TD.TransterId = T.TransferId

where Transferdate >= @startdate and Transferdate <= @enddate And T.DestinationCenterId =@centerid

 group by itemid

) OT

on  m.itemid = OT.itemid

group by m.Itemname,inwardqty,outwardqty,TOutward,TInward )

Temp

where

(case when @than ='<' Then (isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-

isnull(outwardqty,0)-isnull(OutwardTransfer,0) )End) <= 0 

or

(case when @than ='>' Then (isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-

isnull(outwardqty,0)-isnull(OutwardTransfer,0))End) > 0

END

Read More