Transaction Log Backup Help - 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 2000 Forums

SQL Server Administration (2000)

Transaction Log Backup Help

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2005-03-03 : 02:39:42

I want to check how Transaction Log backup works, for this want to have some exercise

1. Full backup of Northwind
2. Transaction log backup of Northwind
3. Restore the backup as Nwnd
4. Delete some records from Northwind.Employees
5. Restore Transaction log backup on Nwnd
6. Will we have all the employees which we have even deleted

Please send me the T-SQL for this

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 06:12:28

It's a bit pointless if you don't write it yourself (or at least try) isn't it?
It's pretty simple so have a go.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

real_pearl
Posting Yak Master

106 Posts

Posted - 2005-03-07 : 07:22:31

These are the steps that I am performing

--Drop existing backup device
sp_dropdevice 'Northwind'

--add backup device
sp_addumpdevice 'Disk', 'Northwind', 'C:\backup\Northwind.bak'

--Backup database
BACKUP DATABASE Northwind TO Northwind WITH INIT

WAITFOR DELAY '00:00:05'

--Backup Log
BACKUP LOG Northwind WITH TRUNCATE_ONLY

WAITFOR DELAY '00:00:05'

select * from master..sysprocesses where dbid = db_id('Nwnd')
kill 51

--Restore database
RESTORE DATABASE Nwnd
FROM DISK = 'c:\backup\Northwind.bak'
WITH
NORECOVERY,
DBO_ONLY,
MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\Nwnd.mdf',
MOVE 'Northwind_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\data\Nwnd_log.ldf'

WAITFOR DELAY '00:00:05'

--Restore Log
RESTORE LOG Nwnd
FROM DISK = 'c:\backup\Northwind.bak'
WITH NORECOVERY, DBO_ONLY

WAITFOR DELAY '00:00:05'

But after restoration, DB shows Nwnd(Loading) and on expansion shows .... (No items)

Please tell me how to resolve this issue

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-03-07 : 12:48:33

you still have the database in NORECOVERY mode. The last logfile you play should have WITH RECOVERY instead.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources