Creating Database Snapshots on SS2005

Hi,
I am trying to programmatically create a snapshot of my database on a
monthly basis. I would like to have one job run one command in SQL to
do this on a monthly basis and I would like each snapshot to have its
own unique name so that I have a snapshot for every month during the
year.

I currently have the name for the snapshot and the snapshot filename
hardcoded for the month... but would like this to be able to give teh
year and date programmatically. I don't know how to code this.. can
anyone help?

CREATE DATABASE QPI_SNAPSHOT_MONTHLY_200807 ON
( NAME = QPI, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
\QPI_SNAPSHOT_MONTHLY_200807.ss' )
AS SNAPSHOT OF QPI;
GO

In the CREATE statement I am having trouble getting this to append the
year and month. Any help is appreciated.

Thanks,
Adam

.

Relevant Pages

  • SQL Server 2005 Transactional Replication Failing
    ... parties using SQL 2005 Developer Edition with SP2. ... records, database size @221MB). ... with a remote client we get ... When the snapshot initialization starts we see the snapshot file ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL Server 2005 Transactional Replication Failing
    ... parties using SQL 2005 Developer Edition with SP2. ... records, database size @221MB). ... We are publishing snapshots to an FTP site and this much is ... When the snapshot initialization starts we see the snapshot file ...
    (microsoft.public.sqlserver.replication)
  • Re: Replication Programming
    ... immediate job to generate a snapshot once, ... > Looking for a SQL Server replication book? ... >> I am given a database and a list of articles in the database to ... >> The publication shows up in the publication folder. ...
    (microsoft.public.sqlserver.replication)
  • Re: Log Shipping: question about secondary dB mode?
    ... You can create snapshots on a mirrored database but not on a log shipped one. ... AS SNAPSHOT OF Northwind ... And last to recover the db you restore the last log file using the WITH RECOVERY option and you should be all set. ... all the backup and restore jobs are proceeding without errors. ...
    (microsoft.public.sqlserver.tools)
  • Re: Prefetch objects failed Error when added more than 2 table as
    ... The database was upgraded from a previous version of SQL Server 2000. ... It seems to me that snapshot only hates the number of tables not stored ... reader, Int32 columnIdx, ObjectparentRow, Boolean forScripting) ... reader, Int32 columnIdx, Int32 columnOffset, ObjectparentRow, Boolean ...
    (microsoft.public.sqlserver.replication)