Backing up a database named update

Question

Post reply

Backing up a database named update

kewlrobd

SSC Enthusiast

Points: 190
More actions
August 23, 2019 at 12:48 pm
Go to Answer
#3673921
I have what I think is a unique situation. I recently deployed an application that uses several databases in SQL Express. One of the databases is named "update". For my SQL Express deployments, I typically script the backups using the following command.
```
BACKUP DATABASE update TO DISK = N'C:\SQLBackups\update.bak' WITH INIT
```
This works great for the rest of the databases but not for the one called update. When I try to run the command I get the error; Incorrect syntax near the keyword update. I have tried putting update in both single and double quotes but I get the same result.
I assume SQL is confused and thinks I am trying to run an update command. Does anyone have any insight or any suggestions on how I can make SQL understand that is a database name? This is a vendor-supplied application so I am unable to change the DB name.
ratbak

Ten Centuries

Points: 1382
More actions
August 23, 2019 at 1:22 pm
Answer
#3673949
Fortunately, it's as simple as wrapping the database name in brackets -- i.e.,
```
BACKUP DATABASE [update] TO DISK = N'C:\SQLBackups\update.bak' WITH INIT
```
Your vendor is at best, uninformed about polite society's rule against using keywords as object names, and perhaps a bit sadistic. 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

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

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

Have you tried putting the database name in square [ ] brackets?

Thanks

kewlrobd SSC Enthusiast Points: 190 More actions · Answer 2

NorthernSoul wrote:

Have you tried putting the database name in square [ ] brackets?
Thanks

ratbak wrote:

Fortunately, it's as simple as wrapping the database name in brackets -- i.e.,
BACKUP DATABASE [update] TO DISK = N'C:\SQLBackups\update.bak' WITH INIT
Your vendor is at best, uninformed about polite society's rule against using keywords as object names, and perhaps a bit sadistic. 🙂

Thank you for this. Putting it in brackets worked perfectly. I believe they were uninformed. The application is actually developed by the New York State Police and provided free to local police departments. It's very unpolished.

Lowell SSC Guru Points: 323518 More actions · Answer 3

that reminds me of one of those bad practice examples i wrote, to show how confusing keywords as object names could be.

IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE name ='SELECT')
  CREATE DATABASE [SELECT];
GO
USE [SELECT];
GO
CREATE SCHEMA "SELECT";
GO
CREATE TABLE [SELECT].[SELECT]([SELECT] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
                      "[SELECT]" VARCHAR(10) DEFAULT 'SELECT');
                      
INSERT INTO [SELECT].[SELECT] DEFAULT VALUES ;

SELECT 
  [SELECT].[SELECT] --TableName.ColumnName
   "SELECT", --Alias
   [SELECT]."[SELECT]" --TableName.ColumnName 
   "SELECT" --Alias
   FROM [SELECT].[SELECT].[SELECT]  ---DatabaseName.SchemaName.TableName
   [SELECT]--Alias 
   WHERE "[SELECT]" --ColumnName 
    = 'SELECT'; --Static String

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!