Re: SQL Express backup
- From: "Cowboy \(Gregory A. Beamer\)" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 18 Aug 2009 18:57:00 -0500
"Scott" <Scott@xxxxxxxxxxxxxx> wrote in message news:yPBim.176358$ZN.173615@xxxxxxxxxxxxxxx
Hello,
I have an interesting situation with my website and I'm not sure if it would be better to ask here or a SQL forum. So I'll start here and see what you all say.
In my asp.NET website I have two SQL express databases. One for logon credentials and another for page content. I cannot backup the MDF files because they are constantly in use. A while back I came accross the following comand line that would do the backup:
-------------Command Line--------------------
c:\sqlcmd -S .\SQLExpress -i E:\ClientAccess\VP_v2.0\SQLBackup\Backup.sql -o c:\sqlcmd -S .\SQLExpress -i E:\ClientAccess\VP_v2.0\SQLBackup\Backup_Log.txt
------------------------------------------------
------------------Bckup.sql --------------------
USE [master]
GO
sp_attach_db "Database", "E:\ClientAccess\VP_v2.0\App_Data\Database.mdf"
GO
BACKUP DATABASE [Database] TO DISK='E:\ClientAccess\VP_v2.0\SQLBackup\Database.bak' WITH FORMAT
GO
sp_detach_db [Database]
GO
sp_attach_db "ASPNETDB", "E:\ClientAccess\VP_v2.0\App_Data\ASPNETDB.MDF"
GO
BACKUP DATABASE [ASPNETDB] TO DISK='E:\ClientAccess\VP_v2.0\SQLBackup\ASPNETDB.bak' WITH FORMAT
GO
sp_detach_db [ASPNETDB]
GO
--------------------------------------------------
This all works well and good but every so often the site stops working because the file permissions on Database.mdf and ASPNETDB.MDF get changed to something that doesn't allow the site to use the DB files and causes a "Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'." error when someone tries to log in. I have enabled autiting on the directory these files are in and I think I've traced it back to my backup script. Can anyone tell me if I'm doing something wrong or perhaps offer a different solution? Maybe someone has seenthis before and has fixed it.
Probably not the "best" group, but I know of few devs who know nothing about SQL Server, and there are plenty of us who could probably serve as DBAs for many companies. If you want a more thorough explanation of the "whys" and "wherefores", I would still ask in the SQL programming group.
I am not understanding the attach of the database. Are you running the mdf file from inside the web site (the auto attach to SQL Express option)? If so, why does it have to be this way? Note I am not saying that it does not, but you can have a database permanently attached to SQL Express and then you can use backup without the attach necessary, which will not interfere with the website.
What you are doing is attaching, which temporarily detaches from the automagic attach in the web site. The backup is then complete and detatched and everything is back to normal. During the time you attach, however, you are creating a problem for the website.
My suggestion would be to permanently attach and then alter your connection string to the Express instance. You can then run a backup script, sans attach, and not interfere with your web clients.
Peace and Grace,
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com
********************************************************
| Think outside the box! |
********************************************************
.
- Follow-Ups:
- Re: SQL Express backup
- From: Scott
- Re: SQL Express backup
- References:
- SQL Express backup
- From: Scott
- SQL Express backup
- Prev by Date: Scan Through All DropDownList on WebForm
- Next by Date: A wonderful opportunity to learn Forex for free
- Previous by thread: SQL Express backup
- Next by thread: Re: SQL Express backup
- Index(es):
Relevant Pages
|
