trying to practice restoring a db

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)

trying to practice restoring a db

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-26 : 19:19:38

im trying to practice restoring this database using its own local LDF file..:

RESTORE DATABASE dbdsnz
FROM DISK = 'z:\dbDS_Log.LDF'

but it says:

Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'z:\dbDSNZ_Log.LDF'. Device error or device off-line. See the SQL Server error log for more details.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-26 : 19:20:50

That isn't how it works. You can't restore an LDF file. You need to restore a backup file.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-26 : 19:22:58

so i cant restore from transactions within an ldf file?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-26 : 19:26:33

You need to have transaction log backups.

BACKUP LOG...

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-26 : 19:28:13

thought so, a college belived otherwise so was trying this alternative...
thank you for clearing that up... so using the current ldf file, a transaction cannot be rolled back ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-26 : 19:30:03

I believe third party tools can do this.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-26 : 19:34:18

ah ok not possible within sql server 2000

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-26 : 19:36:18

Well if you know how to read the output from the DBCC LOG command, then yes it is possible.

There's a reason why it takes a third party company to do it. They work very closely with Microsoft building these types of tools, so they have in depth knowledge of the commands and how to read the data returned. This isn't something most people can do on their own.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-04-26 : 20:19:52

thanx for clearing that up

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-04-26 : 22:58:29

If you have log backup, you can do point of time recover by restoring db and applying log backup.

Kristen
Test

22859 Posts

Posted - 2007-04-29 : 12:54:34

"trying to practice restoring a db"

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen