Need Help Please 1

[nDAa]

Hi,All

How to naming the backup file with backup date in SQL server. The backup command as :

Backup database DATA_W1 to disk = 'F:\backupdata\data.bak' with init skip

I would like data.bak to be data_'sysdate'.bak


Thanks in advance for any advise.

 

[Catadmin]

There are only 2 ways to do this.

1) Use the Database Maint. Planner in Enterprise Manager. It will auto-assign the sysdate to the backup name.

2) Use Dynamic SQL (which may or may not cause speed issues on your machine).

The dynamic SQL way would be as follows:

Declare @BackupDate DateTime,
        @BackupName varchar(50)

Set @BackupDate = GetDate()

Set @BackupName = 'F:\backupdata\data' + @BackupDate + '.bak' 

Backup database DATA_W1 to disk = @BackupName with init skip

Usually, doing your backup with T-SQL doesn't allow the assignment of a system date to the backup name.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???