Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
Joe
Joe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQL...scue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>

Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
JoeJoe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQ...escue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>

Dropping a Database

Hi all,
Is there a log entry that would identify the user that dropped a database
out of my server? I have two people here on my dev server that are at each
others throat because they are blaming each other.
I would like to restore peace here.
TIA,
JoeJoe,
Might be able to determine that *post* op with auditing by using a
third-party transaction log viewer. I.e.,
Trial editions available for download:
http://www.lumigent.com/go/google/
or
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
HTH
Jerry
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:08444183-0540-4A4B-81A4-FD7C01D10838@.microsoft.com...
> Hi all,
> Is there a log entry that would identify the user that dropped a database
> out of my server? I have two people here on my dev server that are at
> each
> others throat because they are blaming each other.
> I would like to restore peace here.
> TIA,
> Joe
>sql

Droping Log File

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.

Drop Primary Transaction Log File

Hi All,
I attemping to move a transaction log file online to another location. I performed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file cannot be removed from a database".
Is it possible to remove the original log file?
Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?
|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?

Drop Primary Transaction Log File

Hi All,
I attemping to move a transaction log file online to another location. I per
formed the following steps;
- Created a second(log_name2) log file.
- ran DBCC Shrinkfile(log_name1,emptyfile)
- alter database mydb
remove file log_name1
* However, I receive the following error 5020 "The primary data or log file
cannot be removed from a database".
Is it possible to remove the original log file?Mike,
You might have to take the database offline by doing:
1.sp_detach_db
2.Then attach the database back using sp_attach_db but this time mentioning
the new path for ldf file.Refer BooksOnLine for syntax of the two commands.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?|||Hi,
In your case , I feel that the only solution is detach and attach the
databases.
Steps:
1. detach the database using sp_detach_db
2. copy the LDF to new location
3. Attach it back using sp_attach_db
Have a look into the below link:
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_03_9dbn.asp
Thanks
Hari
MCDBA
"MikeDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:94423849-18A7-4E5F-9296-ED3EBDD3CCD3@.microsoft.com...
> Hi All,
> I attemping to move a transaction log file online to another location. I
performed the following steps;
> - Created a second(log_name2) log file.
> - ran DBCC Shrinkfile(log_name1,emptyfile)
> - alter database mydb
> remove file log_name1
> * However, I receive the following error 5020 "The primary data or log
file cannot be removed from a database".
> Is it possible to remove the original log file?

Drop large transaction log file and create new

Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.

It's very urgent because we need to clean up space on NT server tonight.

Thanks,
AttilaOriginally posted by horvata
Team,
Could you sned me some idea, how can I remove or replace transaction log file on MS SQL 2000 server? Data size is about 2GB but trx log is more than 35GB. This database include only static data...there are no transactions. I have about 5 million records in one table and there are 13 tables with 40 000-50 000 records.

It's very urgent because we need to clean up space on NT server tonight.

Thanks,
Attila

See: http://dbforums.com/t546372.html|||Thanks a lot for your help.|||Originally posted by DBA
See: http://dbforums.com/t546372.html

Whou much time you spend to make you database full backup ??
Wich type of backup do you do ?

If you log is no longer used (because you data is static) you can use the command bellow

sp_detach_db <dbname>

GO

CREATE DATABASE <dbname>
ON PRIMARY (FILENAME = '<path>.dbname.extension')
FOR ATTACH
GO

Ps: Make 2 full backups of you database before do this. On filename choose the path of you datafile <only>, forget you logfile.

Jorge|||Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.

Thanks,
Attila|||Originally posted by horvata
Just for your information...
I created a new database and I exported old objects into new db. After that I dropped old database and I created new database with the original name and then I exported back db objects. Now I have a DB Maitenence Plan which is working fine and there are no issue with log file size.

Thanks,
Attila

Hi Attila, I discover another way to do this.

Ps:Allways execute a full backup before.

First execute
EXEC sp_detach_db 'database_name', 'true'

Rename your physical log file on Operation system
After this execute the following command.

EXEC sp_attach_single_file_db @.dbname = 'database_name',
@.physname = 'path\database_name.extension'

This command works. I haver already done this.

Jorge Demattos
Bank of America.

Drop and Recreate subscription

I need to drop and recreate few subscriptions in transactional publication
Do I need to worry about log marker issues ?
Do I need to set the primary and replicate databases in 'DBO use only'
The Primary and Replicate databases are being accessed all the time.To your two questions, the answers are

No and No.

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...
>
>

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...
>
>

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.
>>

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.

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!
> >> >
> >> >
> >> >.
> >> >
> >>
> >>
> >>.
> >>
> >.
> >

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

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

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