MSSQL - Backup to external hard drive failed

[Former Member]

Hi gurus,

I am tryin to backup a database using MSSQL2005 Studio to an external hard drive. but i couldn't execute the backup to my external hard drive which is USB. I've checked the error log file in MSsql . I have pasted the following error message below. I done some research and few suggested about the permission or file system could be the issue. I am not sure how to change it. I'm still beginner in MSSQL. Please guide me through to resolve this issue.

BackupDiskFile::OpenMedia: Backup device 'G:\MSSQL\backup' failed to open. Operating system error 2(The system cannot find the file specified.).

2009-11-24 14:39:11.41 spid52 Error: 18204, Severity: 16, State: 1.

[Former Member]

The problem resolved. I've created a new backup type device in SQL manager. It works. The backup is on progress now. Thanks for everyone sharing this with me. Appreciate it.

[Former Member]

Hi Gurus,

The reason i am doing the backup because there is no enough of space in the local drive. That's why i am trying to backup to external drive. The path is created in the external hard drive but i waited almost 2 years but there is no progress in backup screen. It still says executing but no progress. The hard drive is working since i can copy some files into it.Any suggestion.

[Former Member]

Hi Ram,

As quick resolution for your issue, please follow below instructinos.

Get into Enterprise manager

Right click on the database name.

Select Tasks -> Shrink -> Database

Then click OK!

As long term plan, please refer below link that refered when i was settingup backup stragy for my system.

http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx

I hope this will reduce a significatly and gains you free space.

Regards

Sekhar

Edited by: sekhar on Nov 24, 2009 8:25 AM

Edited by: sekhar on Nov 24, 2009 8:27 AM

[Former Member]

Is this file created on your usb device with the mentioned path.

Regards

Yogesh Cheulkar

[Former Member]

Hi Ram,

I have 2 workaround suggestions rather digging myself into finding the reasons for the error posted.

1. try to back/copy any file of SQL database into your external harddisk (this is to ensure your external disk is functioning correct and take the file of SQL based into it)

2. Backup your SQL database into your system first, then copy the same into your external disk.

I hope one of these above will solve your problem.

Regards

Sekhar