Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.com
Hi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com
|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:
>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.
>Are there any good documents or web site about point time recovery?
Books Online is a great start.
>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200507/1
Showing posts with label backups. Show all posts
Showing posts with label backups. Show all posts
Monday, March 26, 2012
Question about Point Time Recovery
Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51A93BABEA594@.SQLMonster.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.sqlmonster.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via SQLMonster.com wrote:
>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.
>Are there any good documents or web site about point time recovery?
Books Online is a great start.
>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51B72295E6DA2@.SQLMonster.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to point
in time? Is it right?
Thanks in advance,
Do.
--
Message posted via http://www.sqlmonster.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51A93BABEA594@.SQLMonster.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.sqlmonster.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via SQLMonster.com wrote:
>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.
>Are there any good documents or web site about point time recovery?
Books Online is a great start.
>If point in time restore button is grey, I cannot restore a database to point
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:51B72295E6DA2@.SQLMonster.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore, my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1
Question about Point Time Recovery
Hello All,
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to poin
t
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:
>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.
>Are there any good documents or web site about point time recovery?
Books Online is a great start.
>If point in time restore button is grey, I cannot restore a database to poi
nt
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it ag
ain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore,
my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1
I have questions about point in time.
I took database backups and transaction log like the following.
Database backup at 1:00 pm on July 20
Transaction backup at 2:00 pm on July 20
Database backup at 1:00 pm on July 21
Transaction backup at 2:00 pm on July 21
Can I restore the database to 3:00 pm on July 20?
Can I restore the database to only 2:00 pm on July 20?
Would you please explain it?
Are there any good documents or web site about point time recovery?
If point in time restore button is grey, I cannot restore a database to poin
t
in time? Is it right?
Thanks in advance,
Do.
Message posted via http://www.droptable.comHi,
POINT-IN-TIME recovery can be made only if your database is FULL recovery
model. BULK_LOGGED recovery model will not allow
POINT-IN_TIME recovery.
Based on your backups below you could do a seperate point in time recovery
till 2:00 pm on July 20 or 3:00 pm on July 20
(Provided your database recovery is FULL)
-- For 2:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 02:00 PM'
GO
-- For 3:00 pm on July 20
RESTORE DATABASE DBNAME
FROM disk='c:\backup\dbfullbackup_20_july.bak'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_20july_02PM.TRN'
WITH NORECOVERY
GO
RESTORE LOG DBNAME
FROM disk='c:\backup\Tranbackup_21july_02PM.TRN'
WITH RECOVERY, STOPAT = 'Jul 20, 2005 03:00 PM'
GO
Are there any good documents or web site about point time recovery?
See POINT IN TIME in books online
If point in time restore button is grey, I cannot restore a database to
point in time? Is it right?
"Do P via droptable.com" <forum@.droptable.com> wrote in message
news:51A93BABEA594@.droptable.com...
> Hello All,
> I have questions about point in time.
> I took database backups and transaction log like the following.
> Database backup at 1:00 pm on July 20
> Transaction backup at 2:00 pm on July 20
> Database backup at 1:00 pm on July 21
> Transaction backup at 2:00 pm on July 21
> Can I restore the database to 3:00 pm on July 20?
> Can I restore the database to only 2:00 pm on July 20?
> Would you please explain it?
> Are there any good documents or web site about point time recovery?
> If point in time restore button is grey, I cannot restore a database to
> point
> in time? Is it right?
>
> Thanks in advance,
> Do.
>
> --
> Message posted via http://www.droptable.com|||On Thu, 21 Jul 2005 17:27:51 GMT, Do P via droptable.com wrote:
>Hello All,
>I have questions about point in time.
>I took database backups and transaction log like the following.
>Database backup at 1:00 pm on July 20
>Transaction backup at 2:00 pm on July 20
>Database backup at 1:00 pm on July 21
>Transaction backup at 2:00 pm on July 21
>Can I restore the database to 3:00 pm on July 20?
Hi Do,
Yes. First restore from the full backup, with the WITH NORECOVERY
option. Next, restore the transaction log backup of July 20 2:00 PM,
again with the WITH NORECOVERY option. Finally, restore from the second
transaction log, this time without WITH NORECOVERY, but with a STOPAT
parameter.
>Are there any good documents or web site about point time recovery?
Books Online is a great start.
>If point in time restore button is grey, I cannot restore a database to poi
nt
>in time? Is it right?
Either that, or you didn't supply all the needed information yet, or you
have to restore some other backup first before you can restore to the
desired point in time. I recommend that you start using Query Analyzer
and typing the RESTORE DATABASE and RESTORE LOG commands. This gives you
much more control over what will happen.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo
Thanks for your update.
If I use the ''Point in time restore'' option, at the end of the restore, my
database is ALWAYS in ''Loading'' state even if I checked the ''Leave
database operational''. Would you please let me know how to restore a
database to point in time in Enterprise Manager? How can I avoid this from
happening?
Best Regards,
Do.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||Perhaps a refresh issue? Try closing down Enterprise Manager and start it ag
ain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Do Park via droptable.com" <forum@.droptable.com> wrote in message
news:51B72295E6DA2@.droptable.com...
> Hello Hugo
> Thanks for your update.
> If I use the ''Point in time restore'' option, at the end of the restore,
my
> database is ALWAYS in ''Loading'' state even if I checked the ''Leave
> database operational''. Would you please let me know how to restore a
> database to point in time in Enterprise Manager? How can I avoid this from
> happening?
> Best Regards,
> Do.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200507/1
Monday, March 12, 2012
Question about DIFFERENTIAL backups
Our production backup schedule is, FULL backup once a month and a DIFFERENTIAL backup every day.
We are starting the FULL and DIFFERENTIAL backups using something similar to
FULL Backup
backup database @.DB to disk = @.BackupFile
DIFFERENTIAL backup
backup database @.DB to disk = @.BackupFile WITH DIFFERENTIAL
A full backup was done on 10/6 and the DB size was 48GB. Since then we have been doing DIFFERENTIAL backups. I recently looked at the DIFFERENTIAL backup directory and found something I think is interesting.
10/07/2005 04:00 AM 8,604,160 prodDB_Diff_200510070400.BAK
10/08/2005 04:00 AM 1,144,320 prodDB_Diff_200510080400.BAK
10/09/2005 04:00 AM 1,134,080 prodDB_Diff_200510090400.BAK
10/10/2005 04:00 AM 21,185,024 prodDB_Diff_200510100400.BAK
10/11/2005 04:00 AM 7,119,360 prodDB_Diff_200510110400.BAK
10/12/2005 04:00 AM 163,669,504 prodDB_Diff_200510120400.BAK
10/13/2005 04:00 AM 14,743,040 prodDB_Diff_200510130400.BAK
10/14/2005 04:00 AM 120,875,520 prodDB_Diff_200510140400.BAK
10/15/2005 04:00 AM 1,216,000 prodDB_Diff_200510150400.BAK
10/16/2005 04:00 AM 5,139,968 prodDB_Diff_200510160400.BAK
10/17/2005 04:00 AM 4,277,760 prodDB_Diff_200510170400.BAK
10/18/2005 04:00 AM 2,778,624 prodDB_Diff_200510180400.BAK
10/19/2005 04:01 AM 750,575,104 prodDB_Diff_200510190400.BAK
My understanding of DIFFERENTIAL backups from books online is
"Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"
MY Question:
Assuming I am understanding this correctly, all DIFFERENTIAL backups are independent of one another. If a failure happens, all I have to do is restore the latest FULL backup and then
restore the latest DIFFERENTIAL backup.
So, I would think the sizes of the backup files should be in ASCENDING order. Why is the size of the backup file from 10/15/2005 (1,216,000 bytes) smaller than the backup file from 10/14/2005 (120,875,520 bytes).
Assuming a crash happens on 10/15 5AM, I would restore the full backup from 10/6 and then restore the DIFF. backup from 10/15 4AM (Which will apply approx.. 1,216,000 bytes of data). So where has the data in the backup file from 10/14/2005 gone (120,875,520 bytes)?
ThanksНou mistaken
Look for keyword "Differential Database Backups"
"A differential database backup records only the data that has changed since the last database backup."
Draw attention - "last database backup". No full backup.|||From BOL:
DIFFERENTIAL
Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup. Use this option so that all individual log backups since the last full backup do not need to be applied. For more information, see Differential Database Backups and File Differential Backups.
Note During a full database or differential backup, SQL Server backs up enough of the transaction log to produce a consistent database when the database is restored.
Are you doing a lot of updates during the time it takes to do the backups?|||Thanks so much for your response..
Right, your quote from BOL is exactly what I read. It has FULL backup written all over it.
Are you sure it is
""A differential database backup records only the data that has changed since the last database backup."
rather than what BOL says it is
"Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"
I wouldn't say lots of updates but the DB is being used.|||You have the right idea about restoring the database in your situation. Just 2 restores, and one recovery, no matter what day you go down.
I am also puzzled by the wild size differences in there. This isn't something strange like the destination of a log-shipping instance, is it? I could see full backups on the source database causing this behavior.
We are starting the FULL and DIFFERENTIAL backups using something similar to
FULL Backup
backup database @.DB to disk = @.BackupFile
DIFFERENTIAL backup
backup database @.DB to disk = @.BackupFile WITH DIFFERENTIAL
A full backup was done on 10/6 and the DB size was 48GB. Since then we have been doing DIFFERENTIAL backups. I recently looked at the DIFFERENTIAL backup directory and found something I think is interesting.
10/07/2005 04:00 AM 8,604,160 prodDB_Diff_200510070400.BAK
10/08/2005 04:00 AM 1,144,320 prodDB_Diff_200510080400.BAK
10/09/2005 04:00 AM 1,134,080 prodDB_Diff_200510090400.BAK
10/10/2005 04:00 AM 21,185,024 prodDB_Diff_200510100400.BAK
10/11/2005 04:00 AM 7,119,360 prodDB_Diff_200510110400.BAK
10/12/2005 04:00 AM 163,669,504 prodDB_Diff_200510120400.BAK
10/13/2005 04:00 AM 14,743,040 prodDB_Diff_200510130400.BAK
10/14/2005 04:00 AM 120,875,520 prodDB_Diff_200510140400.BAK
10/15/2005 04:00 AM 1,216,000 prodDB_Diff_200510150400.BAK
10/16/2005 04:00 AM 5,139,968 prodDB_Diff_200510160400.BAK
10/17/2005 04:00 AM 4,277,760 prodDB_Diff_200510170400.BAK
10/18/2005 04:00 AM 2,778,624 prodDB_Diff_200510180400.BAK
10/19/2005 04:01 AM 750,575,104 prodDB_Diff_200510190400.BAK
My understanding of DIFFERENTIAL backups from books online is
"Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"
MY Question:
Assuming I am understanding this correctly, all DIFFERENTIAL backups are independent of one another. If a failure happens, all I have to do is restore the latest FULL backup and then
restore the latest DIFFERENTIAL backup.
So, I would think the sizes of the backup files should be in ASCENDING order. Why is the size of the backup file from 10/15/2005 (1,216,000 bytes) smaller than the backup file from 10/14/2005 (120,875,520 bytes).
Assuming a crash happens on 10/15 5AM, I would restore the full backup from 10/6 and then restore the DIFF. backup from 10/15 4AM (Which will apply approx.. 1,216,000 bytes of data). So where has the data in the backup file from 10/14/2005 gone (120,875,520 bytes)?
ThanksНou mistaken
Look for keyword "Differential Database Backups"
"A differential database backup records only the data that has changed since the last database backup."
Draw attention - "last database backup". No full backup.|||From BOL:
DIFFERENTIAL
Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup. A differential backup usually takes up less space than a full backup. Use this option so that all individual log backups since the last full backup do not need to be applied. For more information, see Differential Database Backups and File Differential Backups.
Note During a full database or differential backup, SQL Server backs up enough of the transaction log to produce a consistent database when the database is restored.
Are you doing a lot of updates during the time it takes to do the backups?|||Thanks so much for your response..
Right, your quote from BOL is exactly what I read. It has FULL backup written all over it.
Are you sure it is
""A differential database backup records only the data that has changed since the last database backup."
rather than what BOL says it is
"Specifies the database or file backup should consist only of the portions of the database or file changed since the last full backup"
I wouldn't say lots of updates but the DB is being used.|||You have the right idea about restoring the database in your situation. Just 2 restores, and one recovery, no matter what day you go down.
I am also puzzled by the wild size differences in there. This isn't something strange like the destination of a log-shipping instance, is it? I could see full backups on the source database causing this behavior.
Wednesday, March 7, 2012
Question about backups.
Hi guys,
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
Thanks
You always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks
|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FULL
> backup. Be aware that if you are using the SIMPLE recovery mode or you are
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>
|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:
|||Thanks a lot. I appreciate all your help.
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
Thanks
You always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks
|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FULL
> backup. Be aware that if you are using the SIMPLE recovery mode or you are
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>
|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:
|||Thanks a lot. I appreciate all your help.
Question about backups.
Hi guys,
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
ThanksYou always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FUL
L
> backup. Be aware that if you are using the SIMPLE recovery mode or you ar
e
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:|||Thanks a lot. I appreciate all your help.
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
ThanksYou always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FUL
L
> backup. Be aware that if you are using the SIMPLE recovery mode or you ar
e
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:|||Thanks a lot. I appreciate all your help.
Question about backups.
Hi guys,
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
ThanksYou always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
--
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FULL
> backup. Be aware that if you are using the SIMPLE recovery mode or you are
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
--
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:
>> You always have to restore the last FULL backup and then you should only
>> need the last DIFF since it always includes any changes since the last
>> FULL backup. Be aware that if you are using the SIMPLE recovery mode or
>> you are not issuing regular LOG backups you will not be able to recover
>> any transactions that occurred since the last DIFF and the time you have
>> an issue.|||Thanks a lot. I appreciate all your help.
I'm not very familiar with backups, and I have a simple question.
Let's suppose that I do one full backup and 10 differential backups.
If sometime later, I delete the first 9 of those differential backups
and I keep only the last one, should I be able to restore the whole
database?
ThanksYou always have to restore the last FULL backup and then you should only
need the last DIFF since it always includes any changes since the last FULL
backup. Be aware that if you are using the SIMPLE recovery mode or you are
not issuing regular LOG backups you will not be able to recover any
transactions that occurred since the last DIFF and the time you have an
issue.
--
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:O6ykFzzKGHA.3200@.tk2msftngp13.phx.gbl...
> Hi guys,
> I'm not very familiar with backups, and I have a simple question.
> Let's suppose that I do one full backup and 10 differential backups.
> If sometime later, I delete the first 9 of those differential backups and
> I keep only the last one, should I be able to restore the whole database?
> Thanks|||Thanks Andrew for your help.
Just a couple of more questions
1. If I make a transaction log backup, is the transaction log deleted in
that moment?
2. When is it safe to truncate the transaction log?
Andrew J. Kelly wrote:
> You always have to restore the last FULL backup and then you should only
> need the last DIFF since it always includes any changes since the last FULL
> backup. Be aware that if you are using the SIMPLE recovery mode or you are
> not issuing regular LOG backups you will not be able to recover any
> transactions that occurred since the last DIFF and the time you have an
> issue.
>|||1. The log itself is never deleted. It is a physical file that must exist
in order for SQL Server to operate. When you backup the log it will allow
the space in the file taken up by committed transactions to be reused for
new transactions.
2. Almost never. That is something that should be done via backing up the
log. If you manually truncate a log file you may void it's usefulness for
recovery purposes.
I suggest you read up in BooksOnLine under BACKUP and RESTORE for more
details. Also "Inside SQL Server 2000" by Kalen Delaney goes into quite a
bit of detail on how the log works along with backups & restores.
--
Andrew J. Kelly SQL MVP
"Star" <noemail@.noemail.com> wrote in message
news:Od1VpK0KGHA.2668@.tk2msftngp13.phx.gbl...
> Thanks Andrew for your help.
> Just a couple of more questions
> 1. If I make a transaction log backup, is the transaction log deleted in
> that moment?
> 2. When is it safe to truncate the transaction log?
>
>
> Andrew J. Kelly wrote:
>> You always have to restore the last FULL backup and then you should only
>> need the last DIFF since it always includes any changes since the last
>> FULL backup. Be aware that if you are using the SIMPLE recovery mode or
>> you are not issuing regular LOG backups you will not be able to recover
>> any transactions that occurred since the last DIFF and the time you have
>> an issue.|||Thanks a lot. I appreciate all your help.
Subscribe to:
Posts (Atom)