Syntax Error with SQL 2005 Sp1

pokhreapokhrea Posts: 19
edited August 7, 2006 12:37AM in SQL Backup Previous Versions
Hi,

I upgraded my DB env to SQL 2005 SP1 with Redgate 4.5.
I have backup files in the foramt [Test][FULL][2006-07-17 220034].BAK.sqb ir [dbname][type][datetime]

I cannot restore this with the current filename and when I rename the backup name to be _ instead of [] it works so can you tell me how to rectify this problem.

Thanks
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Could you pls try restoring the file using the command line interface e.g.

    sqlbackupc -sql "RESTORE DATABASE Test FROM DISK = '[Test][FULL][2006-07-17 220034].BAK.sqb' "

    The extended stored procedure replaces all square brackets with single quotes, to avoid having users having to escape all single quotes, which makes commands easier to read. Unfortunately, it has the side effect of mangling names using naming conventions like yours.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • pokhreapokhrea Posts: 19
    Thats works but it is really frustrating since we do a lot of things from the GUI which wont work either with this format. Since we are running trans log every 10 mins we need a good naming convention so that we can distinguish between various files in case of recovery. Please let me know what might be a workaround for this.

    Thanks
    Ap
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    The issue is with the use of square brackets in your file naming convention. You could create a separate folder for different database and/or backup types e.g.

    In the Options screen, for Backup folder, you could use

    E:\Backups\<DATABASE>\<TYPE>

    If you are concerned about 'finding' the right files during application of transaction logs, are you aware that SQL Backup can restore multiple log files easily?

    E.g. if you have a folder full of trx logs for a bunch of different databases, and they have the following naming convention:

    <DATABASE>_<TYPE>_<DATETIME ...>.sqb

    You can restore multiple logs using somethings like this:

    master..sqlbackup '-sql "RESTORE LOG pubs FROM DISK = [E:\Backups\pubs_log*.sqb] " '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • pokhreapokhrea Posts: 19
    Hi,

    I had an unusal issue with my production server. I have a daily job which backups full, hourly differential and 10 minute tran log. At Sunday 4AM normal diff and log comleted but Redgate had errors as below
    6/08/2006 4:00:09 AM: SQL Backup process ended.

    6/08/2006 4:02:44 AM: Warning 151: MAILTO error: Failed to send mail.
    6/08/2006 4:02:44 AM: Warning 151: Unable to complete command, no storage available

    6/08/2006 4:03:44 AM: Warning 151: MAILTO error: Failed to send mail.
    6/08/2006 4:03:44 AM: Warning 151: Unable to complete command, no storage available

    After that point the SQL services stopped unexpectedly. Iam using version 4.1 Redgate and this is the first time that this has happened. I also went through the previous log backups and it was taking about 3 minutes for a tran backup to complete.
    Is this something that happens in redgate occasionally or can anyone tell me what might have caused it., any preventive mesures to overcome this as this is our production and we wont have choice but to turn this off if happens again.

    Any help is highly regarded.
    Anup
    · Share on Twitter
Sign In or Register to comment.