Post reply

Backup DB with . in the title.

  • June 12, 2004 at 4:55 am

    #82988

    Hi,

    I have a job running on my SQL Server to backup all the databases on a nightly basis. The script looks like:

    SET QUOTED_IDENTIFIER off
select getdate() "Start Time"
set nocount on
 
declare @dbname varchar(36),@cmd varchar(255)
declare dbname_cursor cursor
    for select name from master..sysdatabases where name != 'tempdb'
          order by name
 
open  dbname_cursor
fetch dbname_cursor into @dbname
 
while @@fetch_status = 0
begin
if  DATABASEPROPERTYEX(@dbname,'Status') = 'ONLINE'
  begin
        select @cmd ='backup database '+@dbname+' to DISK="D:\Backups\Databases\'+@dbname+'.bak" with init'
           print @cmd
          execute (@cmd)
  end
 
fetch dbname_cursor into @dbname
end
 
close dbname_cursor
deallocate dbname_cursor
select getdate() "End Time"

    It's been running fine for ages but recently I found it was failing. I ran the script in QA and found the reason was some database have a '.' in the name. E.g. customerid.client

    I amended the script to save the file with a '-' instead of a '.' as follows:
    ...
    select @cmd ='backup database '+@dbname+' to DISK="D:\Backups\Databases\'+REPLACE(@dbname,'.','-')+'.bak" with init'
    ...

    Unfotunately it still falls over so it must actually be the backup database command that fails. Is there a way to fix this (escpae the '.' character some how)? I really don't want to have to rename the databases.

    Thanks

    Tom Holder

    Clickcess Ltd

  • June 13, 2004 at 9:01 pm

    #510201

    + '[' + @dbname + ']'

    Derrick Leggett
    Mean Old DBA
    When life gives you a lemon, fire the DBA.

  • June 14, 2004 at 2:45 am

    #510222

    perfect. Obvious really, I should have guessed.

    Thanks very much.

    Tom

Viewing 3 posts - 1 through 2 (of 2 total)

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