Restore deleted table from log?

Hi,
If a table was deleted, is it possible to roll back the deletion with the
transaction log?
ThanksCheck out www.lumigent.com.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"BDB" <bdb@.reply.to.group.com> wrote in message
news:%23iSygiyYGHA.508@.TK2MSFTNGP02.phx.gbl...
Hi,
If a table was deleted, is it possible to roll back the deletion with the
transaction log?
Thanks|||Okay this is what I have:
BACKUP LOG mydb
TO DISK= 'mydblog200604180415.bak'
WITH NO_TRUNCATE, INIT
go
use master
go
RESTORE LOG mydb
FROM DISK= 'mydblog200604180415.bak'
WITH STOPAT = 'Apr 18, 2006 3:30 PM'
I get this error:
Msg 3117, Level 16, State 1, Line 2
The log or differential backup cannot be restored because no files are ready
to rollforward.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
Can anyone help?|||"BDB" <bdb@.reply.to.group.com> wrote in message
news:%23iSygiyYGHA.508@.TK2MSFTNGP02.phx.gbl...
> Hi,
> If a table was deleted, is it possible to roll back the deletion with the
> transaction log?
You can restore the database and then all logs up to the point in time just
before it was deleted.
If you mean recover the table w/o doing that, http://www.lumigent.com/ may
help.
> Thanks
>|||When you restore log, you first need to restore database, then the log backups, in order. I suggest
you read some in Books Online about backup and restore to get a grip on how this work. Also, you
might want to check out: http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BDB" <bdb@.reply.to.group.com> wrote in message news:%23$zI%23BzYGHA.428@.TK2MSFTNGP02.phx.gbl...
> Okay this is what I have:
> BACKUP LOG mydb
> TO DISK= 'mydblog200604180415.bak'
> WITH NO_TRUNCATE, INIT
> go
> use master
> go
> RESTORE LOG mydb
> FROM DISK= 'mydblog200604180415.bak'
> WITH STOPAT = 'Apr 18, 2006 3:30 PM'
> I get this error:
> Msg 3117, Level 16, State 1, Line 2
> The log or differential backup cannot be restored because no files are ready to rollforward.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE LOG is terminating abnormally.
>
> Can anyone help?
>|||Just keep in mind the below point in the article to minimise impact on
production db.
* Restore into another database and copy the affected data into the
production database.
Thanks
Ajay Rengunthwarsql

Restore DB without Transaction Log

Hi,

How can I restore a DB without TRN log. As database transaction logs grows too much and I only want to restore the database.

Thanks

Shafiq

If you only restore the transaction log then the database will be transactionally inconsistent - the transaction log is an integral part of the backup.

What's the scenario context here?

BTW, what size DB and transaction log are we talking about here?

Thanks

|||

1. You must change your recovery model to simple and the backups wont record the transaction logs, this is the easiest way, but You shouldnt restore a backup without transaction logs, its supposed for you to have the info upto date until the failover.

|||

You should be wary about just going to simple recovery model. This is not suitable for production systems where loss of recent changes (in the event of a problem) is not acceptable.

This is because after a data backup in the simple recovery model, the transaction log is truncated. This means that its only possible to restore back to the time the last backup was completed - its not possible to do a point-in-time restore up to just before the problem occured so all work between those two points is lost.

See the SQL Server 2005 BOL entry for 'Overview of Simple Recovery' for more details.

Thanks

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisR
Hi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>
|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>
|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisRHi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Is it possible in sql2k to restore a db without restoring the transaction
>> log? If so, how?
>> TIA, ChrisR
>|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
--
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> If you have the FULL Database backup you could restore the full database.
>> See RESTORE DATABASE command in books online.
>> Log restore will help you to recover the database till POINT IN TIME.
>> Thanks
>> Hari
>> SQL Server MVP
>> "ChrisR" <noemail@.bla.com> wrote in message
>> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>> Is it possible in sql2k to restore a db without restoring the
>> transaction log? If so, how?
>> TIA, ChrisR
>>
>

restore db without the log?

Is it possible in sql2k to restore a db without restoring the transaction
log? If so, how?
TIA, ChrisRHi,
If you have the FULL Database backup you could restore the full database.
See RESTORE DATABASE command in books online.
Log restore will help you to recover the database till POINT IN TIME.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
> Is it possible in sql2k to restore a db without restoring the transaction
> log? If so, how?
> TIA, ChrisR
>|||Hari, I have a FULL backup and want to restore the full db, minus the 20 gig
transaction log.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> If you have the FULL Database backup you could restore the full database.
> See RESTORE DATABASE command in books online.
> Log restore will help you to recover the database till POINT IN TIME.
> Thanks
> Hari
> SQL Server MVP
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OMubP%23LiFHA.3476@.TK2MSFTNGP10.phx.gbl...
>|||If you mean without creating the transaction log file then no. The restore
process from a FULL backup will always rebuild the db and log files exactly
as they were when you made the backup. You can detach the db and attach it
with sing_file_db and it will create a new log file. I am not sure how
large it makes the new file though. It's probably the size you originally
created it at or explicitly did an alter file on it.
Andrew J. Kelly SQL MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23dKNBNMiFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Hari, I have a FULL backup and want to restore the full db, minus the 20
> gig transaction log.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ezKnNAMiFHA.392@.TK2MSFTNGP10.phx.gbl...
>sql

Restore db in cluster environment

Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
Your T: drive have not been made a dependency of SQL Server. You need to do
the following:
1) Make the SQL Server resource offline.
2) Move the T: drive resource into the SQL group if it is not already
there.
3) Bring up the properties of the SQL Server resource.
4) Make the T: drive a dependency of SQL Server.
5) Bring the SQL group online.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"mecn" <mecn2002@.yahoo.com> wrote in message
news:eybFs1luHHA.4916@.TK2MSFTNGP04.phx.gbl...
Hi,
I got this error when I move the log file to a diff location in the cluster
environment.
Any ideas and solution?
Thanks
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
formatted files on which the cluster resource of the server has a dependency
can be used.
|||Got it, Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23A3R85luHHA.536@.TK2MSFTNGP06.phx.gbl...
> Your T: drive have not been made a dependency of SQL Server. You need to
> do
> the following:
> 1) Make the SQL Server resource offline.
> 2) Move the T: drive resource into the SQL group if it is not already
> there.
> 3) Bring up the properties of the SQL Server resource.
> 4) Make the T: drive a dependency of SQL Server.
> 5) Bring the SQL group online.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eybFs1luHHA.4916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I got this error when I move the log file to a diff location in the
> cluster
> environment.
> Any ideas and solution?
> Thanks
>
> Msg 5184, Level 16, State 2, Line 1
> Cannot use file 'T:\Logs\myrpdLog.ldf' for clustered server. Only
> formatted files on which the cluster resource of the server has a
> dependency
> can be used.
>
>

Restore db and logs from device to a point in time

When restoring a database and log files from a device the point in time
option disappears. How do I restore from a device to a point in time? Is
there an easy way to restore multiple logs from a device? It seems like
doing them one at a time is the only option and it's quite a pain.I recommend that you familiar yourself with the RESTORE command so you can
do this from Query Analyzer.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"David Morrison" <me@.nospam.com> wrote in message
news:uof2WquuDHA.1596@.TK2MSFTNGP10.phx.gbl...
> When restoring a database and log files from a device the point in time
> option disappears. How do I restore from a device to a point in time? Is
> there an easy way to restore multiple logs from a device? It seems like
> doing them one at a time is the only option and it's quite a pain.
>

Restore Db

Hi ,
When I perform a restore transaction log on my server , I enounter an
issue saying that my db get lock and the restore transaction failed. How can
I set to no lock the database ? It is possible ? I am using the command below
to execute my restore log process. Actually what is the "with stanby" means ?
Am I doing the right command ? Please advise.
RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby = 'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
--
Travis Tan> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed.
You cannot have any connections in the database when you do RESTORE. This includes the connection
from where you run the RESTORE command.
> How can
> I set to no lock the database ? It is possible ?
No, you need to make sure that there are no users in the database.
> Actually what is the "with stanby" means ?
It is documented in Books Online. Read about the RESTORE LOG command and you will find it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:FEBA7216-7152-4605-B8B3-85C2C8C6C274@.microsoft.com...
> Hi ,
> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed. How can
> I set to no lock the database ? It is possible ? I am using the command below
> to execute my restore log process. Actually what is the "with stanby" means ?
> Am I doing the right command ? Please advise.
> RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby => 'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
> --
> Travis Tan|||Do
Alter database <dbname> set single_user with rollback immediate
Now Restore database.
now run
Alter database <dbname> multi_user
Regards
Amish Shahsql

restore db

Hi, I'm trying to restore tran log and the server returns error nb. 4330:
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Please, dont you know, where the problem is?
thanks for any advice...Probably the logs have to applied in order that they were dumped to the last full backup...

Or are you trying to last full back up and have the wrong file?|||Of course I did it with right backup and in a right order...|||Originally posted by Kubas
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.

Sorry, I didn't know...you need to supply for information...

My mind reader machine is broken at the moment...

Can you show us your restore code?

Are the file located on different drives? How many logs did you have to apply?

Is the process automated?

Do you map and unmap the drives after the restore?|||Nevermind :)
I found out new fact that could cause the problem:

I made komplet backup of database before reinstalation SQL server. After that reinstalation a made backup of tran log..
Now I'm not able to load the tran log on the komplet database.

I loaded complet backup in EM with norecovery option.
Then in QA I tried to start this code:

use master
restore log Backup
from disk = 'F:\MSSQL\BACKUP\CallCentrum\CallCentrum_db_200310 100030.BAK'
with file = 2,
stopat ='3.11.2003 12:55 AM'
(tran log is in the same file as complete backup, therefore file =2)
In that moment I get back the error msg. nb. 4330

I think the problem is in reinstalation, although the pathes didnt change. The complete backup is made on another instalation of SQL Server then the tran log...

I hope I descibed it understandable..

Originally posted by Brett Kaiser
Sorry, I didn't know...you need to supply for information...

My mind reader machine is broken at the moment...

Can you show us your restore code?

Are the file located on different drives? How many logs did you have to apply?

Is the process automated?

Do you map and unmap the drives after the restore?

Restore Db

Hi ,
When I perform a restore transaction log on my server , I enounter an
issue saying that my db get lock and the restore transaction failed. How can
I set to no lock the database ? It is possible ? I am using the command below
to execute my restore log process. Actually what is the "with stanby" means ?
Am I doing the right command ? Please advise.
RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby =
'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
Travis Tan
> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed.
You cannot have any connections in the database when you do RESTORE. This includes the connection
from where you run the RESTORE command.

> How can
> I set to no lock the database ? It is possible ?
No, you need to make sure that there are no users in the database.

> Actually what is the "with stanby" means ?
It is documented in Books Online. Read about the RESTORE LOG command and you will find it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:FEBA7216-7152-4605-B8B3-85C2C8C6C274@.microsoft.com...
> Hi ,
> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed. How can
> I set to no lock the database ? It is possible ? I am using the command below
> to execute my restore log process. Actually what is the "with stanby" means ?
> Am I doing the right command ? Please advise.
> RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby =
> 'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
> --
> Travis Tan
|||Do
Alter database <dbname> set single_user with rollback immediate
Now Restore database.
now run
Alter database <dbname> multi_user
Regards
Amish Shah

Restore Db

Hi ,
When I perform a restore transaction log on my server , I enounter an
issue saying that my db get lock and the restore transaction failed. How can
I set to no lock the database ? It is possible ? I am using the command belo
w
to execute my restore log process. Actually what is the "with stanby" means
?
Am I doing the right command ? Please advise.
RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby =
'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
--
Travis Tan> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed.
You cannot have any connections in the database when you do RESTORE. This in
cludes the connection
from where you run the RESTORE command.

> How can
> I set to no lock the database ? It is possible ?
No, you need to make sure that there are no users in the database.

> Actually what is the "with stanby" means ?
It is documented in Books Online. Read about the RESTORE LOG command and you
will find it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:FEBA7216-7152-4605-B8B3-85C2C8C6C274@.microsoft.com...
> Hi ,
> When I perform a restore transaction log on my server , I enounter an
> issue saying that my db get lock and the restore transaction failed. How c
an
> I set to no lock the database ? It is possible ? I am using the command be
low
> to execute my restore log process. Actually what is the "with stanby" mean
s ?
> Am I doing the right command ? Please advise.
> RESTORE LOG PDDIRECTLINK FROM disk = @.FileName with standby =
> 'C:\LaiDBBackup\Undo\Undo_PdDirectLink.dat'
> --
> Travis Tan|||Do
Alter database <dbname> set single_user with rollback immediate
Now Restore database.
now run
Alter database <dbname> multi_user
Regards
Amish Shahsql

Restore database without transaction log

Hi,
I lost transaction log of SQL 2000 database.
How i can restore the database without transaction log ?
What is restore sequence ?
I can't attach the database only:it requires log file
--
Regards
AlexAlex
Take a look ate:
sp_attach_single_file_db
This may be what you are after.
--
-- cranfield, DBA
"alext12" wrote:
> Hi,
> I lost transaction log of SQL 2000 database.
> How i can restore the database without transaction log ?
> What is restore sequence ?
> I can't attach the database only:it requires log file
> --
> Regards
> Alex|||cranfield ,
we receive error something about NLS code is incorrect
--
Regards
Alex
"Cranfield" wrote:
> Alex
> Take a look ate:
> sp_attach_single_file_db
>
> This may be what you are after.
> --
> -- cranfield, DBA
>
> "alext12" wrote:
> > Hi,
> >
> > I lost transaction log of SQL 2000 database.
> > How i can restore the database without transaction log ?
> > What is restore sequence ?
> > I can't attach the database only:it requires log file
> > --
> > Regards
> > Alex|||Have you got a backup of the DB? Without a DB backup you can't restore
anything, regardless of whether you have tlog backups or not.
When you say you "lost" the transaction log, do you mean it's corrupt?
or you've stopped the SQL instance, misplaced the ldf file and now the
server can't recover the DB on start up? or something else?
--
Mike
alext12 wrote:
> cranfield ,
> we receive error something about NLS code is incorrect

Restore database without transaction log

Hi,
I lost transaction log of SQL 2000 database.
How i can restore the database without transaction log ?
What is restore sequence ?
I can't attach the database only:it requires log file
Regards
Alex
Alex
Take a look ate:
sp_attach_single_file_db
This may be what you are after.
-- cranfield, DBA
"alext12" wrote:

> Hi,
> I lost transaction log of SQL 2000 database.
> How i can restore the database without transaction log ?
> What is restore sequence ?
> I can't attach the database only:it requires log file
> --
> Regards
> Alex
|||cranfield ,
we receive error something about NLS code is incorrect
Regards
Alex
"Cranfield" wrote:
[vbcol=seagreen]
> Alex
> Take a look ate:
> sp_attach_single_file_db
>
> This may be what you are after.
> --
> -- cranfield, DBA
>
> "alext12" wrote:
sql

Restore database without transaction log

Hi,
I lost transaction log of SQL 2000 database.
How i can restore the database without transaction log ?
What is restore sequence ?
I can't attach the database only:it requires log file
--
Regards
AlexAlex
Take a look ate:
sp_attach_single_file_db
This may be what you are after.
-- cranfield, DBA
"alext12" wrote:

> Hi,
> I lost transaction log of SQL 2000 database.
> How i can restore the database without transaction log ?
> What is restore sequence ?
> I can't attach the database only:it requires log file
> --
> Regards
> Alex|||cranfield ,
we receive error something about NLS code is incorrect
--
Regards
Alex
"Cranfield" wrote:
[vbcol=seagreen]
> Alex
> Take a look ate:
> sp_attach_single_file_db
>
> This may be what you are after.
> --
> -- cranfield, DBA
>
> "alext12" wrote:
>|||Have you got a backup of the DB? Without a DB backup you can't restore
anything, regardless of whether you have tlog backups or not.
When you say you "lost" the transaction log, do you mean it's corrupt?
or you've stopped the SQL instance, misplaced the ldf file and now the
server can't recover the DB on start up? or something else?
Mike
alext12 wrote:
> cranfield ,
> we receive error something about NLS code is incorrect

Restore database without Tran Log

Can I restore a user created database, from a normal SQL Server Full backup,
without restoring the log? My problem is that I have a user who backed up a
database, deleted it, and now has to restore it again (on another server,
when the old server no longer exists) that doesn't have enough disk space to
accomindate the transaction log.
Thanks in advance.
PaulSQL Server will create the transaction log the size it need to, when you
issue the restore. My experience is that it will be the same as when you did
the backup, but I can imagine that it can potentially grow during the
restore operation (pure speculation here).
However, this is not anything you can influence. Just run RESTORE
FILELISTONY and make sure you have the amount of disk that it reports (sum
the files). Note that you can use the MOVE option to specify some other
disk(/path) for any of the files.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Can I restore a user created database, from a normal SQL Server Full

---

backup,

quote:

---

> without restoring the log? My problem is that I have a user who backed up

---

a

quote:

---

> database, deleted it, and now has to restore it again (on another server,
> when the old server no longer exists) that doesn't have enough disk space

---

to

quote:

---

> accomindate the transaction log.
> Thanks in advance.
> Paul
>

---

|||Unfortunately, he doesn't have enough disk to restore to the original
transaction log file size on any drive. The only solution I can think of is
to do a filegroup restore with NORECOVERY. Set database status = 32768, or
Emergency Mode, restarted SQL Server. Create another database, and use DDL
scripts and DTS packages to transfer over all the objects. If there is a
cleaner way to do it, I would like to know.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:utD36vP5DHA.2580@.TK2MSFTNGP11.phx.gbl...

quote:

---

> SQL Server will create the transaction log the size it need to, when you
> issue the restore. My experience is that it will be the same as when you

---

did

quote:

---

> the backup, but I can imagine that it can potentially grow during the
> restore operation (pure speculation here).
> However, this is not anything you can influence. Just run RESTORE
> FILELISTONY and make sure you have the amount of disk that it reports (sum
> the files). Note that you can use the MOVE option to specify some other
> disk(/path) for any of the files.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>

---

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

---

>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> backup,
up[QUOTE]
> a
server,[QUOTE]
space[QUOTE]
> to
>

---

Restore database without Tran Log

Can I restore a user created database, from a normal SQL Server Full backup,
without restoring the log? My problem is that I have a user who backed up a
database, deleted it, and now has to restore it again (on another server,
when the old server no longer exists) that doesn't have enough disk space to
accomindate the transaction log.
Thanks in advance.
PaulSQL Server will create the transaction log the size it need to, when you
issue the restore. My experience is that it will be the same as when you did
the backup, but I can imagine that it can potentially grow during the
restore operation (pure speculation here).
However, this is not anything you can influence. Just run RESTORE
FILELISTONY and make sure you have the amount of disk that it reports (sum
the files). Note that you can use the MOVE option to specify some other
disk(/path) for any of the files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> Can I restore a user created database, from a normal SQL Server Full
backup,
> without restoring the log? My problem is that I have a user who backed up
a
> database, deleted it, and now has to restore it again (on another server,
> when the old server no longer exists) that doesn't have enough disk space
to
> accomindate the transaction log.
> Thanks in advance.
> Paul
>|||Unfortunately, he doesn't have enough disk to restore to the original
transaction log file size on any drive. The only solution I can think of is
to do a filegroup restore with NORECOVERY. Set database status = 32768, or
Emergency Mode, restarted SQL Server. Create another database, and use DDL
scripts and DTS packages to transfer over all the objects. If there is a
cleaner way to do it, I would like to know.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:utD36vP5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> SQL Server will create the transaction log the size it need to, when you
> issue the restore. My experience is that it will be the same as when you
did
> the backup, but I can imagine that it can potentially grow during the
> restore operation (pure speculation here).
> However, this is not anything you can influence. Just run RESTORE
> FILELISTONY and make sure you have the amount of disk that it reports (sum
> the files). Note that you can use the MOVE option to specify some other
> disk(/path) for any of the files.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> > Can I restore a user created database, from a normal SQL Server Full
> backup,
> > without restoring the log? My problem is that I have a user who backed
up
> a
> > database, deleted it, and now has to restore it again (on another
server,
> > when the old server no longer exists) that doesn't have enough disk
space
> to
> > accomindate the transaction log.
> >
> > Thanks in advance.
> >
> > Paul
> >
> >
>

Restore database with no log

I'm trying to restore a database backed up a production machine onto
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in

backup database CPTS to disk = 'd:\backups\cpts_backup_billeh.bak'
with no_log

I took the backup file and I'm able to restore it on my development
machine with

RESTORE DATABASE [CPTS] FILE = N'CPTS_Data' FROM DISK = N'D:\archives
\Citrix\CPTS\SupportingMaterials\cpts_backup_bille h.bak' WITH MOVE
'CPTS_Data' TO 'd:\databases\CPTS.mdf', MOVE 'CPTS_Log' TO 'd:
\databases\CPTS_log.ldf', MOVE 'CPTS_1_Log' TO 'd:\databases
\CPTS_1_log.ldf'

Note that I cannot additionally specify FILE = N'CPTS_Log' or FILE =
N'CPTS_1_Log' because I get an error.

The restore created the CPTS_Data file only and returned the message

Quote:

Originally Posted by

Quote:

Originally Posted by

>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.

The database cannot be recovered because the log was not restored.<<

What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.

Is there another way around this problem? I tried taking the the
resulting mdf file and attaching it using sp_attach_single_file_db but
this didn't work either.

Thanks,

Bill E.
Hollywood, FLI should add that both production and development environments are SQL
Server 2005|||Bill E. (billmiami2@.netscape.net) writes:

Quote:

Originally Posted by

I'm trying to restore a database backed up a production machine onto
my development machine. I don't want to restore the transaction logs
(there are two) because they are far too large and are unnecessary for
what I'm doing. I would be happy to simply create one new log in my
development environment. Therefore, I backed up the database using
the No_Log option as in
>...

Quote:

Originally Posted by

Quote:

Originally Posted by

>>>Processed 415664 pages for database 'CPTS', file 'CPTS_Data' on file 1.

The database cannot be recovered because the log was not restored.<<
>
What additional step should I take to create a log file? I thought
that No_Log was supposed to give me a truncated log file but it didn't
seem to do so.

Books Online says about NO_LOG:

In the context of a BACKUP DATABASE statement, specifies that a backup
will not contain any log. This equates to how file backups were created
before SQL Server 2005. A database backup created with NO_LOG equates
to a full set of file backups that contains no log records.

Under the full recovery model, NO_LOG is useful if you need to back up
data quickly, and you have a complete sequence of log backups of that
data.

The last paragraph implies that you are expected to apply the transaction
logs from elsewhere.

There is a reason why this does not work as you had expected: you
see, that log serves a purpose. A database backup is taken online, which
means that pages can be updated while it's working. Therefore the
backup must include log records, so that transactions that were committed
when the backup was running can be rolled forward. And more importantly,
transactions that had not yet been committed when the backup completed,
must be rolled back. Thus a database restored from a backup with no log
content is in a inconsistent state, and thus you are not permitted in.

I would suggest that you do a regular backup/restore, and then shrink
the log files once you have restored the database and set the recovery
mode to simple. A tip is to add WITH COPY_ONLY to the BACKUP command.
That prevents the backup from being recorded as a "real" backup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You're not being nosey. I don't know why this little PC was formatted
that way, but it was. It's a Windows x64 PC. I don't think that I'm
going to go through the trouble of reformatting the drives. This is
the first time that the issue has come arisen.

Bill

Restore Database With Move

OK. I have a good one. I have the following setup:
ServerA
Data on: C:\Data
Log on: C:\Logs
ServerB
System Databases on: D:\SysData
Data on: E:\Data
Indexes on : D:\Indexes
Log on: F:\Logs
So, what I want to do is restore a database from ServerA to ServerB. Placing the
files and data in the correct locations. I want the data in a filegroup called
DatabaseName_Data. Problem is when I restore to ServerB, the data always goes to
PRIMARY filegroup during restore. The only thing I can find is to manually
change the location of the data for each table in Enterprise Manager (YUCK!)
Isn't there an easier way?
Thanks for the help
DarrellYou cannot change file or filegroup layout using backup/restore (which basically gives you an image
of your database). Either use EM to move or do it from QA using more or less clever/complicated TSQL
scripts. You change location of a table by (re)creating a clustered index. For an index, you rebuild
the index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB. Placing the files and data in
> the correct locations. I want the data in a filegroup called DatabaseName_Data. Problem is when I
> restore to ServerB, the data always goes to PRIMARY filegroup during restore. The only thing I can
> find is to manually change the location of the data for each table in Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell|||Refer to "How to restore files to a new location (Transact-SQL)" in BOL...
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB.
> Placing the files and data in the correct locations. I want the data in a
> filegroup called DatabaseName_Data. Problem is when I restore to ServerB,
> the data always goes to PRIMARY filegroup during restore. The only thing I
> can find is to manually change the location of the data for each table in
> Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell

Restore Database w/Large Transaction Log File

I have a SQL Server 2000 database that is approximately 300 GB in size and I
auto grew the database transaction log file to 200 GB in size. I grew the
database transaction log file to 200 GB in size since that was the largest
size the file grew.
Weekly I copy the database backup file to a disk and send it to the software
vendor.
Is there away to restore this database without the database transaction log
file growing to 200 GB in size?
To restore the database transaction log file without all of the unused space.
I would like the software vendor not to have to purchase another disk just
to restore an almost empty database transaction log file.
Thank You,This is a multi-part message in MIME format.
--=_NextPart_000_002A_01C4E08C.1DE69330
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Hello there,
Shrink the transaction log file before backing up the database.
Tunji O
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message =news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
I have a SQL Server 2000 database that is approximately 300 GB in size =and I auto grew the database transaction log file to 200 GB in size. I grew =the database transaction log file to 200 GB in size since that was the =largest size the file grew.
Weekly I copy the database backup file to a disk and send it to the =software vendor.
Is there away to restore this database without the database =transaction log file growing to 200 GB in size?
To restore the database transaction log file without all of the unused =space.
I would like the software vendor not to have to purchase another disk =just to restore an almost empty database transaction log file.
Thank You,
--=_NextPart_000_002A_01C4E08C.1DE69330
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hello there,
Shrink the transaction log file before =backing up the database.
Tunji O
"Joe K." wrote in message news:46F=71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...I have a SQL Server 2000 database that is approximately 300 GB in size =and I auto grew the database transaction log file to 200 GB in =size. I grew the database transaction log file to 200 GB in size since =that was the largest size the file grew.Weekly I copy the database =backup file to a disk and send it to the software vendor.Is there =away to restore this database without the database transaction log file =growing to 200 GB in size?To restore the database transaction log file =without all of the unused space.I would like the software vendor not =to have to purchase another disk just to restore an almost empty database transaction log file. Thank You,

---

--=_NextPart_000_002A_01C4E08C.1DE69330--|||Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:
> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size and I
> auto grew the database transaction log file to 200 GB in size. I grew the
> database transaction log file to 200 GB in size since that was the largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the software
> vendor.
> Is there away to restore this database without the database transaction log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused space.
> I would like the software vendor not to have to purchase another disk just
> to restore an almost empty database transaction log file.
> Thank You,|||A restored database is exactly like the original. Only the database name
and file names can be changed during the restore process.
As Tunji O said, you need to shrink the transaction log file before taking
the backup. You cannot shrink it during the restore.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
>> Hello there,
>> Shrink the transaction log file before backing up the database.
>> Tunji O
>> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
>> news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
>> I have a SQL Server 2000 database that is approximately 300 GB in size
>> and I
>> auto grew the database transaction log file to 200 GB in size. I grew
>> the
>> database transaction log file to 200 GB in size since that was the
>> largest
>> size the file grew.
>> Weekly I copy the database backup file to a disk and send it to the
>> software
>> vendor.
>> Is there away to restore this database without the database transaction
>> log
>> file growing to 200 GB in size?
>> To restore the database transaction log file without all of the unused
>> space.
>> I would like the software vendor not to have to purchase another disk
>> just
>> to restore an almost empty database transaction log file.
>> Thank You,|||If you shrink th elog file before you back it up then when you restore that
backup the log file will be the size it was when you backed it up. A 200GB
log file is pretty large for a 300GB database. I suspect you are not doing
regular log backups if it auto grew that much.
--
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
>> Hello there,
>> Shrink the transaction log file before backing up the database.
>> Tunji O
>> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
>> news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
>> I have a SQL Server 2000 database that is approximately 300 GB in size
>> and I
>> auto grew the database transaction log file to 200 GB in size. I grew
>> the
>> database transaction log file to 200 GB in size since that was the
>> largest
>> size the file grew.
>> Weekly I copy the database backup file to a disk and send it to the
>> software
>> vendor.
>> Is there away to restore this database without the database transaction
>> log
>> file growing to 200 GB in size?
>> To restore the database transaction log file without all of the unused
>> space.
>> I would like the software vendor not to have to purchase another disk
>> just
>> to restore an almost empty database transaction log file.
>> Thank You,|||If I am reading you correctly, the backup has already been trasmitted to the
vendor. At this point, the restore will reproduce the files, data and
transaction logs, exactly as they were when the backup was taken. You only
have the option of renaming the database and/or location and names of the
files, not their sizes nor their content.
If you want to achieve what you have specified, YOU WILL HAVE TO DO IT
AGAIN, just like the other respondents have told you: shrink the log file,
backup the database, transmit the backup, manually regrow the transaction
log back to its original size.
AUTOGROW is a nice feature, in a pinch. However, as the Database
Administrator, you need to manually allocate a reasonable intitial size for
the data and log files, especially for the log files.
If you run the DBCC LOGINFO command on the database, I suspect you will see
thousands of VLOG entries. That many VLOGs is inefficient. If you were to
manually grow an initial model database log file to the allocation you want,
you will probably only see 20 or so VLog entries. This is what you want.
The other possibility is as what one of the other respondents has stated,
you may not be applying an appropriate disaster recovery backup schedule by
including regular transaction log backups.
Sincerely,
Anthony Thomas
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:
> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size
and I
> auto grew the database transaction log file to 200 GB in size. I grew
the
> database transaction log file to 200 GB in size since that was the
largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the
software
> vendor.
> Is there away to restore this database without the database transaction
log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused
space.
> I would like the software vendor not to have to purchase another disk
just
> to restore an almost empty database transaction log file.
> Thank You,

Restore Database w/Large Transaction Log File

I have a SQL Server 2000 database that is approximately 300 GB in size and I
auto grew the database transaction log file to 200 GB in size. I grew the
database transaction log file to 200 GB in size since that was the largest
size the file grew.
Weekly I copy the database backup file to a disk and send it to the software
vendor.
Is there away to restore this database without the database transaction log
file growing to 200 GB in size?
To restore the database transaction log file without all of the unused space.
I would like the software vendor not to have to purchase another disk just
to restore an almost empty database transaction log file.
Thank You,
Hello there,
Shrink the transaction log file before backing up the database.
Tunji O
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
I have a SQL Server 2000 database that is approximately 300 GB in size and I
auto grew the database transaction log file to 200 GB in size. I grew the
database transaction log file to 200 GB in size since that was the largest
size the file grew.
Weekly I copy the database backup file to a disk and send it to the software
vendor.
Is there away to restore this database without the database transaction log
file growing to 200 GB in size?
To restore the database transaction log file without all of the unused space.
I would like the software vendor not to have to purchase another disk just
to restore an almost empty database transaction log file.
Thank You,
|||Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:

> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size and I
> auto grew the database transaction log file to 200 GB in size. I grew the
> database transaction log file to 200 GB in size since that was the largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the software
> vendor.
> Is there away to restore this database without the database transaction log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused space.
> I would like the software vendor not to have to purchase another disk just
> to restore an almost empty database transaction log file.
> Thank You,
|||A restored database is exactly like the original. Only the database name
and file names can be changed during the restore process.
As Tunji O said, you need to shrink the transaction log file before taking
the backup. You cannot shrink it during the restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...[vbcol=seagreen]
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
|||If you shrink th elog file before you back it up then when you restore that
backup the log file will be the size it was when you backed it up. A 200GB
log file is pretty large for a 300GB database. I suspect you are not doing
regular log backups if it auto grew that much.
Andrew J. Kelly SQL MVP
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...[vbcol=seagreen]
> Shrinking a database transaction log file that has that has been pre-gone
> to
> 200 GB in size, will not save any disk space on the restore.
> Please help me resolve the problem listed below.
> Thanks,
>
> "Tunji O" wrote:
|||If I am reading you correctly, the backup has already been trasmitted to the
vendor. At this point, the restore will reproduce the files, data and
transaction logs, exactly as they were when the backup was taken. You only
have the option of renaming the database and/or location and names of the
files, not their sizes nor their content.
If you want to achieve what you have specified, YOU WILL HAVE TO DO IT
AGAIN, just like the other respondents have told you: shrink the log file,
backup the database, transmit the backup, manually regrow the transaction
log back to its original size.
AUTOGROW is a nice feature, in a pinch. However, as the Database
Administrator, you need to manually allocate a reasonable intitial size for
the data and log files, especially for the log files.
If you run the DBCC LOGINFO command on the database, I suspect you will see
thousands of VLOG entries. That many VLOGs is inefficient. If you were to
manually grow an initial model database log file to the allocation you want,
you will probably only see 20 or so VLog entries. This is what you want.
The other possibility is as what one of the other respondents has stated,
you may not be applying an appropriate disaster recovery backup schedule by
including regular transaction log backups.
Sincerely,
Anthony Thomas

"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:A1B27173-EA51-41A0-9A19-560229412D82@.microsoft.com...
Shrinking a database transaction log file that has that has been pre-gone to
200 GB in size, will not save any disk space on the restore.
Please help me resolve the problem listed below.
Thanks,
"Tunji O" wrote:

> Hello there,
> Shrink the transaction log file before backing up the database.
> Tunji O
> "Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:46F71F02-4EC7-4B9F-8137-9D9D6A14BF36@.microsoft.com...
> I have a SQL Server 2000 database that is approximately 300 GB in size
and I
> auto grew the database transaction log file to 200 GB in size. I grew
the
> database transaction log file to 200 GB in size since that was the
largest
> size the file grew.
> Weekly I copy the database backup file to a disk and send it to the
software
> vendor.
> Is there away to restore this database without the database transaction
log
> file growing to 200 GB in size?
> To restore the database transaction log file without all of the unused
space.
> I would like the software vendor not to have to purchase another disk
just
> to restore an almost empty database transaction log file.
> Thank You,
sql