does backup database need to specify file name?

hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
DB2, when i backup a database, i only need to designate the backup folder an
d
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error
:
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanksYes, you need to specify a file name. It is easy to generate using some TSQL
code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder
and
> it will automatically generate the image file by using the timestamp as th
e
> file name. But on SQL server, only specify the backup folder gives me erro
r :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you|||I only posted an example. Read in Books Online about the CONVERT function an
d use a suitable
formatting code (3:rd parameter).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if
i
> have multiple copies on the same day, what is the best way to distinct the
m?
> thank you|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me erro
r
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you d
o
normally?
thanks|||The character ":" is not allowed in a file name. That is the reason for your
error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120)
+ '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me er
ror
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you
do
> normally?
> thanks