Backups

Hi,

I'm trying to work out how to backup an ASP.NET 2.0 site which uses an SQL 2005 Express database located in the App_Data directory. It seems that the database file cannot be copied while in use so I guess that either leaves taking the site offline or maybe the database can be backed up to another location using a scheduled task?

Any suggestions?

Thanks,

Dale

That is not a prudent way to run a database and I don't understand what you mean by the file because SQL Server Backup does not copy a single file rather data and objects depending on the Backup version you are using, a full Backup copy everything in the database because it can be needed to recreate the database in time of disaster. Your last question is yes but one problem Express does not come with the Agent but there are user created solution. Hope this helps.

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

|||

Thanks for that. In answer to your question, the easiest way I know to backup the site (not just the database) is to copy the entire site using FTP, included in the site is the database file database.mdf under App_Data which is auto-mounted.

Given that ASP.NET 2.0 comes with this database xcopy feature and the App_Data directory it seemed such a nice solution that I didn't consider the backup implications until now. Now I am wondering if anyone has used the App_Data directory for a live site and if so how they manage backups.

Thanks,

Caddre:

That is not a prudent way to run a database and I don't understand what you mean by the file because SQL Server Backup does not copy a single file rather data and objects depending on the Backup version you are using, a full Backup copy everything in the database because it can be needed to recreate the database in time of disaster. Your last question is yes but one problem Express does not come with the Agent but there are user created solution. Hope this helps.

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/08/13/215.aspx

|||

The implication of that in time of disaster is huge let say you sold several thousand dollars worth of goods and your server comes down with Xcopy database you don't have the transaction data. I would not run anything related to transactions and money on a database that does not include the LDF(log data file) and some times the index files also known a the secondary files. The reason is databases some times just get corrupted so you need a full Backup you can restore in five minutes or less so your users will not realize there was a problem with your site. I have posted a FAQ about moving databases that includes Backup and Restore, I think need to look at it so you can schedule a full Backup of your database everyday and delete old Backup as needed. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||

Thanks for the comprehensive reply - I do agree with you although in this case its nothing as important as transaction data. BTW - using the App_Data directory you still get a mdf & ldf file, Its a normal SQL Server 2005 Express database, its just that its physically located in the website and its automatically mounted when access is required. In all other respects its the same.

I was assuming (probably a bad move) that because Microsoft has developed this architecture it was therefore a *good* way to do things. So far it has proved to be *good* but now maybe with the exception of taking a backup. Therefore I was also assuming (another bad move) that there was a way to backup such an architecture.

Caddre:

The implication of that in time of disaster is huge let say you sold several thousand dollars worth of goods and your server comes down with Xcopy database you don't have the transaction data. I would not run anything related to transactions and money on a database that does not include the LDF(log data file) and some times the index files also known a the secondary files. The reason is databases some times just get corrupted so you need a full Backup you can restore in five minutes or less so your users will not realize there was a problem with your site. I have posted a FAQ about moving databases that includes Backup and Restore, I think need to look at it so you can schedule a full Backup of your database everyday and delete old Backup as needed. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||Actually that was created by the Developers division in Microsoft for use with Express because at the time you cannot deploy with Express and now that you can the size still limit what you can run with it. You could change the location of a database before you create it, so if you specifiy that location during creation then you can use the Backup and Restore wizard to Backup your database as databases located in the Microsoft SQL Server folder because the database engines knows where it is located, while the auto attached version is a free standing database called User Instance the SQL Server engine is not aware of. Run a search for change location of database in SQL Server BOL(books online). Hope this helps.|||

Thanks again, so just to sumamrise you're recommending that I convert my 'user instance' database into a 'real' database? Can you clarify, are you also suggesting that I move the physical location of the database from the website to the SQL data directory?

You've been very helpful, I appreciate it.

Caddre:

Actually that was created by the Developers division in Microsoft for use with Express because at the time you cannot deploy with Express and now that you can the size still limit what you can run with it. You could change the location of a database before you create it, so if you specifiy that location during creation then you can use the Backup and Restore wizard to Backup your database as databases located in the Microsoft SQL Server folder because the database engines knows where it is located, while the auto attached version is a free standing database called User Instance the SQL Server engine is not aware of. Run a search for change location of database in SQL Server BOL(books online). Hope this helps.

|||

No I am not recommending you change the location just receate it through restore or reattach it. User instance is a database that is independent of the SQL Server engine with known issues that is covered by the SQL Server team in the blog entry below. Your database located in the App_Data directory is a User instance. What I am suggesting is Backup the User instance and Restore at the same folder location but this time it is not a user instance because when you choose the Restore location SQL Server engine maps it as the physical location of the database. Microsoft have also provided a guide to use Attach to change the location you are use the method that suits you purpose.

http://msdn2.microsoft.com/en-us/library/ms179316.aspx

http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

|||Thank you! Much appreciated!|||

CoolKiwiBloke:

Thank you! Much appreciated!

I am glad I could help.