Good day! I'm going to configure the creation of backups through a script in SQL 2008 R2, the script is there, but I have a question. Here is the script

BACKUP DATABASE [mybase1] TO DISK = N'c:\sqlbackup\mybase1.bak' WITH INIT, NOUNLOAD, NAME = N'MyBase1 Backup', NOSKIP, STATS = 10, NOFORMAT;

'c:\sqlbackup\mybase1.bak' is the name of the backup file, how can I make the current date appear before the extension (without a colon) !? For example c:\sqlbackup\mybase270112.bak And if you can, then sample code!

    3 answers 3

    it will be better to change the order

     N'c:sqlbackupmybase'+REPLACE(CONVERT(VARCHAR, getdate(), 2), '.', '')+'.bak' DECLARE @path VARCHAR(260) SET @path = N'd:003sqlbackupmybase'+REPLACE(CONVERT(VARCHAR, getdate(), 2), '.', '') +'.bak' BACKUP DATABASE [Ut_demo] TO DISK = @path WITH INIT, NOUNLOAD, NAME = N'UT_demo Backup', NOSKIP, STATS = 10, NOFORMAT;
    • Got this line: BACKUP DATABASE [Ut_demo] TO DISK = N'd: \ 003 \ sqlbackupmybase '+ REPLACE (CONVERT (VARCHAR, getdate (), 2),'. ',' ') +'. Bak 'WITH INIT , NOUNLOAD, NAME = N'UT_demo Backup ', NOSKIP, STATS = 10, NOFORMAT; - CMS
    • And such errors :! The message 102, level 15, state 1, line 1 Incorrect syntax near the construction "+". The message 319, level 15, state 1, line 1 Incorrect syntax around the keyword "with". If this statement is a generic table expression, an xmlnamespaces clause, or a clause in a change tracking context, the previous statement must be terminated with a semicolon. - CMS
    • the parameter must be a literal or a variable and not a variable - renegator
    • ... not the expression - renegator
    • THANK! Everything is working! - CMS

    And if you dynamically perform?

      Try using Maintenance Plan instead of a self-signed code. The task is not of such a level that it can be solved through development.