none
Backup T-SQL in SQL 2008 R2

    Question

  • Hi,

    How to add WITH COMPRESSION  option to below script? I am using SQL Server 2008 R2

    declare @sql as nvarchar(1000)
    set @sql='BACKUP DATABASE ABCDEF TO DISK = ''D:\DBBackups\ABCDEF-'+convert(char(8),getdate(),112)+'.bak'''
    exec sp_executesql @sql

    Friday, May 18, 2012 5:48 PM

Answers

  • DECLARE @filename = 'D:\DBBackups\ABCDEF-' + convert(char(8),getdate(),112) + '.bak'
    BACKUP DATABASE ABCDEF TO DISK = @filename WITH COMPRESSION

    Note that there is rarely any need for dynamic SQL with BACKUP as BACKUP accepts variables for most of its parameters.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Friday, May 18, 2012 6:20 PM
    • Marked as answer by Peja Tao Friday, May 25, 2012 1:40 AM
    Friday, May 18, 2012 6:09 PM
  • Should work then - Developer = Enterprise in terms of features.  Just make sure that your prod environment is actually using enterprise and not standard.  

    As long as the production environment is also SQL 2008 R2 that is no problem, as backup compression is included in Standard Edition of SQL 2008 R2. But if the production environment is SQL 2008 Standard, backup compression is not available.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Monday, May 21, 2012 5:14 AM
    • Marked as answer by Peja Tao Friday, May 25, 2012 1:40 AM
    Friday, May 18, 2012 9:40 PM

All replies

  • set @sql='BACKUP DATABASE ABCDEF TO DISK = ''D:\DBBackups\ABCDEF-'+convert(char(8),getdate(),112)+'.bak'' WITH COMPRESSION'

    Chuck



    Friday, May 18, 2012 5:50 PM
  • Here is the end of my command

    NOUNLOAD, COMPRESSION,  STATS = 10

    BACKUP DATABASE [SiriusSQL_Clean] TO  DISK = N'C:\SQL DB Backups\All DBs\SiriusSQL_Clean.bak' 
    WITH NOFORMAT, INIT,  NAME = N'SiriusSQL_Clean-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
    GO

    So, just add WITH COMPRESSION after the bak, e.g.

    + '.bak'' WITH COMPRESSION'


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Friday, May 18, 2012 5:51 PM
    Moderator
  • Hi,

    Getting below error.

    Message
    Executed as user: NT AUTHORITY\NETWORK SERVICE. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319).  The step failed.

    Friday, May 18, 2012 5:57 PM
  • Can you add a SELECT @sql before you execute it so that the syntax can be seen?

    Also - note that I fixed a couple quote marks in my example since I first posted it


    Chuck

    Friday, May 18, 2012 6:00 PM
  • This worked fine for me:

    declare @SQL nvarchar(max)
    set @SQL = 'BACKUP DATABASE [SiriusSQL_Clean] TO  DISK = N''C:\SQL DB Backups\All DBs\SiriusSQL_Clean.bak'' 
    WITH COMPRESSION'
    
    execute(@SQL)

    What is your SQL Server version? Post output from

    SELECTSERVERPROPERTY('Edition')AS Edition, SERVERPROPERTY('ProductVersion')AS ProductVersion, SERVERPROPERTY('ProductLevel')AS ProductLevel

    Check http://msdn.microsoft.com/en-us/library/bb964719(SQL.105).aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Friday, May 18, 2012 6:03 PM
    Moderator
  • Hi,

    After adding SELECT i got below error,

    Message
    Executed as user: NT AUTHORITY\NETWORK SERVICE. Incorrect syntax near the keyword 'set'. [SQLSTATE 42000] (Error 156)  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319).  The step failed.

    This is the script i am using,

    SELECT set @sql='BACKUP DATABASE ABCDEF TO DISK = ''D:\DBBackups\ABCDEF-'+convert(char(8),getdate(),112)+'.bak'' WITH COMPRESSION'

    Friday, May 18, 2012 6:05 PM
  • You need to remove the SELECT

    set @sql='BACKUP DATABASE ABCDEF TO DISK = ''D:\DBBackups\ABCDEF-'+convert(char(8),getdate(),112)+'.bak'' WITH COMPRESSION'


    Chuck

    Friday, May 18, 2012 6:08 PM
  • DECLARE @filename = 'D:\DBBackups\ABCDEF-' + convert(char(8),getdate(),112) + '.bak'
    BACKUP DATABASE ABCDEF TO DISK = @filename WITH COMPRESSION

    Note that there is rarely any need for dynamic SQL with BACKUP as BACKUP accepts variables for most of its parameters.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Friday, May 18, 2012 6:20 PM
    • Marked as answer by Peja Tao Friday, May 25, 2012 1:40 AM
    Friday, May 18, 2012 6:09 PM
  • What I was asking you to do was:

    set @sql='BACKUP DATABASE ABCDEF TO DISK = ''D:\DBBackups\ABCDEF-'+convert(char(8),getdate(),112)+'.bak'' WITH COMPRESSION'
    Select @Sql


    Chuck

    Friday, May 18, 2012 6:09 PM
  • Edition                                ProductVersion    ProductLevel
    Developer Edition (64-bit)    10.50.1600.1        RTM

    Friday, May 18, 2012 7:33 PM
  • Edition                                ProductVersion    ProductLevel
    Developer Edition (64-bit)    10.50.1600.1        RTM


    Should work then - Developer = Enterprise in terms of features.  Just make sure that your prod environment is actually using enterprise and not standard.   Erlands solution is the best one

    Chuck

    Friday, May 18, 2012 7:37 PM
  • The latest version for SQL Server 2008 R2 is SP1. Make sure to install the Service Pack 1. Also, Erland's solution is correct. Please don't try dynamic SQL here.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Friday, May 18, 2012 7:47 PM
    Moderator
  • Should work then - Developer = Enterprise in terms of features.  Just make sure that your prod environment is actually using enterprise and not standard.  

    As long as the production environment is also SQL 2008 R2 that is no problem, as backup compression is included in Standard Edition of SQL 2008 R2. But if the production environment is SQL 2008 Standard, backup compression is not available.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Peja Tao Monday, May 21, 2012 5:14 AM
    • Marked as answer by Peja Tao Friday, May 25, 2012 1:40 AM
    Friday, May 18, 2012 9:40 PM