TSQL Backup script

Question

TSQL Backup script

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
May 14, 2007 at 3:27 am

#117143

Hi Guys
I am required to backup a database using TSQL. The backup script should have date parameters. The script looks like this:
DECLARE
@BackupDevice VARCHAR(100)
SET
@BackupDevice = 'Display_Backup'+ convert(varchar(11),getdate(),20)
BACKUP
DATABASE DISPLAY
TO
@BackupDevice

I am getting the following error:
Msg 3206, Level 16, State 1, Line 3
No entry in sysdevices for backup device 'Display_Backup2007-05-14 '. Update sysdevices and rerun statement.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.
I am using SQL 2005 and I have added the backup device called 'Display_Backup'.
Can anyone please advise me on what to do.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

the problem with your script is that you are actually specifying a non existant backup device hence the error message. What you probably want to achieve is a database backup name which is being backed up to your Display_Backup device which you have already created .

-------------------

DECLARE @BackupName VARCHAR(100)

SET

@BackupName = N'Display_Backup'+ convert(varchar(11),getdate(),20)

BACKUP DATABASE [DISPLAY] TO

[Display_Backup]

NAME = @BackupName

--------------

Everything you can imagine is real.

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 2

Its giving me an error, "Incorrect syntax near '='."

RichB SSCrazy Eights Points: 9651 More actions · Answer 3

RichB

SSCrazy Eights

Points: 9651

May 14, 2007 at 4:05 am

#706162

<with>

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 4

thanks, just one more problem, its not displayed the date. It should have the name of the backup and the date should be displayed next to the name. At the moment the backup name is 'Display_Backup.bak'

Can you please help

bledu SSChampion Points: 12335 More actions · Answer 5

if you go into Studio Manager > Server Objects > Backup Devices right click on the Display_Backup, goto Properties > Media Contents, you will see that the back up with the date is there.

you are backing up to a device destination, the one which shows the file name(with a date) on the backup name is a backup to a file destination.

Everything you can imagine is real.

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 6

thank you very much....just a question, i want to schedule the backup to run everyday, I want the retention period for the backups to be 2 days...how do I do this?

RichB SSCrazy Eights Points: 9651 More actions · Answer 7

ok, what you need to do if you want each one to be a file in its own right is to a) read books online where it talks about BACKUP and b) do one of the following

add dump device prior to each backup. I dont recommend this.

or backup ... to disk = 'filename' which I think is probably what you are looking for.