locked
Backup WSUS DB in SQL Express RRS feed

  • Question

  • How can I backup SUSDB file weekly with name SUSB-Current Date in SQL Express 2008?

    Schedule task which calls BackupWSUS.SQL file.
    sqlcmd -S LOCALHOST\SQLEXPRESS -i "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\BackupWSUS.sql" -o "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\output.txt"


    BackupWSUS.sql
    BACKUP DATABASE [SUSDB] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\SUSDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'SUSDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
    GO

     

    • Moved by Serge Luca [MVP]MVP Tuesday, August 17, 2010 6:49 PM sql express question,moved from the sharepoint forum (From:SharePoint 2010 - General Questions and Answers)
    Tuesday, August 17, 2010 3:06 PM

Answers

  • Hi bhavin78,

     

    According to your description, you want to replace the name of the backup file with a customized name(in this case, it is SUSB-Current Date). If I have misunderstood, please don’t hesitate to let me know

     

    In this case, we can use dynamic string to achieve it. Something like this:

     

    DECLARE @DATETIME VARCHAR(20)

    DECLARE @EXEC VARCHAR(400)

     

    SET @DATETIME=CONVERT(VARCHAR(8),GETDATE(),112)+REPLACE(LEFT(RIGHT(CONVERT(VARCHAR(30),GETDATE(),121),12),5),':','')

    SET @EXEC = 'BACKUP DATABASE [SUSDB] TO DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\SUSDB-'+@DATETIME+'.bak'' WITH NOFORMAT, NOINIT, NAME = N''SUSDB-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

     

    --PRINT @EXEC

    EXEC (@EXEC)

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    • Marked as answer by bhavin78 Thursday, August 19, 2010 7:02 PM
    Thursday, August 19, 2010 5:51 AM

All replies

  • Hi bhavin78,

     

    According to your description, you want to replace the name of the backup file with a customized name(in this case, it is SUSB-Current Date). If I have misunderstood, please don’t hesitate to let me know

     

    In this case, we can use dynamic string to achieve it. Something like this:

     

    DECLARE @DATETIME VARCHAR(20)

    DECLARE @EXEC VARCHAR(400)

     

    SET @DATETIME=CONVERT(VARCHAR(8),GETDATE(),112)+REPLACE(LEFT(RIGHT(CONVERT(VARCHAR(30),GETDATE(),121),12),5),':','')

    SET @EXEC = 'BACKUP DATABASE [SUSDB] TO DISK = N''C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\SUSDB-'+@DATETIME+'.bak'' WITH NOFORMAT, NOINIT, NAME = N''SUSDB-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

     

    --PRINT @EXEC

    EXEC (@EXEC)

     

    If you have any more questions, please feel free to let me know.

     

    Thanks,

    Yoyo Yu

    • Marked as answer by bhavin78 Thursday, August 19, 2010 7:02 PM
    Thursday, August 19, 2010 5:51 AM
  • Thank's YoYo Yu, your script worked fine.
    Friday, August 20, 2010 1:35 PM