.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> I was informed
> by an expert at Microsoft that this should not be happening.
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...
> 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:
> > I was informed
> > by an expert at Microsoft that this should not be happening.
> 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...
> > 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
>|||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|||> 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.
I wouldn't be concerned. Especially since CHECKDB comes out clean.
> It just
> concerns me that something is happening that an expert says should not be
> happening.
I don't know how this expert is, but if that person claims what you are doing is OK, then perhaps
he/she can comment on what is happening. To me, what you see seems normal. SQL Server has recovery
work to do when it starts up, and without a log file, well... It has been this way since version
1.0.
--
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:81BAA9CC-ECC6-4C42-B8BF-2EBC1F2757DF@.microsoft.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:
>> > I was informed
>> > by an expert at Microsoft that this should not be happening.
>> 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...
>> > 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...
> > 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 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...
>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...
>> > 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 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...
> >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...
> >> > 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
> >>
> >>
> >>
>