SQL 2008 R2 Database backup using maintenance plan

  • Hi All

    I have SQL Svr 2008 R2 & OS Windows 2008 R2 Std, I have created a backup maintenance plan to backup to one of our backup drive. Certain databases backups ok but other DB’s fail to backup. When we installed SharePoint 2010 it created databases automatically e.g. “dbname_d0320f4583a34a5a97cde2a91e0338d4”. I copied the TSQL from the maintenance plan to run from the query window it execute with success. Is it a bug on maintenance plan or ?

    The error number: -1073548784

    Error Message: "Invalid device name. The length of the device name provided exceeds supported limit (maximum length is:259). Reissue the BACKUP statement with a valid device name."

    Please Help

    It's better to fail while trying, rather than fail without trying!!!

  • smthembu (8/30/2010)


    Hi All

    I have SQL Svr 2008 R2 & OS Windows 2008 R2 Std, I have created a backup maintenance plan to backup to one of our backup drive. Certain databases backups ok but other DB’s fail to backup. When we installed SharePoint 2010 it created databases automatically e.g. “dbname_d0320f4583a34a5a97cde2a91e0338d4”. I copied the TSQL from the maintenance plan to run from the query window it execute with success. Is it a bug on maintenance plan or ?

    The error number: -1073548784

    Error Message: "Invalid device name. The length of the device name provided exceeds supported limit (maximum length is:259). Reissue the BACKUP statement with a valid device name."

    Please Help

    Are you talking SQLServer proprietary maintenance plan or did you create a job that runs the backup statements ?

    What's the Serverproperty( 'ProductVersion' ) of your instance ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I created a maintenance plan with Full Database backup, Maintenance Cleanup Task & Update Stats.

    Product version is Standard Edition 64bit R2

    It's better to fail while trying, rather than fail without trying!!!

  • You can use SSMS \ maintenance plans \ select your plan and then select the History information.

    You'll see the step that failed and on the bottom of that panel, you can find a "View T-SQL" hyperlink.

    This will show the sql for that step.

    Apparently your full path + filename for the backup file of a certain db is to long.

    btw select Serverproperty( 'ProductVersion' ) should provide info like this: 10.50.1720.0

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi,

    The device name includes the OS path and db backup filename (include timestamp). If you do a count of the characters, it definitely exceeds 259.

    Try to use a short OS path. E.g., D:\db_backup

  • Thanks it worked:-D, but my issue is that I have similar DB's & they backup okay on the default backup folder but only a few that was not going through. Thanks alot

    It's better to fail while trying, rather than fail without trying!!!

  • I had a similar issue using the maintenance module where not all the DBs were backed up. I killed the maintenance module job and used Microsoft Visual Studio to write an SSIS package that Reindex and then backs up all the DBs without any issues. Well, One issue I was using synonyms and the reindex threw them off and to get around that I just made views of the synonym source tables and then relinked the synonym to the view. All works like a charm , full or incremental.

Viewing 7 posts - 1 through 6 (of 6 total)

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