Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
1  
Post or link to the full script - I'm guessing that the @name variable is defined as 50 chars in length? –  mfinni May 1 '12 at 19:09
1  
You should use the built in Sharepoint backup utility, rather than using T-SQL. –  DanBig May 1 '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 1 '12 at 20:13
add comment

2 Answers

up vote 2 down vote accepted

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).
share|improve this answer
    
I already figured it out, thanks anyway :P –  Jonathan Rioux May 1 '12 at 19:37
add comment

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.)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.