.mdf not properly shut down.

Each night our SQL server is stopped and .mdf files are copied to a backup
server. When I try to reattach the files it claims that they were not
properly shut down. If I add the transaction file all is well. I was informed
by an expert at Microsoft that this should not be happening. Any ideas why I
am not getting a clean shutdown. The SQL server is stopped at night when
there is not activity. TIA
Greg wrote:
> Each night our SQL server is stopped and .mdf files are copied to a backup
> server. When I try to reattach the files it claims that they were not
> properly shut down. If I add the transaction file all is well. I was informed
> by an expert at Microsoft that this should not be happening. Any ideas why I
> am not getting a clean shutdown. The SQL server is stopped at night when
> there is not activity. TIA
A SQL Server database consists of, at minimum, two files - a data file
(MDF) and a transaction log file (LDF). You don't have a complete
database without all of its files.
Why are you shutting down SQL Server to do backups? The accepted method
for doing backups is to use the BACKUP DATABASE command to do "hot"
backups of the database to a disk file, then backup that disk file to
tape (or other media).
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for your response. I know I have that option. Should I be concerned
about what is happening. I am wondering if I have corruption in my database
or log file. I have run dbcc checkdb and it reports all is well. It just
concerns me that something is happening that an expert says should not be
happening. I will do more research.
"Tibor Karaszi" wrote:

> What somebody say is irrelevant. What matters is what the product documentation say. If you can find
> a statement in the product documentation that this is supposed to work, you can file a bug report
> with MS (and not having to pay for it, since it would be a bug). However, I don't think the
> documentation states anywhere that this is supported. For instance, the documentation say for
> sp_attach_db that the database need to be detached first.
> Why not just do a backup? The BACKUP and RESTORE commands are fully supported and BACKUP is all
> on-line.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
>
|||Are you doing an attach single file? This should work.Try using
sp_attach_single_file_db.
If you still get this error I would question how you are shutting down the
SQL Server? Perhaps you are using the kill command from a command prompt or
task manager.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
> Each night our SQL server is stopped and .mdf files are copied to a backup
> server. When I try to reattach the files it claims that they were not
> properly shut down. If I add the transaction file all is well. I was
> informed
> by an expert at Microsoft that this should not be happening. Any ideas why
> I
> am not getting a clean shutdown. The SQL server is stopped at night when
> there is not activity. TIA
|||I have a batch process that stops the services. I tried the
sp_attach_single_file_db and does not work. It seems that I must have the
transaction file.
"Hilary Cotter" wrote:

> Are you doing an attach single file? This should work.Try using
> sp_attach_single_file_db.
> If you still get this error I would question how you are shutting down the
> SQL Server? Perhaps you are using the kill command from a command prompt or
> task manager.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:839E6EF8-2797-4A79-BF80-A86E9DCCB315@.microsoft.com...
>
>
|||I am stopping now. I will do an online backup from this point on. Thanks.
"Tibor Karaszi" wrote:

> I just have to ask: Why are you pursuing this?
> Just grabbing the database files is *not* a supported backup method.
> Here, for instance, is a quote from BOL 2005, sp_attach_db:
> "The sp_attach_db stored procedure should only be executed on databases that were previously
> detached from the database server by using an explicit sp_detach_db operation or on copied
> databases."
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:96023056-5D2F-4C3A-9E22-BA13C362F90F@.microsoft.com...
>