Backup to network drive using Workgroup/Standard.

I am using SQL Server 2005 Express Edition. I am planning on upgrading to either Workgroup or Standard edition. While working with Express Edition I did find out that this version does not support backup to a network drive.
My question is:

Is it possible to backup directly to a network drive using a Workgroup/ Standard edition of SQL Server rather than backing up to a local drive and then 'copy and paste' to a network drive like the Express Edition.

My other question is :
Given a database limit of 50GB, which one of the editions, Workgroup or Standard, will be appropriate for use.Thank you for your time and patience.
Regards
Berly Sam

You cannot backup to a network share with any edition of SQL Server.

SQL Server Backup will only used locally attached drives, or NAS/SAN LUNs. You can either backup locally, and then move the file to a network share (both tasks can be automated with SQL Agent -not included with SQL Express), or Check out the several third party backup tools. (Idera, Red-Gate, to name a couple.)

Workgroup-Standard-Enterprise, which is 'best'. It depends upon the functionality that you require. With a database of that size, you are definitely between the Standard and Enterprise Editions (in my opinion). You may wish to check out the functionality differences at this site.

|||Thats not exactly true, you cannot see the backup media in the SQL Server dialog as it only displays local fixed drives, but you can specify a UNC share within the dialog textbox or within the script. Depending on the situation you should rather consider using a local /SAN / NAS backup first and then copy it to a network share if needed.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

I consider it a gentle ‘challenge’ for me to give more consideration to why I responded as I did. And I appreciate that.

As I contemplate why I thought so, all I can remember is ever since 6.5, I have always taught to backup locally and xcopy to a net share. I can only imagine that was because of the speed issues of what used to be slower networks, as well as the persistence and permissions issues related to mapped drives.

And I now realize that I have not reconsidered or changed that practice in the intervening years.

I’m glad that you challenged me, now I have tested and affirmed that a backup to a UNC works just fine. But I doubt that I will change my practice of Backing up locally and using xcopy to move the backup file to a network resource -the BACKUP won't fail because of any network issues.

Regards,

- Arnie Rowland