Errors restoring Transaction Logs

Howdy,
I just accidently wiped out a table that had some pretty important data in
it. Can I restore it from the transaction log? Here's what I've tried so far
and I get the errors as stated below. I did do "net pause mssqlserver"
before running it.
USE master
RESTORE DATABASE cpts
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
WITH RECOVERY
RESTORE LOG cpts
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
RPCS.ldf'
WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
Erros
Server: Msg 3101, Level 16, State 2, Line 3
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 2, Line 7
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.
Thanks!!
David LozziYour database cpts will need to be changed to single user mode.
EXEC master.dbo.sp_dboption 'cpts', 'single user', true
(Be reminded to set it to false afterwards.)
Martin C K Poon
Senior Analyst Programmer
====================================
"David Lozzi" <dlozzi@.nospam.nospam> bl
news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl g...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so
far
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Pausing the service only prevents new server connections; existing
connections to the database are not affected.
You'll need to kill connections to the database before you can restore.
With SQL 2000 and above, you can do this easily with ALTER DATABASE ...
ROLLBACK IMMEDIATE:
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Note that you don't necessarily need to overwrite the existing database to
recovery the lost data. An alternative is to restore to a different
database name (and different file names) so you can extract the lost data.
However, if related tables have been modified since the problem, you might
find it easier to overwrite the existing database,
Hope this helps.
Dan Guzman
SQL Server MVP
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message
news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so
> far
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Seems you are trying to restore from the database files (mdf, ldf). That is
not how RESTORE work,
You restore from backup files (taken by the TSQL BACKUP command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.ph
x.gbl...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so f
ar
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Good catch, Tibor.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235lJqxFaGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Seems you are trying to restore from the database files (mdf, ldf). That
> is not how RESTORE work, You restore from backup files (taken by the TSQL
> BACKUP command).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Lozzi" <dlozzi@.nospam.nospam> wrote in message
> news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl...
>