Restoring transaction log - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Restoring transaction log

Author

Topic

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-13 : 16:43:31

I run the scripts below & everything seems to run ok. But when I query the DB for data entered since the DB backup which should be on the log, I don't see any. What am I missing?
Thanks.

RESTORE DATABASE yyyyy_Prod_Staging
FROM DISK = '\\xxxxxxxxx\Backup\yyyyy_Full.bak'
WITH NORECOVERY, REPLACE,
MOVE 'yyyyyData' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.DEV\MSSQL\DATA\yyyyy_Prod_Staging.mdf' ,
MOVE 'yyyyyLog' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10.DEV\MSSQL\DATA\yyyyy_Prod_Staging.ldf'

---- Restore TRAN LOG
RESTORE LOG yyyyy_Prod_Staging
FROM DISK = '\\xxxxxxx\Backup\yyyyy_Transaction.trn'
WITH RECOVERY

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-13 : 17:00:21

If you don't see the data, then you didn't restore to the correct point in time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-13 : 20:40:42

Hi Tara,
Thanks. Do I need to specify this in the script somehow?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-13 : 20:57:57

You need to restore the tlog(s) that get you to the right point in time. Which tlogs to restore is not something I can answer, but yes you'll need to add them to the restore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-13 : 22:25:06

I am restoring all tlogs which are there for today (A few in the one file) & I am searching for rows created in a table with today's date on it. So I would expect it should be there.
I am getting a message which may explain the problem:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Not sure where/why this is appearing
thanks.
----------------------------------------------------------------------------------
RESTORE DATABASE YYYY_Prod_Staging
FROM DISK = '\\xxxxx\Backup\YYYY_Full.bak'
WITH NORECOVERY, REPLACE,
MOVE 'YYYYData' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.DEV\MSSQL\DATA\YYYY_Prod_Staging.mdf' ,
MOVE 'YYYYLog' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10.DEV\MSSQL\DATA\YYYY_Prod_Staging.ldf'
GO

RESTORE LOG YYYY_Prod_Staging
FROM DISK = '\\xxxxxx\Backup\YYYY_Transaction.trn'
WITH NORECOVERY
GO
RESTORE DATABASE YYYY_Prod_Staging
WITH RECOVERY
GO
-----------------------------------------------------------------------
OUTPUT
-----------------------------------------------------------------------
Processed 2707824 pages for database 'YYYY_Prod_Staging', file 'YYYYData' on file 1.
Processed 9 pages for database 'YYYY_Prod_Staging', file 'YYYYLog' on file 1.
RESTORE DATABASE successfully processed 2707833 pages in 151.548 seconds (139.592 MB/sec).
Processed 0 pages for database 'YYYY_Prod_Staging', file 'YYYYData' on file 1.
Processed 432 pages for database 'YYYY_Prod_Staging', file 'YYYYLog' on file 1.
RESTORE LOG successfully processed 432 pages in 0.055 seconds (61.301 MB/sec).
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
RESTORE DATABASE successfully processed 0 pages in 15.803 seconds (0.000 MB/sec).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-13 : 22:39:59

If after you restore the database and your data is not there, then your query is either wrong or your restore is wrong.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-13 : 23:06:10

OK. Thanks. So you see no problem with the script or this message:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-13 : 23:07:51

You aren't showing the full script to have gotten that output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-14 : 14:46:53

That is the full script. It is being restored to a different server, could that be a factor?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-14 : 15:03:56

I don't see how you could get that output with that script.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-14 : 16:03:19

It's the last statement which causes it:
RESTORE DATABASE yyyy_Prod_Staging
WITH RECOVERY
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-14 : 16:07:43

You must have some kind of server trigger that is running a post-restore script. That's the only thing I can think of.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-14 : 16:40:40

Here's a Transact SQL extract from profiler output:

RESTORE DATABASE YYYY_Prod_Staging
FROM DISK = '\\xxxxxx\Backup\YYYY_Full.bak'
WITH NORECOVERY, REPLACE,
MOVE 'YYYYData' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.DEV\MSSQL\DATA\YYYY_Prod_Staging.mdf' ,
MOVE 'YYYYLog' TO 'D:\Program Files\Microsoft SQL Server\MSSQL10.DEV\MSSQL\DATA\YYYY_Prod_Staging.ldf'

go
---- Restore TRAN LOG
RESTORE LOG YYYY_Prod_Staging
FROM DISK = '\\xxxxxx\Backup\YYYY_Transaction.trn'
WITH NORECOVERY

go
RESTORE DATABASE YYYY_Prod_Staging
WITH RECOVERY

go
SELECT SYSTEM_USER
go
SET ROWCOUNT 0 SET TEXTSIZE 2147483647 SET NOCOUNT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ARITHABORT ON SET LOCK_TIMEOUT -1 SET QUERY_GOVERNOR_COST_LIMIT 0 SET DEADLOCK_PRIORITY NORMAL SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET ANSI_NULLS ON SET ANSI_NULL_DFLT_ON ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON SET CURSOR_CLOSE_ON_COMMIT OFF SET IMPLICIT_TRANSACTIONS OFF SET QUOTED_IDENTIFIER ON
go
select @@spid;
select SERVERPROPERTY('ProductLevel');

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-14 : 16:59:41

I don't have the answer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2011-02-14 : 17:03:30

Thanks anyway. Appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources