Friday, January 27, 2012

'-' in database name

Hi All,
We have a couple development database that contain a dash in them, something
like 'DB-TEST'.
I need to truncate the log and I would like to issue the following command:
backup log 'DB-TEST' with truncate_only
but sql-server doesn't like it
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
database names.
This fails both interactively and in a job.
Any help in escaping the - would be greatly appreciated.
TIA,
TimThere are rules for naming of things (aka 'identifiers') if you want to be
able to just type the name as it (without quoting the name in any way). This
is called 'standard identifiers'. By using a hyphen (dash, minus) you do not
follow those rules anymore. I discourage naming objects in this way.
The BACKUP command expects an identifier for the database name, and if you
don't follow the rules for standard identifiers, you have to quote the
database name, You can either use double-quotes or square brackets (non-ANSI
SQL). But not single quotes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Tim" <tfleury@.pacbell.net> wrote in message
news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> We have a couple development database that contain a dash in them,
something
> like 'DB-TEST'.
> I need to truncate the log and I would like to issue the following
command:
> backup log 'DB-TEST' with truncate_only
> but sql-server doesn't like it
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> database names.
> This fails both interactively and in a job.
> Any help in escaping the - would be greatly appreciated.
> TIA,
> Tim
>|||I inherited these database and I have tried to get them renamed but I have
encountered too much resistance.
But, I tried the [ ] brackets and they work like a champ.
Thanks a lot for the help.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usYV8HO7DHA.2712@.tk2msftngp13.phx.gbl...
> There are rules for naming of things (aka 'identifiers') if you want to be
> able to just type the name as it (without quoting the name in any way).
This
> is called 'standard identifiers'. By using a hyphen (dash, minus) you do
not
> follow those rules anymore. I discourage naming objects in this way.
> The BACKUP command expects an identifier for the database name, and if you
> don't follow the rules for standard identifiers, you have to quote the
> database name, You can either use double-quotes or square brackets
(non-ANSI
> SQL). But not single quotes.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Tim" <tfleury@.pacbell.net> wrote in message
> news:uYqAeBO7DHA.2472@.TK2MSFTNGP10.phx.gbl...
> > Hi All,
> >
> > We have a couple development database that contain a dash in them,
> something
> > like 'DB-TEST'.
> >
> > I need to truncate the log and I would like to issue the following
> command:
> >
> > backup log 'DB-TEST' with truncate_only
> >
> > but sql-server doesn't like it
> >
> > Server: Msg 170, Level 15, State 1, Line 1
> > Line 1: Incorrect syntax near 'DB-TEST'. ****works fine for one word
> > database names.
> >
> > This fails both interactively and in a job.
> >
> > Any help in escaping the - would be greatly appreciated.
> >
> > TIA,
> > Tim
> >
> >
>

No comments:

Post a Comment