Backup script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Backup script

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-06-23 : 12:36:01

Afternoon,

I have a backup script that I inherited. It iterates through all the databases and works like a charm unless it finds one with a space in the name, I just got here I din't name them before you yell :)

I tried putting brackets around the dbname and it didnt like it got a syntax error. If someone could point me in the right direction that would be great.

Thanks

Laura


/*
Backup Databases
*/
select @cmd = 'backup database ' + rtrim(@dbname) + ' to disk = ''D:\micros~1\mssql\backup\' + rtrim(@dbname) + '\' + rtrim(@dbname) + '.bak'' with init, noformat, noskip'
  print @cmd
  exec (@cmd)
if @@error <> 0
  begin
    raiserror('Database Backup Failed',16,1)
  end

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2006-06-23 : 12:53:06

create database [my test]

declare @dbname as nvarchar(55)
declare @cmd as varchar(2000)

set @dbname = 'my test'

select @cmd = 'backup database [' + rtrim(@dbname) + '] to disk = ''C:\Temp\' + rtrim(@dbname) + '\' + rtrim(@dbname) + '.bak'' with init, noformat, noskip'
print @cmd
exec (@cmd)
if @@error <> 0
begin
raiserror('Database Backup Failed',16,1)
end

drop database [my test]

set @cmd = 'del "c:\Temp\'+ rtrim(@dbname) + '\' + rtrim(@dbname) + '.bak"'
print @cmd
exec xp_cmdshell @cmd

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-06-23 : 14:06:42

Perfecto!! Thanks so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources