patch to backup sql 2008 data incrementally

do you mean a script ?

how do you want this backup task to be activated ?   A windows scheduled task ?

if you have the full version of sql server it comes with a management engine. (not in SQLExpress i understand)

otherwise TSQL can be used and run from a windows command shell.

a few pointers.. https://msdn.microsoft.com/en-AU/library/ms188248.aspx

-- Create a full database backup first.
BACKUP DATABASE sales2015
   TO DISK = 'D:\Backup SQL\sales2015.bak'
   WITH INIT,
     NAME = 'MySales backup';
GO

Select allOpen in new window

-- Time elapses.
-- Create a differential database backup, appending the backup
-- to the backup device containing the full database backup.
BACKUP DATABASE MyAdvWorks
   TO DISK = 'D:\Backup SQL\sales2015.bak'
   WITH DIFFERENTIAL,
     NAME = 'MySales backup';
GO

Select allOpen in new window

Adding more you are using SQL server 2008 then you can take compress backup to compact the backup file and always should verify the backup file after backup completion to make sure backup file is valid or not. Here is the script to backup with compression and validate the file . If you are using SQL Express edition you can save this script in .SQL file and schedule in Windows task schedular, or you can create SQL Agent JOB in case SQL edition is Standard or Enterprise.

Declare @Sql nVarchar(500),
		@Name varchar(50),
		@Btype varchar(20)
	Set @Btype='FULL' -- (Change the variable value according to backup type 1. 'Full' for full backup, 2. 'Differential' for differential backup, 3. 'Log' for transaction log backup
	Set @Sql=''
	Set @Name='sales2015'
if @Btype='FULL'
begin
Set @Sql='BACKUP DATABASE [' + @name + '] TO  DISK = N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.bak WITH  INIT ,  NOUNLOAD,  NOSKIP , COMPRESSION, STATS = 10,  NOFORMAT '
Exec(@Sql)
--print @sql
Set @Sql = 'Restore VerifyOnly from Disk= N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+''''
Exec(@Sql)
--PRINT(@Sql)
End
else if @Btype='Differential'
begin
Set @Sql='BACKUP DATABASE [' + @name + '] TO  DISK = N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.bak WITH  INIT ,  NOUNLOAD,  NOSKIP , COMPRESSION,  STATS = 10,  NOFORMAT '+@Btype
Exec(@Sql)
--print (@Sql)
Set @Sql = 'Restore VerifyOnly from Disk= N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+''''
Exec(@Sql)
--PRINT(@Sql)
end
else if @Btype='Log'
begin
Set @Sql='BACKUP Log [' + @name + '] TO  DISK = N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.Trn WITH  INIT ,  NOUNLOAD,  NOSKIP , COMPRESSION, STATS = 10,  NOFORMAT '
Exec(@Sql)
--print (@Sql)
Set @Sql = 'Restore VerifyOnly from Disk= N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+''''
Exec(@Sql)
--PRINT(@Sql)
end

Select allOpen in new window

i am using sql server 2008 full enterprise edition


is that batch or sql script

i think you have SQL expert answers here but if you need multiple backup solution you can use acronis true image echo backup you can backup data full, incremental and differential as you like
sorry SQL Expert just opinion

In SQL server 2008 you can take advantage of compressed backup.
This is sql script and you can schedule it using SQL server Agent Job, just create new job and copy and paste this script into job step.

If you want to schedule it in Windows task schedular, you can copy this script in notepad and save as <BackupFilename>.SQL file

Create a batch file and add below line

sqlcmd -E -S <ServerName> -i D:\<BackupFileName>.sql

schedule the batch file in Windows Task schedular.

But it is good if you will schedule it in SQL agent job.

i have already acronis true image backup but it`s not save because i faced a problem with data and recover backup from acronis and  i found it damage backup it`s not working ;( so i need a real sql backup

@deepakChauhan i wanna to add it as sql job can you give me the script for it
and can you add shrink order on that script or daily shrink not good

is this script incrementally or full

@deepakChauhan i wanna to add it as sql job can you give me the script for it
and can you add shrink order on that script or daily shrink not good

is this script incrementally or full

i got this error

05/31/2015 21:10:35,spid57,Unknown,BackupDiskFile::OpenMedia: Backup device 'D:\Backup SQL\sales2015_FULL20150531' failed to open. Operating system error 2(failed to retrieve text for this error. Reason: 15105).

extention is missing from the backupfile name it is failing while verifying the backup.
I have added file extention in the script in this version. Please replce the old with this one.

Declare @Sql nVarchar(500),
		@Name varchar(50),
		@Btype varchar(20)
	Set @Btype='FuLL' -- (Change the variable value according to backup type 1. 'Full' for full backup, 2. 'Differential' for differential backup, 3. 'Log' for transaction log backup
	Set @Sql=''
	Set @Name='sales2015'
if @Btype='FULL'
begin
Set @Sql='BACKUP DATABASE [' + @name + '] TO  DISK = N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.bak'+''''+' WITH  INIT ,  NOUNLOAD,  NOSKIP , COMPRESSION, STATS = 10,  NOFORMAT '
Exec(@Sql)
--print @sql
Set @Sql = 'Restore VerifyOnly from Disk= N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.bak'''
Exec(@Sql)
--PRINT(@Sql)
End
else if @Btype='Differential'
begin
Set @Sql='BACKUP DATABASE [' + @name + '] TO  DISK = N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.bak'+''''+' WITH  INIT ,  NOUNLOAD,  NOSKIP , COMPRESSION,  STATS = 10,  NOFORMAT '+@Btype
Exec(@Sql)
--print (@Sql)
Set @Sql = 'Restore VerifyOnly from Disk= N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.bak'''
Exec(@Sql)
--PRINT(@Sql)
end
else if @Btype='Log'
begin
Set @Sql='BACKUP Log [' + @name + '] TO  DISK = N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.Trn'+''''+' WITH  INIT ,  NOUNLOAD,  NOSKIP , COMPRESSION, STATS = 10,  NOFORMAT '
Exec(@Sql)
--print (@Sql)
Set @Sql = 'Restore VerifyOnly from Disk= N''D:\Backup SQL\'+@Name+'_'+@Btype+CONVERT(varchar, getdate(), 112)+'.Trn'''
Exec(@Sql)
--PRINT(@Sql)
end

Select allOpen in new window