Syntex Error in Backing UP Database

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)

Syntex Error in Backing UP Database

Author

Topic

Kaleem021
Starting Member

26 Posts

Posted - 2005-04-05 : 09:14:04

Dear All,
Running this code

BACKUP DATABASE ISPI_ERP TO DISK = 'F:\BKUP ISPI_ERP ' + convert(varchar(17),getdate(),113)

raise the error

Line 1: Incorrect syntax near '+'.

Why? as

select 'F:\BKUP ISPI_ERP ' + convert(varchar(17),getdate(),113)

runs well.

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-05 : 09:26:08

You can't run commands like that. You need to do this:

DECLARE @disk VARCHAR(256)

SELECT @disk = 'F:\BKUP ISPI_ERP ' + convert(varchar(17),getdate(),113)

BACKUP DATABASE ISPI_ERP TO DISK = @disk

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kaleem021
Starting Member

26 Posts

Posted - 2005-04-06 : 00:50:30

That works but I could not get the logic.
This code runs without any complain.
backup database ispi_erp to disk = 'F:\ISPI_ERP 06 APR 2005 18:06'
So why I can't run
BACKUP DATABASE ISPI_ERP TO DISK = 'F:\BKUP ISPI_ERP ' + convert(varchar(17),getdate(),113)
as 'F:\ISPI_ERP 06 APR 2005 18:06' is same as 'F:\BKUP ISPI_ERP ' + convert(varchar(17),getdate(),113).

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-06 : 01:03:53

Because you can't build a string in the same operation you execute it in.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kaleem021
Starting Member

26 Posts

Posted - 2005-04-08 : 09:59:57

Still getting errors
Executing this code

Declare @Path as Varchar(256)
Set @Path = 'F:\BKUP ISPI_ERP ' + convert(varchar(17),getdate(),113)
set @path = replace(@path,':','')
BACKUP DATABASE ISPI_ERP To Disk = @Path

raise the error
"Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\F\BKUP ISPI_ERP 08 Apr 2005 1851'. Device error or device off-line. See the SQL Server error log for more details."

If I run

BACKUP DATABASE ISPI_ERP To Disk = 'BKUP ISPI_ERP 08 Apr 2005 1851'

It successfully take backup at "F:\".

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-08 : 11:09:03

Set @Path = 'F:\BKUP ISPI_ERP ' + convert(varchar(17),getdate(),113)
set @path = replace(@path,':','')<<---
you are removing the semicolon from the drive along with the time

try this:

Declare @Path as Varchar(256)
declare @time varchar(20)

set @time = convert(varchar(17),getdate(),113)
set @time = replace(@time,':','')
set @Path = 'F:\BKUP ISPI_ERP ' + @time
BACKUP DATABASE ISPI_ERP To Disk = @Path

Kaleem021
Starting Member

26 Posts

Posted - 2005-04-09 : 00:44:07

Thanks, you know how hard is to trap logical errors.

*****************************************************************************
Myth Breaker
Kaleem021@hotmail.com

Doing Nothing Is Very Hard To Do, You Never Know When You Are Finished.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-11 : 12:04:26

Yes, it can be a pain in the butt. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources