1

I am using T-SQL to backup my MS SQL 2008 databases:

BACKUP DATABASE @name TO DISK = @fileName

The problem occurs when it comes to the databases with their name length greater than 50 characters like this one:

enter image description here

I then get this error:

enter image description here

I know that the maximum length of a database name is 50 characters, but this database has been automatically created by SharePoint, so I assume it is legal in a way.

It's like if it trunks the database name to the first 50 characters, so of course it cant find the database.

Please help me to take these databases in backup, these are critical. Thanks!

EDIT:

I finally found why I got this error, thanks to mfinni.

This the part of my script that caused problem:

DECLARE @name VARCHAR(50)

I simply changed to:

DECLARE @name VARCHAR(200)

Thanks again!

Jonathan Rioux
  • 1,938
  • 6
  • 33
  • 57
  • 1
    Post or link to the full script - I'm guessing that the @name variable is defined as 50 chars in length? – mfinni May 01 '12 at 19:09
  • 1
    You should use the built in Sharepoint backup utility, rather than using T-SQL. – DanBig May 01 '12 at 19:15
  • 1
    Instead of varchar(200), you should use sysname, which is the data type that is used to store the db name in sys.databases. – Ben Thul May 01 '12 at 20:13

2 Answers2

3

The maximum character length of a database name in SQL Server is 128 characters for SQL Server 2008 R2. Therefore you can backup a database that has more than 50 characters with the BACKUP command in T-SQL (see below for example). You need to make sure your @name variable can hold more than 50 characters.

Example:

CREATE DATABASE MoreThan50characters_abcdefghijklmnopqrstuvwxyz12345;

BACKUP DATABASE MoreThan50characters_abcdefghijklmnopqrstuvwxyz12345
TO DISK = 'MyFile_deleteme.bak';

Gives me this output:


Processed 168 pages for database 'MoreThan50characters_abcdefghijklmnopqrstuvwxyz12345', file 'MoreThan50characters_abcdefghijklmnopqrstuvwxyz12345' on file 3.
Processed 2 pages for database 'MoreThan50characters_abcdefghijklmnopqrstuvwxyz12345', file 'MoreThan50characters_abcdefghijklmnopqrstuvwxyz12345_log' on file 3.
BACKUP DATABASE successfully processed 170 pages in 0.246 seconds (5.390 MB/sec).
0

Posting so that you can mark an answer : I'm guessing that the @name variable is defined as 50 chars in length? (Although now I know that's the case.)

mfinni
  • 36,144
  • 4
  • 53
  • 86