I have two transaction log files, one on C drive and other on D drive. I want to drop the file from that database that is on C drive. Can anyone help me out on this with complete syntax.
I have taken a down time of one and half hour to accomplish this task
Here is what think
Take a full database backup
Take transaction log backup
Shrinkfile using DBCC with truncateonly option
emptyfile using dbcc or drop file using alter database remove
thanks for your input guys
RomeYou can do it all on EM. But the most important thing is to do a checkpoint to make sure everything is on disk, and then do a log backup. The log file should be empty, and you should be able to delete the log file from EM.
Showing posts with label drive. Show all posts
Showing posts with label drive. Show all posts
Sunday, March 25, 2012
Sunday, February 26, 2012
Drives in a cluster environment
Hi,
I have a SAN and configuring a cluster on SQL 2005. I initially created a Quorum drive when setting up the cluster and now added 4 more drives to the physical node but when I try to install SQL that drive cannot be located.
Do we need to create all the drives when installing the cluster or what is the way to add the drives later on.
Thanks
Anup
I remember that adding a drive for sql requires the drive to be added to the resource group, I assume you have to create a resource group prior to install?|||It's one thing to add them locally - did you add them to the cluster via Cluster Admin as well? There's a procedure for adding drives after the cluster is already configured. Are they seen in CluAdmin? If not, you did not add them properly.
You also didn't mention MS DTC. That needs its own drive now as well in its own group with an IP, name, and DTC resource.
|||Thanks All.
With some troubleshooting and tips from this forums the cluster is up and running with SQL 2005. However I do not understand whi we need a seperate drive for MSDTC cant we share the quorum drive to do this. Can you explain how to size this like perf issues, disks needed etc.
Thanks
|||
For failover clusters, you always need to think in terms of "units of failover". If you put the MSDTC data on the same drive as the quorum, you would need to tie MSDTC to the cluster service. You then create dependancies between MSDTC and the cluster service that shouldn't be there.
In general, you want to think in terms of a service and its associated resources. That bundle needs to be independant from all other bundles on the cluster so that it can move from node to node independant of other bundles (resource groups).
For disk storage, the unit of availability is the physical disk (or LUN in the case of a SAN array). You can't have one partition of a disk mounted to one node and another partition mounted to another node.
So, it's not a matter of capacity or throughput or perf issues, it is an availability issue.
|||Kevin Farlee wrote:
For failover clusters, you always need to think in terms of "units of failover". If you put the MSDTC data on the same drive as the quorum, you would need to tie MSDTC to the cluster service. You then create dependancies between MSDTC and the cluster service that shouldn't be there.
Just to reinforce - this is not a recommended configuration. It only existed with Windows 2000 because of comclust and that has (thankfully) been taken away in W2K3. Always put MS DTC in a separate group.
In the cases where MS DTC is heavily used (such as BTS), it could potentially affect the availability of the quorum disk (i.e. if it gets filled up with DTC log). You don't want to go there.
|||
Assuming you added the new drives as a disk resource in cluster manager, did you also make those new drives a Dependency of SQL Server? If not, SQL Server will not be able to see them.
|||
Thanks. Now I am getting to understand the concepts. However one last question if I have a active/active cluster then do i need to setup 2 MSDTC groups one in each node?
I appreciate all the help throughout .
|||
No, one DTC per cluster. It is shared with everything else in the cluster.
You will need separate dedicated disks and such for your other SQL instance(s) though.
Friday, February 24, 2012
Drive Space Problem
Hi,
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of disk
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe Garcia
Perhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of disk
> space a day. If I reboot the server, the space comes back. I have tried some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia
|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia
|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert
|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> problem.
> w/
> disk
> some
> 1MB in
> as
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.
> robert
>
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of disk
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe Garcia
Perhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of disk
> space a day. If I reboot the server, the space comes back. I have tried some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia
|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia
|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert
|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> problem.
> w/
> disk
> some
> 1MB in
> as
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.
> robert
>
Drive Space Problem
Hi,
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of disk
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe GarciaPerhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of disk
> space a day. If I reboot the server, the space comes back. I have tried some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> > Hi,
> > After searching this forum I haven't found a specific answer for my
> problem.
> > We have a 2003 server used for sql databases. We are running version 8
> w/
> > service pack 3. What is happening is we are losing on average of 10GB of
> disk
> > space a day. If I reboot the server, the space comes back. I have tried
> some
> > of things listed in this forum to no avail, i.e. setting the databases
> > recovery to "simple" instead of "full". The actual .ldf and .mdf files
> > combined are only around 200 megs and the Log directory has less than
> 1MB in
> > it. Is there anything else I can check on the SQL side to eliminate it
> as
> > culprit?
> >
> > Thanks for any help,
> > Joe Garcia
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.
> robert
>
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of disk
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe GarciaPerhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of disk
> space a day. If I reboot the server, the space comes back. I have tried some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> > Hi,
> > After searching this forum I haven't found a specific answer for my
> problem.
> > We have a 2003 server used for sql databases. We are running version 8
> w/
> > service pack 3. What is happening is we are losing on average of 10GB of
> disk
> > space a day. If I reboot the server, the space comes back. I have tried
> some
> > of things listed in this forum to no avail, i.e. setting the databases
> > recovery to "simple" instead of "full". The actual .ldf and .mdf files
> > combined are only around 200 megs and the Log directory has less than
> 1MB in
> > it. Is there anything else I can check on the SQL side to eliminate it
> as
> > culprit?
> >
> > Thanks for any help,
> > Joe Garcia
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.
> robert
>
Drive Space Problem
Hi,
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of dis
k
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe GarciaPerhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my proble
m.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of d
isk
> space a day. If I reboot the server, the space comes back. I have tried so
me
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> problem.
> w/
> disk
> some
> 1MB in
> as
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.
> robert
>
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of dis
k
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe GarciaPerhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my proble
m.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of d
isk
> space a day. If I reboot the server, the space comes back. I have tried so
me
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> problem.
> w/
> disk
> some
> 1MB in
> as
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.
> robert
>
Drive space for db and log backups
Hello all,
Can anyone tell me what is the ideal free space on a disk to allow for db
and log backups on a Full Recovery Model? My db is 4GB, and my log is 2.5GB.
How much free space should I keep to safely run regular backups?
Sincerely,
Gerald
None.
Backups should not be stored on the same machine, much less the same disks,
as your primary data store. Decide on your disaster recovery plan,
calculate how many backups you need to make it bulletproof, and then buy
disk space accordingly. Backup storage space doesn't have to be enterprise
grade stuff, but it should be reasonable reliable and fault tolerant.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
> Hello all,
> Can anyone tell me what is the ideal free space on a disk to allow for db
> and log backups on a Full Recovery Model? My db is 4GB, and my log is
> 2.5GB.
> How much free space should I keep to safely run regular backups?
> --
> Sincerely,
> Gerald
|||Geoff,
Thanks for your response. I do have backup files on a different partition.
What I'm asking is how much disk space should I have to ensure that the db
and log backup processes will take place safely?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> None.
> Backups should not be stored on the same machine, much less the same disks,
> as your primary data store. Decide on your disaster recovery plan,
> calculate how many backups you need to make it bulletproof, and then buy
> disk space accordingly. Backup storage space doesn't have to be enterprise
> grade stuff, but it should be reasonable reliable and fault tolerant.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
>
|||I am unclear. If you are asking how much database and log space are
consumed during a backup operation, the answer is only enough log to hopd
the transaction history recorded during a full backup. Backups take space
equal to the allocated portion of the database in question plus the segment
of the log modified during the backup operation. Backups, in and of
themselves, do not force expansion of the log or the data files. As for how
much space, that depends on how many iterations of your backup you intend to
keep online at any given moment.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||Geoff,
Thanks for the response. What I'm asking is does the backup process use any
space on the same partition as the mdf or the ldf during the backup process
itself, which it would then clean up afterwards? In other words, can the
partition be full or close to full and allow SQL Server to successfully
backup the db and the log?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> I am unclear. If you are asking how much database and log space are
> consumed during a backup operation, the answer is only enough log to hopd
> the transaction history recorded during a full backup. Backups take space
> equal to the allocated portion of the database in question plus the segment
> of the log modified during the backup operation. Backups, in and of
> themselves, do not force expansion of the log or the data files. As for how
> much space, that depends on how many iterations of your backup you intend to
> keep online at any given moment.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
|||Ahh. It becomes clearer.
SQL does not use any file resources except the MDF, LDF, and .BAK files
specified to accomplish a backup.
One exception may be if you use the WITH STANDBY option to take the database
into standby mode. There must be room for the standby file, but you can
specify any valid local storage location for that file.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for the response. What I'm asking is does the backup process use
> any
> space on the same partition as the mdf or the ldf during the backup
> process
> itself, which it would then clean up afterwards? In other words, can the
> partition be full or close to full and allow SQL Server to successfully
> backup the db and the log?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||A different partition on the same physical drive or array does nothing to
alleviate the issue Geoff mentioned. You need to backup to a different
physical drive or drive array to get the real benefits.
Andrew J. Kelly SQL MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||Geoff,
Yes!! That's the question I wanted answered ... and my answer. Thanks for
hanging in there with me.
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> Ahh. It becomes clearer.
> SQL does not use any file resources except the MDF, LDF, and .BAK files
> specified to accomplish a backup.
> One exception may be if you use the WITH STANDBY option to take the database
> into standby mode. There must be room for the standby file, but you can
> specify any valid local storage location for that file.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...
>
|||Andrew,
Thanks for your response. Actually, the question was the one that Geoff
answered finally.
Sincerely,
Gerald
"Andrew J. Kelly" wrote:
> A different partition on the same physical drive or array does nothing to
> alleviate the issue Geoff mentioned. You need to backup to a different
> physical drive or drive array to get the real benefits.
> --
> Andrew J. Kelly SQL MVP
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
>
Can anyone tell me what is the ideal free space on a disk to allow for db
and log backups on a Full Recovery Model? My db is 4GB, and my log is 2.5GB.
How much free space should I keep to safely run regular backups?
Sincerely,
Gerald
None.
Backups should not be stored on the same machine, much less the same disks,
as your primary data store. Decide on your disaster recovery plan,
calculate how many backups you need to make it bulletproof, and then buy
disk space accordingly. Backup storage space doesn't have to be enterprise
grade stuff, but it should be reasonable reliable and fault tolerant.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
> Hello all,
> Can anyone tell me what is the ideal free space on a disk to allow for db
> and log backups on a Full Recovery Model? My db is 4GB, and my log is
> 2.5GB.
> How much free space should I keep to safely run regular backups?
> --
> Sincerely,
> Gerald
|||Geoff,
Thanks for your response. I do have backup files on a different partition.
What I'm asking is how much disk space should I have to ensure that the db
and log backup processes will take place safely?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> None.
> Backups should not be stored on the same machine, much less the same disks,
> as your primary data store. Decide on your disaster recovery plan,
> calculate how many backups you need to make it bulletproof, and then buy
> disk space accordingly. Backup storage space doesn't have to be enterprise
> grade stuff, but it should be reasonable reliable and fault tolerant.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
>
|||I am unclear. If you are asking how much database and log space are
consumed during a backup operation, the answer is only enough log to hopd
the transaction history recorded during a full backup. Backups take space
equal to the allocated portion of the database in question plus the segment
of the log modified during the backup operation. Backups, in and of
themselves, do not force expansion of the log or the data files. As for how
much space, that depends on how many iterations of your backup you intend to
keep online at any given moment.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||Geoff,
Thanks for the response. What I'm asking is does the backup process use any
space on the same partition as the mdf or the ldf during the backup process
itself, which it would then clean up afterwards? In other words, can the
partition be full or close to full and allow SQL Server to successfully
backup the db and the log?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> I am unclear. If you are asking how much database and log space are
> consumed during a backup operation, the answer is only enough log to hopd
> the transaction history recorded during a full backup. Backups take space
> equal to the allocated portion of the database in question plus the segment
> of the log modified during the backup operation. Backups, in and of
> themselves, do not force expansion of the log or the data files. As for how
> much space, that depends on how many iterations of your backup you intend to
> keep online at any given moment.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
|||Ahh. It becomes clearer.
SQL does not use any file resources except the MDF, LDF, and .BAK files
specified to accomplish a backup.
One exception may be if you use the WITH STANDBY option to take the database
into standby mode. There must be room for the standby file, but you can
specify any valid local storage location for that file.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for the response. What I'm asking is does the backup process use
> any
> space on the same partition as the mdf or the ldf during the backup
> process
> itself, which it would then clean up afterwards? In other words, can the
> partition be full or close to full and allow SQL Server to successfully
> backup the db and the log?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||A different partition on the same physical drive or array does nothing to
alleviate the issue Geoff mentioned. You need to backup to a different
physical drive or drive array to get the real benefits.
Andrew J. Kelly SQL MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
|||Geoff,
Yes!! That's the question I wanted answered ... and my answer. Thanks for
hanging in there with me.
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> Ahh. It becomes clearer.
> SQL does not use any file resources except the MDF, LDF, and .BAK files
> specified to accomplish a backup.
> One exception may be if you use the WITH STANDBY option to take the database
> into standby mode. There must be room for the standby file, but you can
> specify any valid local storage location for that file.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...
>
|||Andrew,
Thanks for your response. Actually, the question was the one that Geoff
answered finally.
Sincerely,
Gerald
"Andrew J. Kelly" wrote:
> A different partition on the same physical drive or array does nothing to
> alleviate the issue Geoff mentioned. You need to backup to a different
> physical drive or drive array to get the real benefits.
> --
> Andrew J. Kelly SQL MVP
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
>
Drive space for db and log backups
Hello all,
Can anyone tell me what is the ideal free space on a disk to allow for db
and log backups on a Full Recovery Model? My db is 4GB, and my log is 2.5GB.
How much free space should I keep to safely run regular backups?
Sincerely,
GeraldNone.
Backups should not be stored on the same machine, much less the same disks,
as your primary data store. Decide on your disaster recovery plan,
calculate how many backups you need to make it bulletproof, and then buy
disk space accordingly. Backup storage space doesn't have to be enterprise
grade stuff, but it should be reasonable reliable and fault tolerant.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
> Hello all,
> Can anyone tell me what is the ideal free space on a disk to allow for db
> and log backups on a Full Recovery Model? My db is 4GB, and my log is
> 2.5GB.
> How much free space should I keep to safely run regular backups?
> --
> Sincerely,
> Gerald|||Geoff,
Thanks for your response. I do have backup files on a different partition.
What I'm asking is how much disk space should I have to ensure that the db
and log backup processes will take place safely?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> None.
> Backups should not be stored on the same machine, much less the same disks
,
> as your primary data store. Decide on your disaster recovery plan,
> calculate how many backups you need to make it bulletproof, and then buy
> disk space accordingly. Backup storage space doesn't have to be enterpris
e
> grade stuff, but it should be reasonable reliable and fault tolerant.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
>|||I am unclear. If you are asking how much database and log space are
consumed during a backup operation, the answer is only enough log to hopd
the transaction history recorded during a full backup. Backups take space
equal to the allocated portion of the database in question plus the segment
of the log modified during the backup operation. Backups, in and of
themselves, do not force expansion of the log or the data files. As for how
much space, that depends on how many iterations of your backup you intend to
keep online at any given moment.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||Geoff,
Thanks for the response. What I'm asking is does the backup process use any
space on the same partition as the mdf or the ldf during the backup process
itself, which it would then clean up afterwards? In other words, can the
partition be full or close to full and allow SQL Server to successfully
backup the db and the log?
--
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> I am unclear. If you are asking how much database and log space are
> consumed during a backup operation, the answer is only enough log to hopd
> the transaction history recorded during a full backup. Backups take space
> equal to the allocated portion of the database in question plus the segmen
t
> of the log modified during the backup operation. Backups, in and of
> themselves, do not force expansion of the log or the data files. As for h
ow
> much space, that depends on how many iterations of your backup you intend
to
> keep online at any given moment.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>|||Ahh. It becomes clearer.
SQL does not use any file resources except the MDF, LDF, and .BAK files
specified to accomplish a backup.
One exception may be if you use the WITH STANDBY option to take the database
into standby mode. There must be room for the standby file, but you can
specify any valid local storage location for that file.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for the response. What I'm asking is does the backup process use
> any
> space on the same partition as the mdf or the ldf during the backup
> process
> itself, which it would then clean up afterwards? In other words, can the
> partition be full or close to full and allow SQL Server to successfully
> backup the db and the log?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||A different partition on the same physical drive or array does nothing to
alleviate the issue Geoff mentioned. You need to backup to a different
physical drive or drive array to get the real benefits.
Andrew J. Kelly SQL MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||Geoff,
Yes!! That's the question I wanted answered ... and my answer. Thanks for
hanging in there with me.
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> Ahh. It becomes clearer.
> SQL does not use any file resources except the MDF, LDF, and .BAK files
> specified to accomplish a backup.
> One exception may be if you use the WITH STANDBY option to take the databa
se
> into standby mode. There must be room for the standby file, but you can
> specify any valid local storage location for that file.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...
>|||Andrew,
Thanks for your response. Actually, the question was the one that Geoff
answered finally.
Sincerely,
Gerald
"Andrew J. Kelly" wrote:
> A different partition on the same physical drive or array does nothing to
> alleviate the issue Geoff mentioned. You need to backup to a different
> physical drive or drive array to get the real benefits.
> --
> Andrew J. Kelly SQL MVP
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
>
Can anyone tell me what is the ideal free space on a disk to allow for db
and log backups on a Full Recovery Model? My db is 4GB, and my log is 2.5GB.
How much free space should I keep to safely run regular backups?
Sincerely,
GeraldNone.
Backups should not be stored on the same machine, much less the same disks,
as your primary data store. Decide on your disaster recovery plan,
calculate how many backups you need to make it bulletproof, and then buy
disk space accordingly. Backup storage space doesn't have to be enterprise
grade stuff, but it should be reasonable reliable and fault tolerant.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
> Hello all,
> Can anyone tell me what is the ideal free space on a disk to allow for db
> and log backups on a Full Recovery Model? My db is 4GB, and my log is
> 2.5GB.
> How much free space should I keep to safely run regular backups?
> --
> Sincerely,
> Gerald|||Geoff,
Thanks for your response. I do have backup files on a different partition.
What I'm asking is how much disk space should I have to ensure that the db
and log backup processes will take place safely?
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> None.
> Backups should not be stored on the same machine, much less the same disks
,
> as your primary data store. Decide on your disaster recovery plan,
> calculate how many backups you need to make it bulletproof, and then buy
> disk space accordingly. Backup storage space doesn't have to be enterpris
e
> grade stuff, but it should be reasonable reliable and fault tolerant.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:351EC7E4-1142-48C8-BEB0-4677D22FA78E@.microsoft.com...
>|||I am unclear. If you are asking how much database and log space are
consumed during a backup operation, the answer is only enough log to hopd
the transaction history recorded during a full backup. Backups take space
equal to the allocated portion of the database in question plus the segment
of the log modified during the backup operation. Backups, in and of
themselves, do not force expansion of the log or the data files. As for how
much space, that depends on how many iterations of your backup you intend to
keep online at any given moment.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||Geoff,
Thanks for the response. What I'm asking is does the backup process use any
space on the same partition as the mdf or the ldf during the backup process
itself, which it would then clean up afterwards? In other words, can the
partition be full or close to full and allow SQL Server to successfully
backup the db and the log?
--
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> I am unclear. If you are asking how much database and log space are
> consumed during a backup operation, the answer is only enough log to hopd
> the transaction history recorded during a full backup. Backups take space
> equal to the allocated portion of the database in question plus the segmen
t
> of the log modified during the backup operation. Backups, in and of
> themselves, do not force expansion of the log or the data files. As for h
ow
> much space, that depends on how many iterations of your backup you intend
to
> keep online at any given moment.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>|||Ahh. It becomes clearer.
SQL does not use any file resources except the MDF, LDF, and .BAK files
specified to accomplish a backup.
One exception may be if you use the WITH STANDBY option to take the database
into standby mode. There must be room for the standby file, but you can
specify any valid local storage location for that file.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for the response. What I'm asking is does the backup process use
> any
> space on the same partition as the mdf or the ldf during the backup
> process
> itself, which it would then clean up afterwards? In other words, can the
> partition be full or close to full and allow SQL Server to successfully
> backup the db and the log?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||A different partition on the same physical drive or array does nothing to
alleviate the issue Geoff mentioned. You need to backup to a different
physical drive or drive array to get the real benefits.
Andrew J. Kelly SQL MVP
"Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in message
news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...[vbcol=seagreen]
> Geoff,
> Thanks for your response. I do have backup files on a different partition.
> What I'm asking is how much disk space should I have to ensure that the db
> and log backup processes will take place safely?
> --
> Sincerely,
> Gerald
>
> "Geoff N. Hiten" wrote:
>|||Geoff,
Yes!! That's the question I wanted answered ... and my answer. Thanks for
hanging in there with me.
Sincerely,
Gerald
"Geoff N. Hiten" wrote:
> Ahh. It becomes clearer.
> SQL does not use any file resources except the MDF, LDF, and .BAK files
> specified to accomplish a backup.
> One exception may be if you use the WITH STANDBY option to take the databa
se
> into standby mode. There must be room for the standby file, but you can
> specify any valid local storage location for that file.
>
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:F679B7FB-F8D3-48B2-9EB9-30460D9A8401@.microsoft.com...
>|||Andrew,
Thanks for your response. Actually, the question was the one that Geoff
answered finally.
Sincerely,
Gerald
"Andrew J. Kelly" wrote:
> A different partition on the same physical drive or array does nothing to
> alleviate the issue Geoff mentioned. You need to backup to a different
> physical drive or drive array to get the real benefits.
> --
> Andrew J. Kelly SQL MVP
> "Gerald Hopkins" <GeraldHopkins@.discussions.microsoft.com> wrote in messag
e
> news:59C902AF-BF2E-4F9F-BC69-2FB7EAAD6558@.microsoft.com...
>
>
Drive out of space
Hello,
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
Thanks
Hi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.c om...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks
|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.c om...
>
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
Thanks
Hi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.c om...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks
|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.c om...
>
Drive out of space
Hello,
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
ThanksHi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.com...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.com...
>
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
ThanksHi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.com...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.com...
>
Drive out of space
Hello,
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
ThanksHi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.com...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.com...
> > Hello,
> >
> > The drive my DB is on only has 10 % space available. Would the Shrink
> > option free up some space for this DB?
> >
> >
> > Thanks
>
The drive my DB is on only has 10 % space available. Would the Shrink
option free up some space for this DB?
ThanksHi,
Check the usage of database using
use dbname
go
sp_spaceused @.updateusage='true'
For LDF file see
dbcc sqlperf(logspace)
If either of MDF or LDF have space you can backup the database and shrink
the file
If LDF can be shrunk then:-
1. Backup the transaction log or truncate the transaction log
2. dbcc shrinkfile
Thanks
Hari
MCDBA
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407090906.4da4e71e@.posting.google.com...
> Hello,
> The drive my DB is on only has 10 % space available. Would the Shrink
> option free up some space for this DB?
>
> Thanks|||Beware that using the updateusage=true option will run DBCC UPDATEUSAGE on
all the tables using table S locks, effectively making them read-only during
the operation.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVYPLfdZEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Check the usage of database using
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For LDF file see
> dbcc sqlperf(logspace)
> If either of MDF or LDF have space you can backup the database and shrink
> the file
> If LDF can be shrunk then:-
> 1. Backup the transaction log or truncate the transaction log
> 2. dbcc shrinkfile
> Thanks
> Hari
> MCDBA
> "Craig Alexander" <craig@.itas.net> wrote in message
> news:2582929c.0407090906.4da4e71e@.posting.google.com...
> > Hello,
> >
> > The drive my DB is on only has 10 % space available. Would the Shrink
> > option free up some space for this DB?
> >
> >
> > Thanks
>
drive not visible in the SQL server
Hi all,
Here is my problem:
I've having trouble to make my second logical drive available for database
under SQL server 2005 (Other words: I cannot move database to my second
database drive because I cannot see it in SQL server 2005)
Config:
- Cluster Acitve/passive
- 2 nodes (Win 2003 server SE SP2)
- 4 drives configured:
* drive 1 for quorum
* drive 2 for fro tempdb
* drive 3 for my database (old) and logs
* drive 4 for my database (where I want to move to)
- SQL server 2005 SP1
This new drive is properly configured (or so I think) in the cluster admin,
as a physical drive part of my SQLserver Data group resource (as my old drive
is) and is showing up active in my resource list.
I can also browse it as normal from Windows
When trying to move the database in SQL server 2005, I can only browse my
current database drive and I cannot see the new one.
Any ideas what am I missing ?
Thanks a lot for your help.
Julien
You have to make the SQL service dependant on the new drive resource. This
will require taking SQL offline when making the change.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Julien" <Julien@.discussions.microsoft.com> wrote in message
news:84B592EF-FD04-447B-8456-25ABE59E6FF1@.microsoft.com...
> Hi all,
> Here is my problem:
> I've having trouble to make my second logical drive available for database
> under SQL server 2005 (Other words: I cannot move database to my second
> database drive because I cannot see it in SQL server 2005)
> Config:
> - Cluster Acitve/passive
> - 2 nodes (Win 2003 server SE SP2)
> - 4 drives configured:
> * drive 1 for quorum
> * drive 2 for fro tempdb
> * drive 3 for my database (old) and logs
> * drive 4 for my database (where I want to move to)
> - SQL server 2005 SP1
>
> This new drive is properly configured (or so I think) in the cluster
> admin,
> as a physical drive part of my SQLserver Data group resource (as my old
> drive
> is) and is showing up active in my resource list.
> I can also browse it as normal from Windows
> When trying to move the database in SQL server 2005, I can only browse my
> current database drive and I cannot see the new one.
> Any ideas what am I missing ?
> Thanks a lot for your help.
> Julien
>
|||Hi Geoff,
Thanks a lot for your reply.
It's definitely making sens to me but I'm not sure how to do this.
Could you please l explain a bit further?
Thanks,
Julien
"Geoff N. Hiten" wrote:
> You have to make the SQL service dependant on the new drive resource. This
> will require taking SQL offline when making the change.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Julien" <Julien@.discussions.microsoft.com> wrote in message
> news:84B592EF-FD04-447B-8456-25ABE59E6FF1@.microsoft.com...
>
|||After a bit of diggin, I found and corrected the options.
Located in the SQLserver ressource of the cluster, once put offline, just
added the correct dependencies.
Thanks a lot for the Geoff.
Julien
"Julien" wrote:
[vbcol=seagreen]
> Hi Geoff,
> Thanks a lot for your reply.
> It's definitely making sens to me but I'm not sure how to do this.
> Could you please l explain a bit further?
> Thanks,
> Julien
> "Geoff N. Hiten" wrote:
Here is my problem:
I've having trouble to make my second logical drive available for database
under SQL server 2005 (Other words: I cannot move database to my second
database drive because I cannot see it in SQL server 2005)
Config:
- Cluster Acitve/passive
- 2 nodes (Win 2003 server SE SP2)
- 4 drives configured:
* drive 1 for quorum
* drive 2 for fro tempdb
* drive 3 for my database (old) and logs
* drive 4 for my database (where I want to move to)
- SQL server 2005 SP1
This new drive is properly configured (or so I think) in the cluster admin,
as a physical drive part of my SQLserver Data group resource (as my old drive
is) and is showing up active in my resource list.
I can also browse it as normal from Windows
When trying to move the database in SQL server 2005, I can only browse my
current database drive and I cannot see the new one.
Any ideas what am I missing ?
Thanks a lot for your help.
Julien
You have to make the SQL service dependant on the new drive resource. This
will require taking SQL offline when making the change.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Julien" <Julien@.discussions.microsoft.com> wrote in message
news:84B592EF-FD04-447B-8456-25ABE59E6FF1@.microsoft.com...
> Hi all,
> Here is my problem:
> I've having trouble to make my second logical drive available for database
> under SQL server 2005 (Other words: I cannot move database to my second
> database drive because I cannot see it in SQL server 2005)
> Config:
> - Cluster Acitve/passive
> - 2 nodes (Win 2003 server SE SP2)
> - 4 drives configured:
> * drive 1 for quorum
> * drive 2 for fro tempdb
> * drive 3 for my database (old) and logs
> * drive 4 for my database (where I want to move to)
> - SQL server 2005 SP1
>
> This new drive is properly configured (or so I think) in the cluster
> admin,
> as a physical drive part of my SQLserver Data group resource (as my old
> drive
> is) and is showing up active in my resource list.
> I can also browse it as normal from Windows
> When trying to move the database in SQL server 2005, I can only browse my
> current database drive and I cannot see the new one.
> Any ideas what am I missing ?
> Thanks a lot for your help.
> Julien
>
|||Hi Geoff,
Thanks a lot for your reply.
It's definitely making sens to me but I'm not sure how to do this.
Could you please l explain a bit further?
Thanks,
Julien
"Geoff N. Hiten" wrote:
> You have to make the SQL service dependant on the new drive resource. This
> will require taking SQL offline when making the change.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Julien" <Julien@.discussions.microsoft.com> wrote in message
> news:84B592EF-FD04-447B-8456-25ABE59E6FF1@.microsoft.com...
>
|||After a bit of diggin, I found and corrected the options.
Located in the SQLserver ressource of the cluster, once put offline, just
added the correct dependencies.
Thanks a lot for the Geoff.
Julien
"Julien" wrote:
[vbcol=seagreen]
> Hi Geoff,
> Thanks a lot for your reply.
> It's definitely making sens to me but I'm not sure how to do this.
> Could you please l explain a bit further?
> Thanks,
> Julien
> "Geoff N. Hiten" wrote:
Drive missing from Enterprise Manager
Hi,
I have an SQL cluster on Windows 2003 with 2 drives (1 for dbs and 1 for logs). Both of these drives are accessible from Windows explorer.
In Enterprise Manager only the db drive appears as a location to house dbs and logs.
I can't work out how to get the second drive to appear and adding the path manual doesn't work as I end up with the other drive letter appending to the front eg "y:\z:\logs"
Has anyone seen this or have any ideas how to fix it?
Help much appreciated.What's your cluster like? Active/Passive or Active/Active?|||The cluster is active/passive.
Just figured it out, I forgot to add the log drive to the dependennce list for the SQL service.
I have an SQL cluster on Windows 2003 with 2 drives (1 for dbs and 1 for logs). Both of these drives are accessible from Windows explorer.
In Enterprise Manager only the db drive appears as a location to house dbs and logs.
I can't work out how to get the second drive to appear and adding the path manual doesn't work as I end up with the other drive letter appending to the front eg "y:\z:\logs"
Has anyone seen this or have any ideas how to fix it?
Help much appreciated.What's your cluster like? Active/Passive or Active/Active?|||The cluster is active/passive.
Just figured it out, I forgot to add the log drive to the dependennce list for the SQL service.
Drive lost - log files were on that drive - wondering about option
I've got a customer who re-booted their SQL server (SQL 2000) and when it
came up the F: drive - with all the log files - was gone.
They are going to attempt to get the F: drive back on-line - hopefully long
enough to detach the DB's and copy the LOG files to another drive.
If that does not work - we see two options at this moment.
We have backups, including TRANSACTION log backups every hour - last one was
at 3:00 pm today. The server was re-booted just before the 4:00 cycle - so
we have about 50 minutes of "unknown" work that was not backed up. We
realize we could restore all the DB's from the backup/transaction log backups
at get us back to 3:00 pm.
Other option would be to attempt to start the DB's with new empty logs. Not
so comfortable with this option. How do we check that the DB's were
closed/shutdown properly when the server rebooted - so we know that we aren't
going to be missing rollback/commit operations.
Thanks for any help you can offer - I might not be back till Sunday
afternoon to check this thread - but only have till Tuesday AM to get the box
back up and running.If you can't get your log files back, I suggest you first copy all data
files elsewhere for safe keeping. Then try to attach the databases. If a
database is at a consistent state and has a single log file, SQL Server will
recreate the log and no data will be lost.
For databases that cannot be attached, I suggest you restore from database
and transaction log backups. You can also salvage what you can for the lost
50 minutes by rebuilding logs from the copied data files, running DBCCs and
reconciling. However, you will not have logical or physical data integrity
after recreating logs for a 'dirty' database. You are wise to be
uncomfortable with using those databases as the live version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
> I've got a customer who re-booted their SQL server (SQL 2000) and when it
> came up the F: drive - with all the log files - was gone.
> They are going to attempt to get the F: drive back on-line - hopefully
> long
> enough to detach the DB's and copy the LOG files to another drive.
> If that does not work - we see two options at this moment.
> We have backups, including TRANSACTION log backups every hour - last one
> was
> at 3:00 pm today. The server was re-booted just before the 4:00 cycle -
> so
> we have about 50 minutes of "unknown" work that was not backed up. We
> realize we could restore all the DB's from the backup/transaction log
> backups
> at get us back to 3:00 pm.
> Other option would be to attempt to start the DB's with new empty logs.
> Not
> so comfortable with this option. How do we check that the DB's were
> closed/shutdown properly when the server rebooted - so we know that we
> aren't
> going to be missing rollback/commit operations.
> Thanks for any help you can offer - I might not be back till Sunday
> afternoon to check this thread - but only have till Tuesday AM to get the
> box
> back up and running.|||Dan - thanks for the response - kind of confirmed what I was expecting.
We do only have single LOG files...
How does the server know that the DB is in a consistent state? The server
was shutdown with the log files still accessible - it was the re-boot moment
that the F: drive disappered. If I can guarantee that the DB received all
logged data during shutdown (is that possible?) - then I can use these DB's
with empty logs - right?
Several of the DB's are our design - we have APPCONNECT tables that will
tell me who was connected and what time they jumped in. We also have TDATE
columns in all our tables that indicate GETDATE() timestamps of last
INSERT/UPDATE of the row.
Some of the DB's are not ours - so I'm going to have less ability to even do
a reconciliation between RESTORED DB and REATTACHED-WITH-EMPTY LOG DB's...
Thanks again!
"Dan Guzman" wrote:
> If you can't get your log files back, I suggest you first copy all data
> files elsewhere for safe keeping. Then try to attach the databases. If a
> database is at a consistent state and has a single log file, SQL Server will
> recreate the log and no data will be lost.
> For databases that cannot be attached, I suggest you restore from database
> and transaction log backups. You can also salvage what you can for the lost
> 50 minutes by rebuilding logs from the copied data files, running DBCCs and
> reconciling. However, you will not have logical or physical data integrity
> after recreating logs for a 'dirty' database. You are wise to be
> uncomfortable with using those databases as the live version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
> > I've got a customer who re-booted their SQL server (SQL 2000) and when it
> > came up the F: drive - with all the log files - was gone.
> >
> > They are going to attempt to get the F: drive back on-line - hopefully
> > long
> > enough to detach the DB's and copy the LOG files to another drive.
> >
> > If that does not work - we see two options at this moment.
> >
> > We have backups, including TRANSACTION log backups every hour - last one
> > was
> > at 3:00 pm today. The server was re-booted just before the 4:00 cycle -
> > so
> > we have about 50 minutes of "unknown" work that was not backed up. We
> > realize we could restore all the DB's from the backup/transaction log
> > backups
> > at get us back to 3:00 pm.
> >
> > Other option would be to attempt to start the DB's with new empty logs.
> > Not
> > so comfortable with this option. How do we check that the DB's were
> > closed/shutdown properly when the server rebooted - so we know that we
> > aren't
> > going to be missing rollback/commit operations.
> >
> > Thanks for any help you can offer - I might not be back till Sunday
> > afternoon to check this thread - but only have till Tuesday AM to get the
> > box
> > back up and running.
>
>|||> If I can guarantee that the DB received all
> logged data during shutdown (is that possible?) - then I can use these
> DB's
> with empty logs - right?
I don't recall the exact implementation details but the primary data file
contains information indicating whether or not a database was cleanly
shutdown. SQL Server checks this and will not create an new log file during
the attach unless it is safe to do so.
So the way to check it to try it - detach the suspect databases and then
attempt to attach specifying only the primary data file. If the shutdown
was clean, SQL Server will create a new log and you are good to go without
issues. If you get errors because the database shutdown wasn't clean, you
should restore from backups and accept the 50 minute data loss.
It's up to you whether you should go through the extra effort salvage and
data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:3FF3320E-7B51-4F0F-9F3F-806B24A9E7DB@.microsoft.com...
> Dan - thanks for the response - kind of confirmed what I was expecting.
> We do only have single LOG files...
> How does the server know that the DB is in a consistent state? The server
> was shutdown with the log files still accessible - it was the re-boot
> moment
> that the F: drive disappered. If I can guarantee that the DB received all
> logged data during shutdown (is that possible?) - then I can use these
> DB's
> with empty logs - right?
> Several of the DB's are our design - we have APPCONNECT tables that will
> tell me who was connected and what time they jumped in. We also have
> TDATE
> columns in all our tables that indicate GETDATE() timestamps of last
> INSERT/UPDATE of the row.
> Some of the DB's are not ours - so I'm going to have less ability to even
> do
> a reconciliation between RESTORED DB and REATTACHED-WITH-EMPTY LOG DB's...
> Thanks again!
> "Dan Guzman" wrote:
>> If you can't get your log files back, I suggest you first copy all data
>> files elsewhere for safe keeping. Then try to attach the databases. If
>> a
>> database is at a consistent state and has a single log file, SQL Server
>> will
>> recreate the log and no data will be lost.
>> For databases that cannot be attached, I suggest you restore from
>> database
>> and transaction log backups. You can also salvage what you can for the
>> lost
>> 50 minutes by rebuilding logs from the copied data files, running DBCCs
>> and
>> reconciling. However, you will not have logical or physical data
>> integrity
>> after recreating logs for a 'dirty' database. You are wise to be
>> uncomfortable with using those databases as the live version.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
>> > I've got a customer who re-booted their SQL server (SQL 2000) and when
>> > it
>> > came up the F: drive - with all the log files - was gone.
>> >
>> > They are going to attempt to get the F: drive back on-line - hopefully
>> > long
>> > enough to detach the DB's and copy the LOG files to another drive.
>> >
>> > If that does not work - we see two options at this moment.
>> >
>> > We have backups, including TRANSACTION log backups every hour - last
>> > one
>> > was
>> > at 3:00 pm today. The server was re-booted just before the 4:00
>> > cycle -
>> > so
>> > we have about 50 minutes of "unknown" work that was not backed up. We
>> > realize we could restore all the DB's from the backup/transaction log
>> > backups
>> > at get us back to 3:00 pm.
>> >
>> > Other option would be to attempt to start the DB's with new empty logs.
>> > Not
>> > so comfortable with this option. How do we check that the DB's were
>> > closed/shutdown properly when the server rebooted - so we know that we
>> > aren't
>> > going to be missing rollback/commit operations.
>> >
>> > Thanks for any help you can offer - I might not be back till Sunday
>> > afternoon to check this thread - but only have till Tuesday AM to get
>> > the
>> > box
>> > back up and running.
>>
came up the F: drive - with all the log files - was gone.
They are going to attempt to get the F: drive back on-line - hopefully long
enough to detach the DB's and copy the LOG files to another drive.
If that does not work - we see two options at this moment.
We have backups, including TRANSACTION log backups every hour - last one was
at 3:00 pm today. The server was re-booted just before the 4:00 cycle - so
we have about 50 minutes of "unknown" work that was not backed up. We
realize we could restore all the DB's from the backup/transaction log backups
at get us back to 3:00 pm.
Other option would be to attempt to start the DB's with new empty logs. Not
so comfortable with this option. How do we check that the DB's were
closed/shutdown properly when the server rebooted - so we know that we aren't
going to be missing rollback/commit operations.
Thanks for any help you can offer - I might not be back till Sunday
afternoon to check this thread - but only have till Tuesday AM to get the box
back up and running.If you can't get your log files back, I suggest you first copy all data
files elsewhere for safe keeping. Then try to attach the databases. If a
database is at a consistent state and has a single log file, SQL Server will
recreate the log and no data will be lost.
For databases that cannot be attached, I suggest you restore from database
and transaction log backups. You can also salvage what you can for the lost
50 minutes by rebuilding logs from the copied data files, running DBCCs and
reconciling. However, you will not have logical or physical data integrity
after recreating logs for a 'dirty' database. You are wise to be
uncomfortable with using those databases as the live version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
> I've got a customer who re-booted their SQL server (SQL 2000) and when it
> came up the F: drive - with all the log files - was gone.
> They are going to attempt to get the F: drive back on-line - hopefully
> long
> enough to detach the DB's and copy the LOG files to another drive.
> If that does not work - we see two options at this moment.
> We have backups, including TRANSACTION log backups every hour - last one
> was
> at 3:00 pm today. The server was re-booted just before the 4:00 cycle -
> so
> we have about 50 minutes of "unknown" work that was not backed up. We
> realize we could restore all the DB's from the backup/transaction log
> backups
> at get us back to 3:00 pm.
> Other option would be to attempt to start the DB's with new empty logs.
> Not
> so comfortable with this option. How do we check that the DB's were
> closed/shutdown properly when the server rebooted - so we know that we
> aren't
> going to be missing rollback/commit operations.
> Thanks for any help you can offer - I might not be back till Sunday
> afternoon to check this thread - but only have till Tuesday AM to get the
> box
> back up and running.|||Dan - thanks for the response - kind of confirmed what I was expecting.
We do only have single LOG files...
How does the server know that the DB is in a consistent state? The server
was shutdown with the log files still accessible - it was the re-boot moment
that the F: drive disappered. If I can guarantee that the DB received all
logged data during shutdown (is that possible?) - then I can use these DB's
with empty logs - right?
Several of the DB's are our design - we have APPCONNECT tables that will
tell me who was connected and what time they jumped in. We also have TDATE
columns in all our tables that indicate GETDATE() timestamps of last
INSERT/UPDATE of the row.
Some of the DB's are not ours - so I'm going to have less ability to even do
a reconciliation between RESTORED DB and REATTACHED-WITH-EMPTY LOG DB's...
Thanks again!
"Dan Guzman" wrote:
> If you can't get your log files back, I suggest you first copy all data
> files elsewhere for safe keeping. Then try to attach the databases. If a
> database is at a consistent state and has a single log file, SQL Server will
> recreate the log and no data will be lost.
> For databases that cannot be attached, I suggest you restore from database
> and transaction log backups. You can also salvage what you can for the lost
> 50 minutes by rebuilding logs from the copied data files, running DBCCs and
> reconciling. However, you will not have logical or physical data integrity
> after recreating logs for a 'dirty' database. You are wise to be
> uncomfortable with using those databases as the live version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
> > I've got a customer who re-booted their SQL server (SQL 2000) and when it
> > came up the F: drive - with all the log files - was gone.
> >
> > They are going to attempt to get the F: drive back on-line - hopefully
> > long
> > enough to detach the DB's and copy the LOG files to another drive.
> >
> > If that does not work - we see two options at this moment.
> >
> > We have backups, including TRANSACTION log backups every hour - last one
> > was
> > at 3:00 pm today. The server was re-booted just before the 4:00 cycle -
> > so
> > we have about 50 minutes of "unknown" work that was not backed up. We
> > realize we could restore all the DB's from the backup/transaction log
> > backups
> > at get us back to 3:00 pm.
> >
> > Other option would be to attempt to start the DB's with new empty logs.
> > Not
> > so comfortable with this option. How do we check that the DB's were
> > closed/shutdown properly when the server rebooted - so we know that we
> > aren't
> > going to be missing rollback/commit operations.
> >
> > Thanks for any help you can offer - I might not be back till Sunday
> > afternoon to check this thread - but only have till Tuesday AM to get the
> > box
> > back up and running.
>
>|||> If I can guarantee that the DB received all
> logged data during shutdown (is that possible?) - then I can use these
> DB's
> with empty logs - right?
I don't recall the exact implementation details but the primary data file
contains information indicating whether or not a database was cleanly
shutdown. SQL Server checks this and will not create an new log file during
the attach unless it is safe to do so.
So the way to check it to try it - detach the suspect databases and then
attempt to attach specifying only the primary data file. If the shutdown
was clean, SQL Server will create a new log and you are good to go without
issues. If you get errors because the database shutdown wasn't clean, you
should restore from backups and accept the 50 minute data loss.
It's up to you whether you should go through the extra effort salvage and
data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:3FF3320E-7B51-4F0F-9F3F-806B24A9E7DB@.microsoft.com...
> Dan - thanks for the response - kind of confirmed what I was expecting.
> We do only have single LOG files...
> How does the server know that the DB is in a consistent state? The server
> was shutdown with the log files still accessible - it was the re-boot
> moment
> that the F: drive disappered. If I can guarantee that the DB received all
> logged data during shutdown (is that possible?) - then I can use these
> DB's
> with empty logs - right?
> Several of the DB's are our design - we have APPCONNECT tables that will
> tell me who was connected and what time they jumped in. We also have
> TDATE
> columns in all our tables that indicate GETDATE() timestamps of last
> INSERT/UPDATE of the row.
> Some of the DB's are not ours - so I'm going to have less ability to even
> do
> a reconciliation between RESTORED DB and REATTACHED-WITH-EMPTY LOG DB's...
> Thanks again!
> "Dan Guzman" wrote:
>> If you can't get your log files back, I suggest you first copy all data
>> files elsewhere for safe keeping. Then try to attach the databases. If
>> a
>> database is at a consistent state and has a single log file, SQL Server
>> will
>> recreate the log and no data will be lost.
>> For databases that cannot be attached, I suggest you restore from
>> database
>> and transaction log backups. You can also salvage what you can for the
>> lost
>> 50 minutes by rebuilding logs from the copied data files, running DBCCs
>> and
>> reconciling. However, you will not have logical or physical data
>> integrity
>> after recreating logs for a 'dirty' database. You are wise to be
>> uncomfortable with using those databases as the live version.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
>> > I've got a customer who re-booted their SQL server (SQL 2000) and when
>> > it
>> > came up the F: drive - with all the log files - was gone.
>> >
>> > They are going to attempt to get the F: drive back on-line - hopefully
>> > long
>> > enough to detach the DB's and copy the LOG files to another drive.
>> >
>> > If that does not work - we see two options at this moment.
>> >
>> > We have backups, including TRANSACTION log backups every hour - last
>> > one
>> > was
>> > at 3:00 pm today. The server was re-booted just before the 4:00
>> > cycle -
>> > so
>> > we have about 50 minutes of "unknown" work that was not backed up. We
>> > realize we could restore all the DB's from the backup/transaction log
>> > backups
>> > at get us back to 3:00 pm.
>> >
>> > Other option would be to attempt to start the DB's with new empty logs.
>> > Not
>> > so comfortable with this option. How do we check that the DB's were
>> > closed/shutdown properly when the server rebooted - so we know that we
>> > aren't
>> > going to be missing rollback/commit operations.
>> >
>> > Thanks for any help you can offer - I might not be back till Sunday
>> > afternoon to check this thread - but only have till Tuesday AM to get
>> > the
>> > box
>> > back up and running.
>>
Drive lost - log files were on that drive - wondering about option
I've got a customer who re-booted their SQL server (SQL 2000) and when it
came up the F: drive - with all the log files - was gone.
They are going to attempt to get the F: drive back on-line - hopefully long
enough to detach the DB's and copy the LOG files to another drive.
If that does not work - we see two options at this moment.
We have backups, including TRANSACTION log backups every hour - last one was
at 3:00 pm today. The server was re-booted just before the 4:00 cycle - so
we have about 50 minutes of "unknown" work that was not backed up. We
realize we could restore all the DB's from the backup/transaction log backup
s
at get us back to 3:00 pm.
Other option would be to attempt to start the DB's with new empty logs. Not
so comfortable with this option. How do we check that the DB's were
closed/shutdown properly when the server rebooted - so we know that we aren'
t
going to be missing rollback/commit operations.
Thanks for any help you can offer - I might not be back till Sunday
afternoon to check this thread - but only have till Tuesday AM to get the bo
x
back up and running.If you can't get your log files back, I suggest you first copy all data
files elsewhere for safe keeping. Then try to attach the databases. If a
database is at a consistent state and has a single log file, SQL Server will
recreate the log and no data will be lost.
For databases that cannot be attached, I suggest you restore from database
and transaction log backups. You can also salvage what you can for the lost
50 minutes by rebuilding logs from the copied data files, running DBCCs and
reconciling. However, you will not have logical or physical data integrity
after recreating logs for a 'dirty' database. You are wise to be
uncomfortable with using those databases as the live version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
> I've got a customer who re-booted their SQL server (SQL 2000) and when it
> came up the F: drive - with all the log files - was gone.
> They are going to attempt to get the F: drive back on-line - hopefully
> long
> enough to detach the DB's and copy the LOG files to another drive.
> If that does not work - we see two options at this moment.
> We have backups, including TRANSACTION log backups every hour - last one
> was
> at 3:00 pm today. The server was re-booted just before the 4:00 cycle -
> so
> we have about 50 minutes of "unknown" work that was not backed up. We
> realize we could restore all the DB's from the backup/transaction log
> backups
> at get us back to 3:00 pm.
> Other option would be to attempt to start the DB's with new empty logs.
> Not
> so comfortable with this option. How do we check that the DB's were
> closed/shutdown properly when the server rebooted - so we know that we
> aren't
> going to be missing rollback/commit operations.
> Thanks for any help you can offer - I might not be back till Sunday
> afternoon to check this thread - but only have till Tuesday AM to get the
> box
> back up and running.
came up the F: drive - with all the log files - was gone.
They are going to attempt to get the F: drive back on-line - hopefully long
enough to detach the DB's and copy the LOG files to another drive.
If that does not work - we see two options at this moment.
We have backups, including TRANSACTION log backups every hour - last one was
at 3:00 pm today. The server was re-booted just before the 4:00 cycle - so
we have about 50 minutes of "unknown" work that was not backed up. We
realize we could restore all the DB's from the backup/transaction log backup
s
at get us back to 3:00 pm.
Other option would be to attempt to start the DB's with new empty logs. Not
so comfortable with this option. How do we check that the DB's were
closed/shutdown properly when the server rebooted - so we know that we aren'
t
going to be missing rollback/commit operations.
Thanks for any help you can offer - I might not be back till Sunday
afternoon to check this thread - but only have till Tuesday AM to get the bo
x
back up and running.If you can't get your log files back, I suggest you first copy all data
files elsewhere for safe keeping. Then try to attach the databases. If a
database is at a consistent state and has a single log file, SQL Server will
recreate the log and no data will be lost.
For databases that cannot be attached, I suggest you restore from database
and transaction log backups. You can also salvage what you can for the lost
50 minutes by rebuilding logs from the copied data files, running DBCCs and
reconciling. However, you will not have logical or physical data integrity
after recreating logs for a 'dirty' database. You are wise to be
uncomfortable with using those databases as the live version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:0B9A38A2-067F-4101-9BE5-95D6F59759DC@.microsoft.com...
> I've got a customer who re-booted their SQL server (SQL 2000) and when it
> came up the F: drive - with all the log files - was gone.
> They are going to attempt to get the F: drive back on-line - hopefully
> long
> enough to detach the DB's and copy the LOG files to another drive.
> If that does not work - we see two options at this moment.
> We have backups, including TRANSACTION log backups every hour - last one
> was
> at 3:00 pm today. The server was re-booted just before the 4:00 cycle -
> so
> we have about 50 minutes of "unknown" work that was not backed up. We
> realize we could restore all the DB's from the backup/transaction log
> backups
> at get us back to 3:00 pm.
> Other option would be to attempt to start the DB's with new empty logs.
> Not
> so comfortable with this option. How do we check that the DB's were
> closed/shutdown properly when the server rebooted - so we know that we
> aren't
> going to be missing rollback/commit operations.
> Thanks for any help you can offer - I might not be back till Sunday
> afternoon to check this thread - but only have till Tuesday AM to get the
> box
> back up and running.
Drive letters and MDF/LDF files...
All,
I'm writing to ask if anyone knows whether or not MS SQL server stores in any system tables the association between a database and the drive letter/directory path where its corresponding MDF/LDF files are located.
Thanks,
IsaacSELECT database_id
, name
, physical_name
FROM sys.master_files
SQL 2005 - not sure re 2000|||select filename
from master..sysaltfiles|||Exec master.dbo.sp_helpfile
Regards,
hmscott|||SELECT name
, physical_name
FROM [dbName].sys.database_files
I'm writing to ask if anyone knows whether or not MS SQL server stores in any system tables the association between a database and the drive letter/directory path where its corresponding MDF/LDF files are located.
Thanks,
IsaacSELECT database_id
, name
, physical_name
FROM sys.master_files
SQL 2005 - not sure re 2000|||select filename
from master..sysaltfiles|||Exec master.dbo.sp_helpfile
Regards,
hmscott|||SELECT name
, physical_name
FROM [dbName].sys.database_files
drive is corupted
Hi,
the drive with the data file is corupted, however the
drive where the transaction log is located is fine.
What is the best aproach for recovering the db ?
Can I restore the db from the last full db backup and
then backup the curent log and apply to it ?
The only thing that has happened beetween the full db
backup and crash is one transaction log backup.
Any view is apreciated!Mirna,
> What is the best aproach for recovering the db ?
Are you currently dong log backups? What is the recovery model for the database. If it is simple,
then you can only recovery up to the latest database backup. If it is full and you also do log
backup, do a log backup now using the NO_TRUNCATE parameter and then restore the latest database
backup and all subsequent log backups (including this last one).
> Can I restore the db from the last full db backup and
> then backup the curent log and apply to it ?
No, as soon as you restored you lost the stuff in the log file. Always start with performing a log
backup as I explained above.
> The only thing that has happened beetween the full db
> backup and crash is one transaction log backup.
Then it seems that you are in good shape. Do this last log backup and then the restore stuff.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> Hi,
> the drive with the data file is corupted, however the
> drive where the transaction log is located is fine.
> What is the best aproach for recovering the db ?
> Can I restore the db from the last full db backup and
> then backup the curent log and apply to it ?
> The only thing that has happened beetween the full db
> backup and crash is one transaction log backup.
> Any view is apreciated!|||Thanks Tibor for a quick answer!
The recovery model for the database is full.
mdf file is not accesible as well as the last transaction
log backup( they reside on the same drive and they are not
backed up to a tape before the failure ).
I can not perform the transaction log backup since the
problem is actually OS not the hardware.It looks like that
I will have to go to a last full db backup unless I can
somehow apply the transaction log (ldf file).
In other words I have full db backup of Mydb.BAK and ldf
file of the same db. Since the transaction log backup
was performed once I am wondering if that made the log(ldf)
invalid. I am backing the log as a part of the db
maintenance plan.
Thanks in advance!
>--Original Message--
>Mirna,
>> What is the best aproach for recovering the db ?
>Are you currently dong log backups? What is the recovery
model for the database. If it is simple,
>then you can only recovery up to the latest database
backup. If it is full and you also do log
>backup, do a log backup now using the NO_TRUNCATE
parameter and then restore the latest database
>backup and all subsequent log backups (including this
last one).
>
>> Can I restore the db from the last full db backup and
>> then backup the curent log and apply to it ?
>No, as soon as you restored you lost the stuff in the log
file. Always start with performing a log
>backup as I explained above.
>
>> The only thing that has happened beetween the full db
>> backup and crash is one transaction log backup.
>Then it seems that you are in good shape. Do this last
log backup and then the restore stuff.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> Hi,
>> the drive with the data file is corupted, however the
>> drive where the transaction log is located is fine.
>> What is the best aproach for recovering the db ?
>> Can I restore the db from the last full db backup and
>> then backup the curent log and apply to it ?
>> The only thing that has happened beetween the full db
>> backup and crash is one transaction log backup.
>> Any view is apreciated!
>
>.
>|||> The recovery model for the database is full.
> mdf file is not accesible as well as the last transaction
> log backup
Ouch. Then you would not be able to use a log backup even if you managed to do one at this point in
time. This is because the log is emptied each time you do a log backup, so now you are in a
situation where you did a log backup and that is lost. You have a "hole" in your sequence of log
records.
If it weren't for that prior log backup is lost, you could actually product this last one log backup
event though the OS install is damaged (or if SQL Server install is damaged). You would "fake"
another database on another SQL Server, slide in that log file (ldf) into that install and then do
the log backup using NO_TRUNCATE on that installation. There's a KB article on that subject. This is
only FYI as this unfortunately won't help you because of that missing prior log backup.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
> Thanks Tibor for a quick answer!
> The recovery model for the database is full.
> mdf file is not accesible as well as the last transaction
> log backup( they reside on the same drive and they are not
> backed up to a tape before the failure ).
> I can not perform the transaction log backup since the
> problem is actually OS not the hardware.It looks like that
> I will have to go to a last full db backup unless I can
> somehow apply the transaction log (ldf file).
> In other words I have full db backup of Mydb.BAK and ldf
> file of the same db. Since the transaction log backup
> was performed once I am wondering if that made the log(ldf)
> invalid. I am backing the log as a part of the db
> maintenance plan.
> Thanks in advance!
>
> >--Original Message--
> >Mirna,
> >
> >> What is the best aproach for recovering the db ?
> >
> >Are you currently dong log backups? What is the recovery
> model for the database. If it is simple,
> >then you can only recovery up to the latest database
> backup. If it is full and you also do log
> >backup, do a log backup now using the NO_TRUNCATE
> parameter and then restore the latest database
> >backup and all subsequent log backups (including this
> last one).
> >
> >
> >> Can I restore the db from the last full db backup and
> >> then backup the curent log and apply to it ?
> >
> >No, as soon as you restored you lost the stuff in the log
> file. Always start with performing a log
> >backup as I explained above.
> >
> >
> >> The only thing that has happened beetween the full db
> >> backup and crash is one transaction log backup.
> >
> >Then it seems that you are in good shape. Do this last
> log backup and then the restore stuff.
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> the drive with the data file is corupted, however the
> >> drive where the transaction log is located is fine.
> >>
> >> What is the best aproach for recovering the db ?
> >> Can I restore the db from the last full db backup and
> >> then backup the curent log and apply to it ?
> >> The only thing that has happened beetween the full db
> >> backup and crash is one transaction log backup.
> >>
> >> Any view is apreciated!
> >
> >
> >.
> >|||Thanks Tibor,
I understand what you are saying.. too bad that we will
have to go to a last full db backup. Thank You again!
>--Original Message--
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
transaction
>> log backup
>Ouch. Then you would not be able to use a log backup even
if you managed to do one at this point in
>time. This is because the log is emptied each time you do
a log backup, so now you are in a
>situation where you did a log backup and that is lost.
You have a "hole" in your sequence of log
>records.
>If it weren't for that prior log backup is lost, you
could actually product this last one log backup
>event though the OS install is damaged (or if SQL Server
install is damaged). You would "fake"
>another database on another SQL Server, slide in that log
file (ldf) into that install and then do
>the log backup using NO_TRUNCATE on that installation.
There's a KB article on that subject. This is
>only FYI as this unfortunately won't help you because of
that missing prior log backup.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
>> Thanks Tibor for a quick answer!
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
transaction
>> log backup( they reside on the same drive and they are
not
>> backed up to a tape before the failure ).
>> I can not perform the transaction log backup since the
>> problem is actually OS not the hardware.It looks like
that
>> I will have to go to a last full db backup unless I can
>> somehow apply the transaction log (ldf file).
>> In other words I have full db backup of Mydb.BAK and ldf
>> file of the same db. Since the transaction log backup
>> was performed once I am wondering if that made the log
(ldf)
>> invalid. I am backing the log as a part of the db
>> maintenance plan.
>> Thanks in advance!
>>
>> >--Original Message--
>> >Mirna,
>> >
>> >> What is the best aproach for recovering the db ?
>> >
>> >Are you currently dong log backups? What is the
recovery
>> model for the database. If it is simple,
>> >then you can only recovery up to the latest database
>> backup. If it is full and you also do log
>> >backup, do a log backup now using the NO_TRUNCATE
>> parameter and then restore the latest database
>> >backup and all subsequent log backups (including this
>> last one).
>> >
>> >
>> >> Can I restore the db from the last full db backup
and
>> >> then backup the curent log and apply to it ?
>> >
>> >No, as soon as you restored you lost the stuff in the
log
>> file. Always start with performing a log
>> >backup as I explained above.
>> >
>> >
>> >> The only thing that has happened beetween the full db
>> >> backup and crash is one transaction log backup.
>> >
>> >Then it seems that you are in good shape. Do this last
>> log backup and then the restore stuff.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> the drive with the data file is corupted, however the
>> >> drive where the transaction log is located is fine.
>> >>
>> >> What is the best aproach for recovering the db ?
>> >> Can I restore the db from the last full db backup
and
>> >> then backup the curent log and apply to it ?
>> >> The only thing that has happened beetween the full db
>> >> backup and crash is one transaction log backup.
>> >>
>> >> Any view is apreciated!
>> >
>> >
>> >.
>> >
>
>.
>|||Is there a way that ldf file can be used to recover the
database if the transaction log backup and .mdf file is
lost ? Does Microsoft has any utility that can recover
what has been flushed to mdf file during the transaction
log backup ?
Any view is apreciated
>--Original Message--
>Thanks Tibor,
>I understand what you are saying.. too bad that we will
>have to go to a last full db backup. Thank You again!
>>--Original Message--
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
>transaction
>> log backup
>>Ouch. Then you would not be able to use a log backup
even
>if you managed to do one at this point in
>>time. This is because the log is emptied each time you
do
>a log backup, so now you are in a
>>situation where you did a log backup and that is lost.
>You have a "hole" in your sequence of log
>>records.
>>If it weren't for that prior log backup is lost, you
>could actually product this last one log backup
>>event though the OS install is damaged (or if SQL Server
>install is damaged). You would "fake"
>>another database on another SQL Server, slide in that
log
>file (ldf) into that install and then do
>>the log backup using NO_TRUNCATE on that installation.
>There's a KB article on that subject. This is
>>only FYI as this unfortunately won't help you because of
>that missing prior log backup.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at: http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
>> Thanks Tibor for a quick answer!
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
>transaction
>> log backup( they reside on the same drive and they are
>not
>> backed up to a tape before the failure ).
>> I can not perform the transaction log backup since the
>> problem is actually OS not the hardware.It looks like
>that
>> I will have to go to a last full db backup unless I can
>> somehow apply the transaction log (ldf file).
>> In other words I have full db backup of Mydb.BAK and
ldf
>> file of the same db. Since the transaction log backup
>> was performed once I am wondering if that made the log
>(ldf)
>> invalid. I am backing the log as a part of the db
>> maintenance plan.
>> Thanks in advance!
>>
>> >--Original Message--
>> >Mirna,
>> >
>> >> What is the best aproach for recovering the db ?
>> >
>> >Are you currently dong log backups? What is the
>recovery
>> model for the database. If it is simple,
>> >then you can only recovery up to the latest database
>> backup. If it is full and you also do log
>> >backup, do a log backup now using the NO_TRUNCATE
>> parameter and then restore the latest database
>> >backup and all subsequent log backups (including this
>> last one).
>> >
>> >
>> >> Can I restore the db from the last full db backup
>and
>> >> then backup the curent log and apply to it ?
>> >
>> >No, as soon as you restored you lost the stuff in the
>log
>> file. Always start with performing a log
>> >backup as I explained above.
>> >
>> >
>> >> The only thing that has happened beetween the full
db
>> >> backup and crash is one transaction log backup.
>> >
>> >Then it seems that you are in good shape. Do this last
>> log backup and then the restore stuff.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> the drive with the data file is corupted, however
the
>> >> drive where the transaction log is located is fine.
>> >>
>> >> What is the best aproach for recovering the db ?
>> >> Can I restore the db from the last full db backup
>and
>> >> then backup the curent log and apply to it ?
>> >> The only thing that has happened beetween the full
db
>> >> backup and crash is one transaction log backup.
>> >>
>> >> Any view is apreciated!
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||> Is there a way that ldf file can be used to recover the
> database if the transaction log backup and .mdf file is
> lost ?
No. As I described, you have a missing log backup.
> Does Microsoft has any utility that can recover
> what has been flushed to mdf file during the transaction
> log backup ?
This is not how things work ("flushed to mdf file"). Read about transaction log handling and backup
and restore in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:b43501c3b8f1$7cf00640$a601280a@.phx.gbl...
> Is there a way that ldf file can be used to recover the
> database if the transaction log backup and .mdf file is
> lost ? Does Microsoft has any utility that can recover
> what has been flushed to mdf file during the transaction
> log backup ?
> Any view is apreciated
> >--Original Message--
> >Thanks Tibor,
> >
> >I understand what you are saying.. too bad that we will
> >have to go to a last full db backup. Thank You again!
> >
> >>--Original Message--
> >> The recovery model for the database is full.
> >> mdf file is not accesible as well as the last
> >transaction
> >> log backup
> >>
> >>Ouch. Then you would not be able to use a log backup
> even
> >if you managed to do one at this point in
> >>time. This is because the log is emptied each time you
> do
> >a log backup, so now you are in a
> >>situation where you did a log backup and that is lost.
> >You have a "hole" in your sequence of log
> >>records.
> >>
> >>If it weren't for that prior log backup is lost, you
> >could actually product this last one log backup
> >>event though the OS install is damaged (or if SQL Server
> >install is damaged). You would "fake"
> >>another database on another SQL Server, slide in that
> log
> >file (ldf) into that install and then do
> >>the log backup using NO_TRUNCATE on that installation.
> >There's a KB article on that subject. This is
> >>only FYI as this unfortunately won't help you because of
> >that missing prior log backup.
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>Archive at: http://groups.google.com/groups?
> >oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> >message
> >>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
> >> Thanks Tibor for a quick answer!
> >>
> >> The recovery model for the database is full.
> >> mdf file is not accesible as well as the last
> >transaction
> >> log backup( they reside on the same drive and they are
> >not
> >> backed up to a tape before the failure ).
> >> I can not perform the transaction log backup since the
> >> problem is actually OS not the hardware.It looks like
> >that
> >> I will have to go to a last full db backup unless I can
> >> somehow apply the transaction log (ldf file).
> >> In other words I have full db backup of Mydb.BAK and
> ldf
> >> file of the same db. Since the transaction log backup
> >> was performed once I am wondering if that made the log
> >(ldf)
> >> invalid. I am backing the log as a part of the db
> >> maintenance plan.
> >>
> >> Thanks in advance!
> >>
> >>
> >> >--Original Message--
> >> >Mirna,
> >> >
> >> >> What is the best aproach for recovering the db ?
> >> >
> >> >Are you currently dong log backups? What is the
> >recovery
> >> model for the database. If it is simple,
> >> >then you can only recovery up to the latest database
> >> backup. If it is full and you also do log
> >> >backup, do a log backup now using the NO_TRUNCATE
> >> parameter and then restore the latest database
> >> >backup and all subsequent log backups (including this
> >> last one).
> >> >
> >> >
> >> >> Can I restore the db from the last full db backup
> >and
> >> >> then backup the curent log and apply to it ?
> >> >
> >> >No, as soon as you restored you lost the stuff in the
> >log
> >> file. Always start with performing a log
> >> >backup as I explained above.
> >> >
> >> >
> >> >> The only thing that has happened beetween the full
> db
> >> >> backup and crash is one transaction log backup.
> >> >
> >> >Then it seems that you are in good shape. Do this last
> >> log backup and then the restore stuff.
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at: http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> >> >> Hi,
> >> >>
> >> >> the drive with the data file is corupted, however
> the
> >> >> drive where the transaction log is located is fine.
> >> >>
> >> >> What is the best aproach for recovering the db ?
> >> >> Can I restore the db from the last full db backup
> >and
> >> >> then backup the curent log and apply to it ?
> >> >> The only thing that has happened beetween the full
> db
> >> >> backup and crash is one transaction log backup.
> >> >>
> >> >> Any view is apreciated!
> >> >
> >> >
> >> >.
> >> >
> >>
> >>
> >>.
> >>
> >.
> >
the drive with the data file is corupted, however the
drive where the transaction log is located is fine.
What is the best aproach for recovering the db ?
Can I restore the db from the last full db backup and
then backup the curent log and apply to it ?
The only thing that has happened beetween the full db
backup and crash is one transaction log backup.
Any view is apreciated!Mirna,
> What is the best aproach for recovering the db ?
Are you currently dong log backups? What is the recovery model for the database. If it is simple,
then you can only recovery up to the latest database backup. If it is full and you also do log
backup, do a log backup now using the NO_TRUNCATE parameter and then restore the latest database
backup and all subsequent log backups (including this last one).
> Can I restore the db from the last full db backup and
> then backup the curent log and apply to it ?
No, as soon as you restored you lost the stuff in the log file. Always start with performing a log
backup as I explained above.
> The only thing that has happened beetween the full db
> backup and crash is one transaction log backup.
Then it seems that you are in good shape. Do this last log backup and then the restore stuff.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> Hi,
> the drive with the data file is corupted, however the
> drive where the transaction log is located is fine.
> What is the best aproach for recovering the db ?
> Can I restore the db from the last full db backup and
> then backup the curent log and apply to it ?
> The only thing that has happened beetween the full db
> backup and crash is one transaction log backup.
> Any view is apreciated!|||Thanks Tibor for a quick answer!
The recovery model for the database is full.
mdf file is not accesible as well as the last transaction
log backup( they reside on the same drive and they are not
backed up to a tape before the failure ).
I can not perform the transaction log backup since the
problem is actually OS not the hardware.It looks like that
I will have to go to a last full db backup unless I can
somehow apply the transaction log (ldf file).
In other words I have full db backup of Mydb.BAK and ldf
file of the same db. Since the transaction log backup
was performed once I am wondering if that made the log(ldf)
invalid. I am backing the log as a part of the db
maintenance plan.
Thanks in advance!
>--Original Message--
>Mirna,
>> What is the best aproach for recovering the db ?
>Are you currently dong log backups? What is the recovery
model for the database. If it is simple,
>then you can only recovery up to the latest database
backup. If it is full and you also do log
>backup, do a log backup now using the NO_TRUNCATE
parameter and then restore the latest database
>backup and all subsequent log backups (including this
last one).
>
>> Can I restore the db from the last full db backup and
>> then backup the curent log and apply to it ?
>No, as soon as you restored you lost the stuff in the log
file. Always start with performing a log
>backup as I explained above.
>
>> The only thing that has happened beetween the full db
>> backup and crash is one transaction log backup.
>Then it seems that you are in good shape. Do this last
log backup and then the restore stuff.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> Hi,
>> the drive with the data file is corupted, however the
>> drive where the transaction log is located is fine.
>> What is the best aproach for recovering the db ?
>> Can I restore the db from the last full db backup and
>> then backup the curent log and apply to it ?
>> The only thing that has happened beetween the full db
>> backup and crash is one transaction log backup.
>> Any view is apreciated!
>
>.
>|||> The recovery model for the database is full.
> mdf file is not accesible as well as the last transaction
> log backup
Ouch. Then you would not be able to use a log backup even if you managed to do one at this point in
time. This is because the log is emptied each time you do a log backup, so now you are in a
situation where you did a log backup and that is lost. You have a "hole" in your sequence of log
records.
If it weren't for that prior log backup is lost, you could actually product this last one log backup
event though the OS install is damaged (or if SQL Server install is damaged). You would "fake"
another database on another SQL Server, slide in that log file (ldf) into that install and then do
the log backup using NO_TRUNCATE on that installation. There's a KB article on that subject. This is
only FYI as this unfortunately won't help you because of that missing prior log backup.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
> Thanks Tibor for a quick answer!
> The recovery model for the database is full.
> mdf file is not accesible as well as the last transaction
> log backup( they reside on the same drive and they are not
> backed up to a tape before the failure ).
> I can not perform the transaction log backup since the
> problem is actually OS not the hardware.It looks like that
> I will have to go to a last full db backup unless I can
> somehow apply the transaction log (ldf file).
> In other words I have full db backup of Mydb.BAK and ldf
> file of the same db. Since the transaction log backup
> was performed once I am wondering if that made the log(ldf)
> invalid. I am backing the log as a part of the db
> maintenance plan.
> Thanks in advance!
>
> >--Original Message--
> >Mirna,
> >
> >> What is the best aproach for recovering the db ?
> >
> >Are you currently dong log backups? What is the recovery
> model for the database. If it is simple,
> >then you can only recovery up to the latest database
> backup. If it is full and you also do log
> >backup, do a log backup now using the NO_TRUNCATE
> parameter and then restore the latest database
> >backup and all subsequent log backups (including this
> last one).
> >
> >
> >> Can I restore the db from the last full db backup and
> >> then backup the curent log and apply to it ?
> >
> >No, as soon as you restored you lost the stuff in the log
> file. Always start with performing a log
> >backup as I explained above.
> >
> >
> >> The only thing that has happened beetween the full db
> >> backup and crash is one transaction log backup.
> >
> >Then it seems that you are in good shape. Do this last
> log backup and then the restore stuff.
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> >> Hi,
> >>
> >> the drive with the data file is corupted, however the
> >> drive where the transaction log is located is fine.
> >>
> >> What is the best aproach for recovering the db ?
> >> Can I restore the db from the last full db backup and
> >> then backup the curent log and apply to it ?
> >> The only thing that has happened beetween the full db
> >> backup and crash is one transaction log backup.
> >>
> >> Any view is apreciated!
> >
> >
> >.
> >|||Thanks Tibor,
I understand what you are saying.. too bad that we will
have to go to a last full db backup. Thank You again!
>--Original Message--
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
transaction
>> log backup
>Ouch. Then you would not be able to use a log backup even
if you managed to do one at this point in
>time. This is because the log is emptied each time you do
a log backup, so now you are in a
>situation where you did a log backup and that is lost.
You have a "hole" in your sequence of log
>records.
>If it weren't for that prior log backup is lost, you
could actually product this last one log backup
>event though the OS install is damaged (or if SQL Server
install is damaged). You would "fake"
>another database on another SQL Server, slide in that log
file (ldf) into that install and then do
>the log backup using NO_TRUNCATE on that installation.
There's a KB article on that subject. This is
>only FYI as this unfortunately won't help you because of
that missing prior log backup.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
>> Thanks Tibor for a quick answer!
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
transaction
>> log backup( they reside on the same drive and they are
not
>> backed up to a tape before the failure ).
>> I can not perform the transaction log backup since the
>> problem is actually OS not the hardware.It looks like
that
>> I will have to go to a last full db backup unless I can
>> somehow apply the transaction log (ldf file).
>> In other words I have full db backup of Mydb.BAK and ldf
>> file of the same db. Since the transaction log backup
>> was performed once I am wondering if that made the log
(ldf)
>> invalid. I am backing the log as a part of the db
>> maintenance plan.
>> Thanks in advance!
>>
>> >--Original Message--
>> >Mirna,
>> >
>> >> What is the best aproach for recovering the db ?
>> >
>> >Are you currently dong log backups? What is the
recovery
>> model for the database. If it is simple,
>> >then you can only recovery up to the latest database
>> backup. If it is full and you also do log
>> >backup, do a log backup now using the NO_TRUNCATE
>> parameter and then restore the latest database
>> >backup and all subsequent log backups (including this
>> last one).
>> >
>> >
>> >> Can I restore the db from the last full db backup
and
>> >> then backup the curent log and apply to it ?
>> >
>> >No, as soon as you restored you lost the stuff in the
log
>> file. Always start with performing a log
>> >backup as I explained above.
>> >
>> >
>> >> The only thing that has happened beetween the full db
>> >> backup and crash is one transaction log backup.
>> >
>> >Then it seems that you are in good shape. Do this last
>> log backup and then the restore stuff.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> the drive with the data file is corupted, however the
>> >> drive where the transaction log is located is fine.
>> >>
>> >> What is the best aproach for recovering the db ?
>> >> Can I restore the db from the last full db backup
and
>> >> then backup the curent log and apply to it ?
>> >> The only thing that has happened beetween the full db
>> >> backup and crash is one transaction log backup.
>> >>
>> >> Any view is apreciated!
>> >
>> >
>> >.
>> >
>
>.
>|||Is there a way that ldf file can be used to recover the
database if the transaction log backup and .mdf file is
lost ? Does Microsoft has any utility that can recover
what has been flushed to mdf file during the transaction
log backup ?
Any view is apreciated
>--Original Message--
>Thanks Tibor,
>I understand what you are saying.. too bad that we will
>have to go to a last full db backup. Thank You again!
>>--Original Message--
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
>transaction
>> log backup
>>Ouch. Then you would not be able to use a log backup
even
>if you managed to do one at this point in
>>time. This is because the log is emptied each time you
do
>a log backup, so now you are in a
>>situation where you did a log backup and that is lost.
>You have a "hole" in your sequence of log
>>records.
>>If it weren't for that prior log backup is lost, you
>could actually product this last one log backup
>>event though the OS install is damaged (or if SQL Server
>install is damaged). You would "fake"
>>another database on another SQL Server, slide in that
log
>file (ldf) into that install and then do
>>the log backup using NO_TRUNCATE on that installation.
>There's a KB article on that subject. This is
>>only FYI as this unfortunately won't help you because of
>that missing prior log backup.
>>--
>>Tibor Karaszi, SQL Server MVP
>>Archive at: http://groups.google.com/groups?
>oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
>> Thanks Tibor for a quick answer!
>> The recovery model for the database is full.
>> mdf file is not accesible as well as the last
>transaction
>> log backup( they reside on the same drive and they are
>not
>> backed up to a tape before the failure ).
>> I can not perform the transaction log backup since the
>> problem is actually OS not the hardware.It looks like
>that
>> I will have to go to a last full db backup unless I can
>> somehow apply the transaction log (ldf file).
>> In other words I have full db backup of Mydb.BAK and
ldf
>> file of the same db. Since the transaction log backup
>> was performed once I am wondering if that made the log
>(ldf)
>> invalid. I am backing the log as a part of the db
>> maintenance plan.
>> Thanks in advance!
>>
>> >--Original Message--
>> >Mirna,
>> >
>> >> What is the best aproach for recovering the db ?
>> >
>> >Are you currently dong log backups? What is the
>recovery
>> model for the database. If it is simple,
>> >then you can only recovery up to the latest database
>> backup. If it is full and you also do log
>> >backup, do a log backup now using the NO_TRUNCATE
>> parameter and then restore the latest database
>> >backup and all subsequent log backups (including this
>> last one).
>> >
>> >
>> >> Can I restore the db from the last full db backup
>and
>> >> then backup the curent log and apply to it ?
>> >
>> >No, as soon as you restored you lost the stuff in the
>log
>> file. Always start with performing a log
>> >backup as I explained above.
>> >
>> >
>> >> The only thing that has happened beetween the full
db
>> >> backup and crash is one transaction log backup.
>> >
>> >Then it seems that you are in good shape. Do this last
>> log backup and then the restore stuff.
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >Archive at: http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >
>> >
>> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> the drive with the data file is corupted, however
the
>> >> drive where the transaction log is located is fine.
>> >>
>> >> What is the best aproach for recovering the db ?
>> >> Can I restore the db from the last full db backup
>and
>> >> then backup the curent log and apply to it ?
>> >> The only thing that has happened beetween the full
db
>> >> backup and crash is one transaction log backup.
>> >>
>> >> Any view is apreciated!
>> >
>> >
>> >.
>> >
>>
>>.
>.
>|||> Is there a way that ldf file can be used to recover the
> database if the transaction log backup and .mdf file is
> lost ?
No. As I described, you have a missing log backup.
> Does Microsoft has any utility that can recover
> what has been flushed to mdf file during the transaction
> log backup ?
This is not how things work ("flushed to mdf file"). Read about transaction log handling and backup
and restore in Books Online.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:b43501c3b8f1$7cf00640$a601280a@.phx.gbl...
> Is there a way that ldf file can be used to recover the
> database if the transaction log backup and .mdf file is
> lost ? Does Microsoft has any utility that can recover
> what has been flushed to mdf file during the transaction
> log backup ?
> Any view is apreciated
> >--Original Message--
> >Thanks Tibor,
> >
> >I understand what you are saying.. too bad that we will
> >have to go to a last full db backup. Thank You again!
> >
> >>--Original Message--
> >> The recovery model for the database is full.
> >> mdf file is not accesible as well as the last
> >transaction
> >> log backup
> >>
> >>Ouch. Then you would not be able to use a log backup
> even
> >if you managed to do one at this point in
> >>time. This is because the log is emptied each time you
> do
> >a log backup, so now you are in a
> >>situation where you did a log backup and that is lost.
> >You have a "hole" in your sequence of log
> >>records.
> >>
> >>If it weren't for that prior log backup is lost, you
> >could actually product this last one log backup
> >>event though the OS install is damaged (or if SQL Server
> >install is damaged). You would "fake"
> >>another database on another SQL Server, slide in that
> log
> >file (ldf) into that install and then do
> >>the log backup using NO_TRUNCATE on that installation.
> >There's a KB article on that subject. This is
> >>only FYI as this unfortunately won't help you because of
> >that missing prior log backup.
> >>
> >>--
> >>Tibor Karaszi, SQL Server MVP
> >>Archive at: http://groups.google.com/groups?
> >oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >>"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> >message
> >>news:0ad701c3b8df$3be49600$a401280a@.phx.gbl...
> >> Thanks Tibor for a quick answer!
> >>
> >> The recovery model for the database is full.
> >> mdf file is not accesible as well as the last
> >transaction
> >> log backup( they reside on the same drive and they are
> >not
> >> backed up to a tape before the failure ).
> >> I can not perform the transaction log backup since the
> >> problem is actually OS not the hardware.It looks like
> >that
> >> I will have to go to a last full db backup unless I can
> >> somehow apply the transaction log (ldf file).
> >> In other words I have full db backup of Mydb.BAK and
> ldf
> >> file of the same db. Since the transaction log backup
> >> was performed once I am wondering if that made the log
> >(ldf)
> >> invalid. I am backing the log as a part of the db
> >> maintenance plan.
> >>
> >> Thanks in advance!
> >>
> >>
> >> >--Original Message--
> >> >Mirna,
> >> >
> >> >> What is the best aproach for recovering the db ?
> >> >
> >> >Are you currently dong log backups? What is the
> >recovery
> >> model for the database. If it is simple,
> >> >then you can only recovery up to the latest database
> >> backup. If it is full and you also do log
> >> >backup, do a log backup now using the NO_TRUNCATE
> >> parameter and then restore the latest database
> >> >backup and all subsequent log backups (including this
> >> last one).
> >> >
> >> >
> >> >> Can I restore the db from the last full db backup
> >and
> >> >> then backup the curent log and apply to it ?
> >> >
> >> >No, as soon as you restored you lost the stuff in the
> >log
> >> file. Always start with performing a log
> >> >backup as I explained above.
> >> >
> >> >
> >> >> The only thing that has happened beetween the full
> db
> >> >> backup and crash is one transaction log backup.
> >> >
> >> >Then it seems that you are in good shape. Do this last
> >> log backup and then the restore stuff.
> >> >--
> >> >Tibor Karaszi, SQL Server MVP
> >> >Archive at: http://groups.google.com/groups?
> >> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> >
> >> >
> >> >"Mirna" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:09c601c3b8d3$d5a16400$a501280a@.phx.gbl...
> >> >> Hi,
> >> >>
> >> >> the drive with the data file is corupted, however
> the
> >> >> drive where the transaction log is located is fine.
> >> >>
> >> >> What is the best aproach for recovering the db ?
> >> >> Can I restore the db from the last full db backup
> >and
> >> >> then backup the curent log and apply to it ?
> >> >> The only thing that has happened beetween the full
> db
> >> >> backup and crash is one transaction log backup.
> >> >>
> >> >> Any view is apreciated!
> >> >
> >> >
> >> >.
> >> >
> >>
> >>
> >>.
> >>
> >.
> >
Drive Fragmentation
The drive on my SQL 2K Server is highly fragmented. Do I
need to shut down SQL before I run the defrag or can I
just run it?From an old post...
There are 2 kinds of fragmentation. You have fragmentation at the OS (or
file) level and at the Table level inside the DB file(s). The first kind
(OS) can be fixed with a product like Disk Keeper but you have to stop SQL
Server for it to work. The .MDF will only be fragmented if you didn't have
enough contiguous space when you created it or if it was resized at a later
date and there wasn't a contiguous space. More than likely it is at the
table level. If you have clustered indexes you can use DBCC DBREINDEX or
DBCC INDEXDEFRAG to fix that.
HTH
Ryan Waight, MCDBA, MCSE
"JUSBYS" <anonymous@.discussions.microsoft.com> wrote in message
news:061901c3a545$4c761780$a001280a@.phx.gbl...
> The drive on my SQL 2K Server is highly fragmented. Do I
> need to shut down SQL before I run the defrag or can I
> just run it?
>|||I was told from a defrag too manufacturer that you can defrag it without
shutting down SQL Server. I still don't defrag without shutting down,
though... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"JUSBYS" <anonymous@.discussions.microsoft.com> wrote in message
news:061901c3a545$4c761780$a001280a@.phx.gbl...
> The drive on my SQL 2K Server is highly fragmented. Do I
> need to shut down SQL before I run the defrag or can I
> just run it?
>|||If this fragmentation is of the OS type and you have
enough room, you can try a utility from
www.sysinternals.com called 'contig'. This will defrag
individual database files without needing to shut down the
database. I use it here to defrag my Oracle and SQL
Server databases and I've never had a problem with it.
That's said, every environment is different so test it
EXTENSIVELY and make sure your backup/recovery plan is
bulletproof before turning on a live system!
HTH
>--Original Message--
>The drive on my SQL 2K Server is highly fragmented. Do I
>need to shut down SQL before I run the defrag or can I
>just run it?
>
>.
>
need to shut down SQL before I run the defrag or can I
just run it?From an old post...
There are 2 kinds of fragmentation. You have fragmentation at the OS (or
file) level and at the Table level inside the DB file(s). The first kind
(OS) can be fixed with a product like Disk Keeper but you have to stop SQL
Server for it to work. The .MDF will only be fragmented if you didn't have
enough contiguous space when you created it or if it was resized at a later
date and there wasn't a contiguous space. More than likely it is at the
table level. If you have clustered indexes you can use DBCC DBREINDEX or
DBCC INDEXDEFRAG to fix that.
HTH
Ryan Waight, MCDBA, MCSE
"JUSBYS" <anonymous@.discussions.microsoft.com> wrote in message
news:061901c3a545$4c761780$a001280a@.phx.gbl...
> The drive on my SQL 2K Server is highly fragmented. Do I
> need to shut down SQL before I run the defrag or can I
> just run it?
>|||I was told from a defrag too manufacturer that you can defrag it without
shutting down SQL Server. I still don't defrag without shutting down,
though... :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"JUSBYS" <anonymous@.discussions.microsoft.com> wrote in message
news:061901c3a545$4c761780$a001280a@.phx.gbl...
> The drive on my SQL 2K Server is highly fragmented. Do I
> need to shut down SQL before I run the defrag or can I
> just run it?
>|||If this fragmentation is of the OS type and you have
enough room, you can try a utility from
www.sysinternals.com called 'contig'. This will defrag
individual database files without needing to shut down the
database. I use it here to defrag my Oracle and SQL
Server databases and I've never had a problem with it.
That's said, every environment is different so test it
EXTENSIVELY and make sure your backup/recovery plan is
bulletproof before turning on a live system!
HTH
>--Original Message--
>The drive on my SQL 2K Server is highly fragmented. Do I
>need to shut down SQL before I run the defrag or can I
>just run it?
>
>.
>
Drive Failure
I had a drive failure last night. Sql Server was loaded on the drive that
failed (which also contained the OS). Fortunately, the log files were on
another drive and the databases on another and both of these drives are OK.
Does anyone know the correct procedure after reloading the OS and Sql Server
2000 on the new drive, for reattaching the databases to this new instance of
Sql Server.
Thanks for any help.
Brian
Try to attach the databases again,a dn then check the status of the
database. If its working you gotta be ok, if not you might try to check the
DBCC command to fixed the errors. dont know how long i will stay on the
wire this evening (yes, i am in europe ;-) ), but i thin if there is an
error all other guys here are able to help you.
If that all doenst work, i hope for you you gotta working backup
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Brian" <Brian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian
|||Hi,
Other methodology:- This work good for me once.
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
If any of the database is in suspect status, then check the cause for the
error in SQL server Error log. If it is critical you may
need to restore the database from Backup
Thanks
Hari
SQL Server MVP
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian
failed (which also contained the OS). Fortunately, the log files were on
another drive and the databases on another and both of these drives are OK.
Does anyone know the correct procedure after reloading the OS and Sql Server
2000 on the new drive, for reattaching the databases to this new instance of
Sql Server.
Thanks for any help.
Brian
Try to attach the databases again,a dn then check the status of the
database. If its working you gotta be ok, if not you might try to check the
DBCC command to fixed the errors. dont know how long i will stay on the
wire this evening (yes, i am in europe ;-) ), but i thin if there is an
error all other guys here are able to help you.
If that all doenst work, i hope for you you gotta working backup
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Brian" <Brian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian
|||Hi,
Other methodology:- This work good for me once.
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
If any of the database is in suspect status, then check the cause for the
error in SQL server Error log. If it is critical you may
need to restore the database from Backup
Thanks
Hari
SQL Server MVP
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian
Drive Failure
I had a drive failure last night. Sql Server was loaded on the drive that
failed (which also contained the OS). Fortunately, the log files were on
another drive and the databases on another and both of these drives are OK.
Does anyone know the correct procedure after reloading the OS and Sql Server
2000 on the new drive, for reattaching the databases to this new instance of
Sql Server.
Thanks for any help.
BrianTry to attach the databases again,a dn then check the status of the
database. If its working you gotta be ok, if not you might try to check the
DBCC command to fixed the errors. dont know how long i will stay on the
wire this evening (yes, i am in europe ;-) ), but i thin if there is an
error all other guys here are able to help you.
If that all doenst work, i hope for you you gotta working backup
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Brian" <Brian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian|||Hi,
Other methodology:- This work good for me once.
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
If any of the database is in suspect status, then check the cause for the
error in SQL server Error log. If it is critical you may
need to restore the database from Backup
Thanks
Hari
SQL Server MVP
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian
failed (which also contained the OS). Fortunately, the log files were on
another drive and the databases on another and both of these drives are OK.
Does anyone know the correct procedure after reloading the OS and Sql Server
2000 on the new drive, for reattaching the databases to this new instance of
Sql Server.
Thanks for any help.
BrianTry to attach the databases again,a dn then check the status of the
database. If its working you gotta be ok, if not you might try to check the
DBCC command to fixed the errors. dont know how long i will stay on the
wire this evening (yes, i am in europe ;-) ), but i thin if there is an
error all other guys here are able to help you.
If that all doenst work, i hope for you you gotta working backup
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Brian" <Brian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian|||Hi,
Other methodology:- This work good for me once.
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
If any of the database is in suspect status, then check the cause for the
error in SQL server Error log. If it is critical you may
need to restore the database from Backup
Thanks
Hari
SQL Server MVP
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian
Drive Failure
I had a drive failure last night. Sql Server was loaded on the drive that
failed (which also contained the OS). Fortunately, the log files were on
another drive and the databases on another and both of these drives are OK.
Does anyone know the correct procedure after reloading the OS and Sql Server
2000 on the new drive, for reattaching the databases to this new instance of
Sql Server.
Thanks for any help.
--
BrianTry to attach the databases again,a dn then check the status of the
database. If its working you gotta be ok, if not you might try to check the
DBCC command to fixed the errors. don´t know how long i will stay on the
wire this evening (yes, i am in europe ;-) ), but i thin if there is an
error all other guys here are able to help you.
If that all doens´t work, i hope for you you gotta working backup :)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Brian" <Brian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian|||Hi,
Other methodology:- This work good for me once.
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
If any of the database is in suspect status, then check the cause for the
error in SQL server Error log. If it is critical you may
need to restore the database from Backup
Thanks
Hari
SQL Server MVP
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian
failed (which also contained the OS). Fortunately, the log files were on
another drive and the databases on another and both of these drives are OK.
Does anyone know the correct procedure after reloading the OS and Sql Server
2000 on the new drive, for reattaching the databases to this new instance of
Sql Server.
Thanks for any help.
--
BrianTry to attach the databases again,a dn then check the status of the
database. If its working you gotta be ok, if not you might try to check the
DBCC command to fixed the errors. don´t know how long i will stay on the
wire this evening (yes, i am in europe ;-) ), but i thin if there is an
error all other guys here are able to help you.
If that all doens´t work, i hope for you you gotta working backup :)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Brian" <Brian@.discussions.microsoft.com> schrieb im Newsbeitrag
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian|||Hi,
Other methodology:- This work good for me once.
Easy way to recover the database after OS crash is,
1. After OS installation, Copy all .MDF and .LDF files to a new folder
(safe location)
2. Install SQL server and same Service packs (as old) in the identical
folder (Same as old installation)
3. Stop the SQL server
4. Copy the .MDF and .LDF files (took in step 1) to the same folders (Same
as old installation).
5. Start SQL server
Now login to query analyzer or enterprise manager and confirm all the
databases are online.
If any of the database is in suspect status, then check the cause for the
error in SQL server Error log. If it is critical you may
need to restore the database from Backup
Thanks
Hari
SQL Server MVP
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:14C9F68D-E6D1-412D-81A9-8EDFFA42C4B6@.microsoft.com...
>I had a drive failure last night. Sql Server was loaded on the drive that
> failed (which also contained the OS). Fortunately, the log files were on
> another drive and the databases on another and both of these drives are
> OK.
> Does anyone know the correct procedure after reloading the OS and Sql
> Server
> 2000 on the new drive, for reattaching the databases to this new instance
> of
> Sql Server.
> Thanks for any help.
> --
> Brian
Subscribe to:
Posts (Atom)