We help IT Professionals succeed at work.

how to create nightly SQL backup copy-only

GordonPrince
GordonPrince used Ask the Experts™
on
Does anyone have an example of a SQL statement I could use to create a nightly backup of one SQL database, make it a copy-only backup, write it to a file with the date/time as the backup filename?

I see the example
BACKUP DATABASE database_name TO <backup_device> … WITH COPY_ONLY …
in the SQL 2005 help. But I don't know how to combine that with automatically putting the date/time in the TSQL.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Probably the only way to do it is via dynamic SQL.  Build the string and then execute it.

DECLARE @SQL VARCHAR(1000),
            @DatabaseName VARCHAR(100)
            
SET @DatabaseName = 'MyDatabase'

SET @SQL = 'BACKUP DATABASE ' + @DatabaseName + '  TO ' + @DatabaseName + CONVERT(VARCHAR(25), GetDate(), 127) + '.BAK WITH COPY_ONLY'

EXECUTE (@SQL)
I fixed an error in it.  You would have to pass in the DatabaseName and DatabasePath.

DECLARE @SQL VARCHAR(1000),
            @DatabasePath VARCHAR(100),
            @DatabaseName VARCHAR(100)
            
SET @DatabaseName = 'MyDatabase'
SET @DatabasePath = 'C:\'

SET @SQL = 'BACKUP DATABASE ' + @DatabaseName + '  TO DISK ''' + @DatabasePath + @DatabaseName + '-' + CONVERT(VARCHAR(25), GetDate(), 127) + '.BAK'' WITH COPY_ONLY'

EXECUTE (@SQL)

Greg