SQL Express backup

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

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.

-Scott
 
Scott said:
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.
Click to expand...

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! |
********************************************************
 
I will post this in the SQl group and see what they say too.

Are you suggesting that I run the sp_attach_db command once to get the DB's
attached and leave it and then just run my usual script without the
sp_attach_db and sp_detach_db commands?
I believe I did it this way because the site was working fine without the
DB's attached and I didn't wan that to get screwed up. Sense this script is
running at midnight I figured a quick DB attach/detach would be better and
leave the sites operations unchanged. Do you think these repeated
attach/detach operations could be my issue?

-scott

Cowboy (Gregory A. Beamer) said:
Scott said:
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.
Click to expand...

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! |
********************************************************
Click to expand...
 
I will post this in the SQl group and see what they say too.

Are you suggesting that I run the sp_attach_db command once to get the
DB's attached and leave it and then just run my usual script without
the sp_attach_db and sp_detach_db commands?
Click to expand...

Essentially, yes. The main reason for the auto attach to SQL from the
App_Data folder was for ISPs that did not give you a database. This gave
you the option, if they allowed, of configging the file and having it
auto attach to SQL Express.

While it is a nice model, if you have the ability to attach, treat
Express like a full fledged SQL Server (sans some Enterprise bits and
user/size restrictions, it is). This means attach the database to the
server rather than have it auto attach for you.

I did not completely check your script for errors, but here is a backup
I just created in Enterprise Manager. It is simple T_SQL for backing up
to c:\backup with a file name:

BACKUP DATABASE [CrazyCancerTour] TO DISK = N'C:\Backup
\CrazyCancerTour.bak' WITH NOFORMAT, NOINIT, NAME = N'CrazyCancerTour-
Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

You can consult the SQL Books Online (install with Express if you want
to look at the "local" copy) for the syntax of backup, but the above
script will work as long as your database is CrazyCancerTour. ;-)

I believe I did it this way because the site was working fine without
the DB's attached and I didn't wan that to get screwed up.
Click to expand...

The auto attach is a nice addition, when needed. Unfortunately, it is a
bad option if you can attach the db yourself.
Sense this
script is running at midnight I figured a quick DB attach/detach would
be better and leave the sites operations unchanged. Do you think
these repeated attach/detach operations could be my issue?
Click to expand...

All it takes is one person dinging the site while it is "attached" in
"two places" to throw things off and cause you issues. By permanently
attaching, you treat Express as SQL Server and not some alien red headed
stepchild baby. It is much better for your sanity. For those without the
option (ISPs that do not allow them to "create" or "attach" databases,
the folder option works, but it is a bit kludgy). Avoid the kludge. ;-)

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
 
Thanks, I will attach the databases and comment out the attach/detach
portion of my script to see if that solves my issue.

-Scott

Gregory A. Beamer said:
I will post this in the SQl group and see what they say too.

Are you suggesting that I run the sp_attach_db command once to get the
DB's attached and leave it and then just run my usual script without
the sp_attach_db and sp_detach_db commands?
Click to expand...

Essentially, yes. The main reason for the auto attach to SQL from the
App_Data folder was for ISPs that did not give you a database. This gave
you the option, if they allowed, of configging the file and having it
auto attach to SQL Express.

While it is a nice model, if you have the ability to attach, treat
Express like a full fledged SQL Server (sans some Enterprise bits and
user/size restrictions, it is). This means attach the database to the
server rather than have it auto attach for you.

I did not completely check your script for errors, but here is a backup
I just created in Enterprise Manager. It is simple T_SQL for backing up
to c:\backup with a file name:

BACKUP DATABASE [CrazyCancerTour] TO DISK = N'C:\Backup
\CrazyCancerTour.bak' WITH NOFORMAT, NOINIT, NAME = N'CrazyCancerTour-
Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

You can consult the SQL Books Online (install with Express if you want
to look at the "local" copy) for the syntax of backup, but the above
script will work as long as your database is CrazyCancerTour. ;-)

I believe I did it this way because the site was working fine without
the DB's attached and I didn't wan that to get screwed up.
Click to expand...

The auto attach is a nice addition, when needed. Unfortunately, it is a
bad option if you can attach the db yourself.
Sense this
script is running at midnight I figured a quick DB attach/detach would
be better and leave the sites operations unchanged. Do you think
these repeated attach/detach operations could be my issue?
Click to expand...

All it takes is one person dinging the site while it is "attached" in
"two places" to throw things off and cause you issues. By permanently
attaching, you treat Express as SQL Server and not some alien red headed
stepchild baby. It is much better for your sanity. For those without the
option (ISPs that do not allow them to "create" or "attach" databases,
the folder option works, but it is a bit kludgy). Avoid the kludge. ;-)

Peace and Grace,


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************
Click to expand...
 
Back
Top