patch to backup sql 2008 data incrementally

i have data base on sql server 2008 named sales2015

i need a patch file to backup this data incrementally on D:\Backup SQL\
MASWORLDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
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

Open 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

Open in new window

Deepak ChauhanSQL Server DBACommented:
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

Open in new window

MASWORLDAuthor Commented:
i am using sql server 2008 full enterprise edition


is that batch or sql script
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

TemodyPickalbatros, IT ManagerCommented:
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
Deepak ChauhanSQL Server DBACommented:
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.
MASWORLDAuthor Commented:
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
MASWORLDAuthor Commented:
@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
MASWORLDAuthor Commented:
@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
Deepak ChauhanSQL Server DBACommented:
@is this script incrementally or full

This script is set to full. For incremental you need to change the Set @Btype='Differential' thats it.

Set @Btype='Differential'    -- (Change the variable value according to backup type 1. 'Full' for full backup, 2. 'Differential' for differential backup, 3. 'Log' for transaction log backup

You can create SQL agent job. Here is the steps.

1. expand the SQL server agent.
2.Right click on Job folder > New JOB > Give the JOB name in Name text Box
3. click on Steps > New > Give the steps name >
4. Paste the code already give to you in the Command text box.
5. Click on Schedule > New > give the schedule name and set the frequency of execution.

SQL-JOB.JPG

 



Do you mean database shrink ?

Database shrinking is not good it affect the database performance. If you are lacking of disk space then it is the last option in case you are not able to add more disk space.

But shrinking is not good either daily or ever.

In addition i would like to make you aware with SQL server mentinance plan. Just expand the Management folder in SQL server management studio and
1. right click on the maintenance
2. Maintenance paln wizard ..... click Next..
3. select the rdio button > Seperate schedule for each task
4. Check mark the whatever you want to do.
5. Set the schedule for the action.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MASWORLDAuthor Commented:
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).
Deepak ChauhanSQL Server DBACommented:
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

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.