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...
>

error: Too many arguments specified

Error:Too Many Arguments Specified
Posted: 03-03-2005 05:03 AM
Hi,
I'm pretty new to ASP.NET so I'm having some problems
I'm trying to insert a users registration info into the DB using a Stored
Proc but for some reason it won't work
When i run the Sproc in query analyser it seems to be ok with it but when i
run the asp pages it lets me enter the info then when i submit it gives me
the Too many Arguments Specified error
here's the code for the proc
CREATE PROCEDURE [dbo].[Toregister]
(
@.firstname varchar(20),
@.lastname varchar(20),
@.address varchar(20),
@.town varchar(20),
@.county varchar(20),
@.postcode varchar(10),
@.telno varchar(10),
@.email varchar(20),
@.username varchar(20),
@.password varchar(20)
)
AS
insert into t_user
(firstname,lastname,address,town,county,
postcode,telno,email,username,passwo
rd)
values
(@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.userna
me,@.password)
GO
and here's the code in my aspx file for adding the parameters
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Save.Click
SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
SqlCommand1.Parameters.Add("@.town", txtTown.Text)
SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
End Sub
and just for good measure here's the result from the DB profiler when I run
the asp
exec [Toregister] @.firstname = default, @.lastname = default, @.address =
default, @.town = default, @.county = default, @.postcode = default, @.telno =
default, @.email = default, @.username = default, @.password = default,
@.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.town
= N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
@.password = N'neil'
I really can't see where this is going wrong
Anny suggestions welcomeMaybe the parameters already exist in the SqlParameterCollection (Look at
where SqlCommand1 is created).
Have you tried... (Warning I am not a .net programmer so my syntax may be
off).
SqlCommand1.Parameters("@.firstname").Value = txtFirstname.Text
SqlCommand1.Parameters("@.lastname").Value = txtSurname.Text
SqlCommand1.Parameters("@.address").Value = txtAddress.Text
SqlCommand1.Parameters("@.town").Value = txtTown.Text
SqlCommand1.Parameters("@.county").Value = txtCounty.Text
SqlCommand1.Parameters("@.postcode").Value = txtPostcode.Text
SqlCommand1.Parameters("@.telno").Value = txtTelno.Text
SqlCommand1.Parameters("@.email").Value = txtEmail.Text
SqlCommand1.Parameters("@.username").Value = txtUsername.Text
SqlCommand1.Parameters("@.password").Value = txtPassword.Text
"neil_pat" wrote:

> Error:Too Many Arguments Specified
> Posted: 03-03-2005 05:03 AM
> Hi,
> I'm pretty new to ASP.NET so I'm having some problems
> I'm trying to insert a users registration info into the DB using a Stored
> Proc but for some reason it won't work
> When i run the Sproc in query analyser it seems to be ok with it but when
i
> run the asp pages it lets me enter the info then when i submit it gives me
> the Too many Arguments Specified error
> here's the code for the proc
> CREATE PROCEDURE [dbo].[Toregister]
> (
> @.firstname varchar(20),
> @.lastname varchar(20),
> @.address varchar(20),
> @.town varchar(20),
> @.county varchar(20),
> @.postcode varchar(10),
> @.telno varchar(10),
> @.email varchar(20),
> @.username varchar(20),
> @.password varchar(20)
> )
> AS
> insert into t_user
> (firstname,lastname,address,town,county,
postcode,telno,email,username,pass
word)
> values
> (@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.user
name,@.password)
> GO
> and here's the code in my aspx file for adding the parameters
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Save.Click
>
> SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
> SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
> SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
> SqlCommand1.Parameters.Add("@.town", txtTown.Text)
> SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
> SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
> SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
> SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
> SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
> SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
> SqlConnection1.Open()
> SqlCommand1.ExecuteNonQuery()
> End Sub
>
> and just for good measure here's the result from the DB profiler when I ru
n
> the asp
> exec [Toregister] @.firstname = default, @.lastname = default, @.address =
> default, @.town = default, @.county = default, @.postcode = default, @.telno =
> default, @.email = default, @.username = default, @.password = default,
> @.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.to
wn
> = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
> N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
> @.password = N'neil'
> I really can't see where this is going wrong
> Anny suggestions welcome
>

error: too many arguments specified

Error:Too Many Arguments Specified
Posted: 03-03-2005 05:03 AM
Hi,
I'm pretty new to ASP.NET so I'm having some problems
I'm trying to insert a users registration info into the DB using a Stored
Proc but for some reason it won't work
When i run the Sproc in query analyser it seems to be ok with it but when i
run the asp pages it lets me enter the info then when i submit it gives me
the Too many Arguments Specified error
here's the code for the proc
CREATE PROCEDURE [dbo].[Toregister]
(
@.firstname varchar(20),
@.lastname varchar(20),
@.address varchar(20),
@.town varchar(20),
@.county varchar(20),
@.postcode varchar(10),
@.telno varchar(10),
@.email varchar(20),
@.username varchar(20),
@.password varchar(20)
)
AS
insert into t_user
(firstname,lastname,address,town,county,
postcode,telno,email,username,passwo
rd)
values
(@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.userna
me,@.password)
GO
and here's the code in my aspx file for adding the parameters
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Save.Click
SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
SqlCommand1.Parameters.Add("@.town", txtTown.Text)
SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
SqlConnection1.Open()
SqlCommand1.ExecuteNonQuery()
End Sub
and just for good measure here's the result from the DB profiler when I run
the asp
exec [Toregister] @.firstname = default, @.lastname = default, @.address =
default, @.town = default, @.county = default, @.postcode = default, @.telno =
default, @.email = default, @.username = default, @.password = default,
@.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.town
= N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
@.password = N'neil'
I really can't see where this is going wrong
Anny suggestions welcomeActually there are only 10 parameters to be passed.
exec [Toregister] @.firstname = default, @.lastname = default, @.address =
default, @.town = default, @.county = default, @.postcode = default,
@.telno
= default, @.email = default, @.username = default, @.password = default,
@.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street,
@.town = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd',
@.telno = N'01234567890, @.email = N'neil_...@.email.com', @.username =
N'neilpat', @.password = N'neil'
You passed more than 10
Madhivanan|||Look at the profiler trace. It is passing all the parameters as default
folowed by all the parameters with values - hence 20 parameters instead of 1
0.
I suspect when you build the command you are getting the parameters for the
sp then adding them again.
"neil_pat" wrote:

> Error:Too Many Arguments Specified
> Posted: 03-03-2005 05:03 AM
> Hi,
> I'm pretty new to ASP.NET so I'm having some problems
> I'm trying to insert a users registration info into the DB using a Stored
> Proc but for some reason it won't work
> When i run the Sproc in query analyser it seems to be ok with it but when
i
> run the asp pages it lets me enter the info then when i submit it gives me
> the Too many Arguments Specified error
> here's the code for the proc
> CREATE PROCEDURE [dbo].[Toregister]
> (
> @.firstname varchar(20),
> @.lastname varchar(20),
> @.address varchar(20),
> @.town varchar(20),
> @.county varchar(20),
> @.postcode varchar(10),
> @.telno varchar(10),
> @.email varchar(20),
> @.username varchar(20),
> @.password varchar(20)
> )
> AS
> insert into t_user
> (firstname,lastname,address,town,county,
postcode,telno,email,username,pass
word)
> values
> (@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.user
name,@.password)
> GO
> and here's the code in my aspx file for adding the parameters
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Save.Click
>
> SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
> SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
> SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
> SqlCommand1.Parameters.Add("@.town", txtTown.Text)
> SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
> SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
> SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
> SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
> SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
> SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
> SqlConnection1.Open()
> SqlCommand1.ExecuteNonQuery()
> End Sub
>
> and just for good measure here's the result from the DB profiler when I ru
n
> the asp
> exec [Toregister] @.firstname = default, @.lastname = default, @.address =
> default, @.town = default, @.county = default, @.postcode = default, @.telno =
> default, @.email = default, @.username = default, @.password = default,
> @.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.to
wn
> = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
> N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
> @.password = N'neil'
> I really can't see where this is going wrong
> Anny suggestions welcome
>|||Could you just explain that? iDo you mean that its counting each one
seprately i.e. 20 instead of 10?
How do i rectify this?
"Madhivanan" wrote:

> Actually there are only 10 parameters to be passed.
> exec [Toregister] @.firstname = default, @.lastname = default, @.address =
> default, @.town = default, @.county = default, @.postcode = default,
> @.telno
> = default, @.email = default, @.username = default, @.password = default,
> @.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street,
> @.town = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd',
> @.telno = N'01234567890, @.email = N'neil_...@.email.com', @.username =
> N'neilpat', @.password = N'neil'
> You passed more than 10
> Madhivanan
>|||Sorry I'm a bit of a beginer.
Which bit of my code do i need to edit to stop this?
"Nigel Rivett" wrote:
> Look at the profiler trace. It is passing all the parameters as default
> folowed by all the parameters with values - hence 20 parameters instead of
10.
> I suspect when you build the command you are getting the parameters for th
e
> sp then adding them again.
> "neil_pat" wrote:
>|||Have a look at
http://www.mindsdoor.net/DOTNET/DotNetDBAccess.html
It gives a database access layer and includes creation of all the objects.
I suspect you are binding sqlcommand1 to the sp before adding the parameters
.
"neil_pat" wrote:
> Sorry I'm a bit of a beginer.
> Which bit of my code do i need to edit to stop this?
> "Nigel Rivett" wrote:
>|||Remove all the fields with default values
Madhivanan|||I managed to sort it out now.
VS.net was adding default parameters automatically which i removed and it
works fine now
Thanks
"neil_pat" wrote:

> Error:Too Many Arguments Specified
> Posted: 03-03-2005 05:03 AM
> Hi,
> I'm pretty new to ASP.NET so I'm having some problems
> I'm trying to insert a users registration info into the DB using a Stored
> Proc but for some reason it won't work
> When i run the Sproc in query analyser it seems to be ok with it but when
i
> run the asp pages it lets me enter the info then when i submit it gives me
> the Too many Arguments Specified error
> here's the code for the proc
> CREATE PROCEDURE [dbo].[Toregister]
> (
> @.firstname varchar(20),
> @.lastname varchar(20),
> @.address varchar(20),
> @.town varchar(20),
> @.county varchar(20),
> @.postcode varchar(10),
> @.telno varchar(10),
> @.email varchar(20),
> @.username varchar(20),
> @.password varchar(20)
> )
> AS
> insert into t_user
> (firstname,lastname,address,town,county,
postcode,telno,email,username,pass
word)
> values
> (@.firstname,@.lastname,@.address,@.town,@.co
unty,@.postcode,@.telno,@.email,@.user
name,@.password)
> GO
> and here's the code in my aspx file for adding the parameters
> Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles Save.Click
>
> SqlCommand1.Parameters.Add("@.firstname", txtFirstname.Text)
> SqlCommand1.Parameters.Add("@.lastname", txtSurname.Text)
> SqlCommand1.Parameters.Add("@.address", txtAddress.Text)
> SqlCommand1.Parameters.Add("@.town", txtTown.Text)
> SqlCommand1.Parameters.Add("@.county", txtCounty.Text)
> SqlCommand1.Parameters.Add("@.postcode", txtPostcode.Text)
> SqlCommand1.Parameters.Add("@.telno", txtTelno.Text)
> SqlCommand1.Parameters.Add("@.email", txtEmail.Text)
> SqlCommand1.Parameters.Add("@.username", txtUsername.Text)
> SqlCommand1.Parameters.Add("@.password", txtPassword.Text)
> SqlConnection1.Open()
> SqlCommand1.ExecuteNonQuery()
> End Sub
>
> and just for good measure here's the result from the DB profiler when I ru
n
> the asp
> exec [Toregister] @.firstname = default, @.lastname = default, @.address =
> default, @.town = default, @.county = default, @.postcode = default, @.telno =
> default, @.email = default, @.username = default, @.password = default,
> @.firstname = N'Neil', @.lastname = N'Patel', @.address = N'28 my street, @.to
wn
> = N'mytown', @.county = N'mycounty', @.postcode = N'mypostcd', @.telno =
> N'01234567890, @.email = N'neil_pat@.email.com', @.username = N'neilpat',
> @.password = N'neil'
> I really can't see where this is going wrong
> Anny suggestions welcome
>|||I've sorted it now
VS.Net was adding default parameters automatically. I've removed them and it
works fine now
"Nigel Rivett" wrote:
> Have a look at
> http://www.mindsdoor.net/DOTNET/DotNetDBAccess.html
> It gives a database access layer and includes creation of all the objects.
> I suspect you are binding sqlcommand1 to the sp before adding the paramete
rs.
> "neil_pat" wrote:
>