<div>
Probably the only way to do it is via dynamic SQL. &nbsp;Build the string and then execute it.<br />
<br />
DECLARE @SQL VARCHAR(1000),<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@DatabaseName VARCHAR(100)<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br />
SET @DatabaseName = 'MyDatabase'<br />
<br />
SET @SQL = 'BACKUP DATABASE ' + @DatabaseName + ' &nbsp;TO ' + @DatabaseName + CONVERT(VARCHAR(25), GetDate(), 127) + '.BAK WITH COPY_ONLY'<br />
<br />
EXECUTE (@SQL)
</div>


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)

<div>
<div class="content wysiwyg-content">
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?<br />
<br />
I see the example<br />
BACKUP DATABASE database_name TO &lt;backup_device&gt; … WITH COPY_ONLY … <br />
in the SQL 2005 help. But I don't know how to combine that with automatically putting the date/time in the TSQL.
</div>
</div>


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.

how to create nightly SQL backup copy-only

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.