MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
RegardsNo. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
>> Not sure if this is possible, but a client wants to now if we can use a
>> backup we made on 2/24
>> and then pull transaction out of the database using the daily
>> transaction log backups to get the database to be an effective copy of
>> what it looked like on 2/13/06.
>> We have all the daily transaction logs up to today and the 2/24/06 is
>> the latest backup we have.
>> Any help would be greatly appreciated.
>> Regards
>|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
Regards
No. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
>
|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegr oups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

MSSQL 2000 Restore - can I roll back Transaction Logs

Not sure if this is possible, but a client wants to now if we can use a
backup we made on 2/24
and then pull transaction out of the database using the daily
transaction log backups to get the database to be an effective copy of
what it looked like on 2/13/06.
We have all the daily transaction logs up to today and the 2/24/06 is
the latest backup we have.
Any help would be greatly appreciated.
RegardsNo. You need a full backup as a starting point BEFORE the desired target
date. Transaction logs are write-ahead, I.E. they log new data, not old.
Logs can thus be rolled forward but not backwards.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>|||Technical correction for the fussy. The logs do contain roll-back
information, but the restore/recovery process is a forward-only one.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:upWIc03UGHA.1572@.tk2msftngp13.phx.gbl...
> No. You need a full backup as a starting point BEFORE the desired target
> date. Transaction logs are write-ahead, I.E. they log new data, not old.
> Logs can thus be rolled forward but not backwards.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <raekwon2112@.gmail.com> wrote in message
> news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
>|||<raekwon2112@.gmail.com> wrote in message
news:1143669384.250415.233980@.z34g2000cwc.googlegroups.com...
> Not sure if this is possible, but a client wants to now if we can use a
> backup we made on 2/24
> and then pull transaction out of the database using the daily
> transaction log backups to get the database to be an effective copy of
> what it looked like on 2/13/06.
> We have all the daily transaction logs up to today and the 2/24/06 is
> the latest backup we have.
> Any help would be greatly appreciated.
> Regards
>
See the topic "How to Restore to a Point In Time" in Books Online.
You need to restore the latest full backup dated BEFORE 2/13/06 with NO
RECOVERY option, followed by the sequence of transaction log backups
covering that date.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

mssql 2000 kill all user connection for restore

i need a sql statment or procedure to kill all user connection to one database.
And Deny new connection for a little time.
So i can a restore this database from a dump.

I can this do only in exclusiv modus. without any other connection.

Can anybody help me??

thanks and best regards from Berlin, Germanydeclare @.l_spid varchar(4)
,@.l_hostname varchar(20)
,@.dbname varchar(256)

select @.dbname = 'YOUR DATABASE NAME HERE'
declare kill_cursor scroll cursor
for
select convert(varchar(4), spid), hostname from master..sysprocesses with (nolock)
where db_name(dbid) = @.dbname

open kill_cursor
select @.@.cursor_rows

fetch next from kill_cursor into
@.l_spid
,@.l_hostname
while (@.@.fetch_status = 0 )
begin
select @.l_hostname Killed
exec ( 'kill ' + @.l_spid)
fetch next from kill_cursor into
@.l_spid
,@.l_hostname
end
close kill_cursor
deallocate kill_cursor
RESTORE STATEMENT HERE|||thanks i can use the code in my procedure.

Cu ... trapezz|||alter database DBName
set SINGLE_USER with rollback immediate|||I prefer

ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Msg 5133, Level 16, State 1, Line 1

Hi all,
I am try to restore a database from external hard drive and I am get the
errors below. Do you have any ideas how to resolve these errors?. Thank you
in advance.
Errors:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\SCData_prosum.mdf" failed with the operating system
error 3(The system cannot find the path specified.).
Msg 3156, Level 16, State 3, Line 1
File 'SCData_prosum' cannot be restored to 'E:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\SCData_prosum.mdf'. Use WITH MOVE to identify a
valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous
messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
-- Create databse file under: c:\mssql\data\...
c\msssql\Log\....
-- Here is my script try to restore the database.
EXECute Master.dbo.sp_dboption 'SCData_prosum', 'Single', True
GO
PRINT 'Begin restore SCData_prosum database, please wait...'
RESTORE DATABASE SCData_prosum
FROM DISK = 'C:\MSSQL\Backup\SCData_Prosum\SCData_prosum_backup_200709061759.bak'
WITH REPLACE,
RECOVERY,
STATS = 10
GO
EXECute Master.dbo.sp_dboption 'SCData_prosum', 'Single', False
GOHi
Does the directory "E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\" exist?
If not you will need to change the location of where the database file will
be placed
e.g. ( be careful if this wraps around)
RESTORE DATABASE SCData_prosum
FROM DISK ='C:\MSSQL\Backup\SCData_Prosum\SCData_prosum_backup_200709061759.bak'
WITH MOVE 'SCData_prosum_Data' TO 'C:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\SCData_prosum.mdf',
MOVE 'SCData_prosum_Log' TO ''C:\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\SCData_prosum.ldf';
GO
To get the name of the logical files and where it expects to put them you
can use
RESTORE FILELISTONLY SCData_prosum
FROM DISK ='C:\MSSQL\Backup\SCData_Prosum\SCData_prosum_backup_200709061759.bak'
GO
If the directory does exist then it may be a permissions issue.
John
"Lam Nguyen" wrote:
> Hi all,
> I am try to restore a database from external hard drive and I am get the
> errors below. Do you have any ideas how to resolve these errors?. Thank you
> in advance.
> Errors:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "E:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\SCData_prosum.mdf" failed with the operating system
> error 3(The system cannot find the path specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'SCData_prosum' cannot be restored to 'E:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\SCData_prosum.mdf'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 3119, Level 16, State 1, Line 1
> Problems were identified while planning for the RESTORE statement. Previous
> messages provide details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> -- Create databse file under: c:\mssql\data\...
> c\msssql\Log\....
> -- Here is my script try to restore the database.
> EXECute Master.dbo.sp_dboption 'SCData_prosum', 'Single', True
> GO
> PRINT 'Begin restore SCData_prosum database, please wait...'
> RESTORE DATABASE SCData_prosum
> FROM DISK => 'C:\MSSQL\Backup\SCData_Prosum\SCData_prosum_backup_200709061759.bak'
> WITH REPLACE,
> RECOVERY,
> STATS = 10
> GO
> EXECute Master.dbo.sp_dboption 'SCData_prosum', 'Single', False
> GO
>|||You are trying to replace your backup as John mentions.
If you are using SSMS to restore your backup then in the Restore Database
window click to Options from the left panel and check out "Restore the
database files as:" in the list view, you will see 3 columns. One of them is
Original File Name and the other is Restore As.
Both columns' values must be the same in your situation and they both must
be "E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SCData_prosum.mdf" so your
restore operation fails.
Change the values in the Restore As column. So you will be able to restore
your backup successfully.
--
Ekrem Ã?nsoy
"Lam Nguyen" <LamNguyen@.discussions.microsoft.com> wrote in message
news:DE41AA77-9CA3-427A-888C-F759BA1AFD07@.microsoft.com...
> Hi all,
> I am try to restore a database from external hard drive and I am get the
> errors below. Do you have any ideas how to resolve these errors?. Thank
> you
> in advance.
> Errors:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "E:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\SCData_prosum.mdf" failed with the operating
> system
> error 3(The system cannot find the path specified.).
> Msg 3156, Level 16, State 3, Line 1
> File 'SCData_prosum' cannot be restored to 'E:\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\SCData_prosum.mdf'. Use WITH MOVE to identify a
> valid location for the file.
> Msg 3119, Level 16, State 1, Line 1
> Problems were identified while planning for the RESTORE statement.
> Previous
> messages provide details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> -- Create databse file under: c:\mssql\data\...
> c\msssql\Log\....
> -- Here is my script try to restore the database.
> EXECute Master.dbo.sp_dboption 'SCData_prosum', 'Single', True
> GO
> PRINT 'Begin restore SCData_prosum database, please wait...'
> RESTORE DATABASE SCData_prosum
> FROM DISK => 'C:\MSSQL\Backup\SCData_Prosum\SCData_prosum_backup_200709061759.bak'
> WITH REPLACE,
> RECOVERY,
> STATS = 10
> GO
> EXECute Master.dbo.sp_dboption 'SCData_prosum', 'Single', False
> GO
>

Msg (Loading) after db name

I wanted to restore a database to a new location on a different server. I
did a backup to a file and then did a restore of that file onto the new
server. I got a message that the restore was successful, but when I opened
Enterprise Manager the database had "(Loading)" after the database name and
was grayed out. When I tried to select properties it gave me "Error 927:
Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
Can anyone help? Thanks.
DavidSounds like you might have left your database in a state where you can
restore a differential or additional transaction logs. Could you possibly
have restored using the "NORECOVERY" options, or set the "Leave Database
nonoperation...." radio button when doing your restore.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23qBVFFmQEHA.1960@.TK2MSFTNGP10.phx.gbl...
> I wanted to restore a database to a new location on a different server. I
> did a backup to a file and then did a restore of that file onto the new
> server. I got a message that the restore was successful, but when I
opened
> Enterprise Manager the database had "(Loading)" after the database name
and
> was grayed out. When I tried to select properties it gave me "Error 927:
> Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
> Can anyone help? Thanks.
> David
>|||Hi,
Execute the below command from query analyzer:-
restore database <dbname> with recovery
Thanks
Hari
MCDBA
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:#qBVFFmQEHA.1960@.TK2MSFTNGP10.phx.gbl...
> I wanted to restore a database to a new location on a different server. I
> did a backup to a file and then did a restore of that file onto the new
> server. I got a message that the restore was successful, but when I
opened
> Enterprise Manager the database had "(Loading)" after the database name
and
> was grayed out. When I tried to select properties it gave me "Error 927:
> Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
> Can anyone help? Thanks.
> David
>

Msg (Loading) after db name

I wanted to restore a database to a new location on a different server. I
did a backup to a file and then did a restore of that file onto the new
server. I got a message that the restore was successful, but when I opened
Enterprise Manager the database had "(Loading)" after the database name and
was grayed out. When I tried to select properties it gave me "Error 927:
Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
Can anyone help? Thanks.
David
Sounds like you might have left your database in a state where you can
restore a differential or additional transaction logs. Could you possibly
have restored using the "NORECOVERY" options, or set the "Leave Database
nonoperation...." radio button when doing your restore.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23qBVFFmQEHA.1960@.TK2MSFTNGP10.phx.gbl...
> I wanted to restore a database to a new location on a different server. I
> did a backup to a file and then did a restore of that file onto the new
> server. I got a message that the restore was successful, but when I
opened
> Enterprise Manager the database had "(Loading)" after the database name
and
> was grayed out. When I tried to select properties it gave me "Error 927:
> Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
> Can anyone help? Thanks.
> David
>
|||Hi,
Execute the below command from query analyzer:-
restore database <dbname> with recovery
Thanks
Hari
MCDBA
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:#qBVFFmQEHA.1960@.TK2MSFTNGP10.phx.gbl...
> I wanted to restore a database to a new location on a different server. I
> did a backup to a file and then did a restore of that file onto the new
> server. I got a message that the restore was successful, but when I
opened
> Enterprise Manager the database had "(Loading)" after the database name
and
> was grayed out. When I tried to select properties it gave me "Error 927:
> Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
> Can anyone help? Thanks.
> David
>
|||Yup. That was it...had the "Leave database nonoperation..." radio boxed
checked. Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Msg (Loading) after db name

I wanted to restore a database to a new location on a different server. I
did a backup to a file and then did a restore of that file onto the new
server. I got a message that the restore was successful, but when I opened
Enterprise Manager the database had "(Loading)" after the database name and
was grayed out. When I tried to select properties it gave me "Error 927:
Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
Can anyone help? Thanks.
DavidSounds like you might have left your database in a state where you can
restore a differential or additional transaction logs. Could you possibly
have restored using the "NORECOVERY" options, or set the "Leave Database
nonoperation...." radio button when doing your restore.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23qBVFFmQEHA.1960@.TK2MSFTNGP10.phx.gbl...
> I wanted to restore a database to a new location on a different server. I
> did a backup to a file and then did a restore of that file onto the new
> server. I got a message that the restore was successful, but when I
opened
> Enterprise Manager the database had "(Loading)" after the database name
and
> was grayed out. When I tried to select properties it gave me "Error 927:
> Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
> Can anyone help? Thanks.
> David
>|||Hi,
Execute the below command from query analyzer:-
restore database <dbname> with recovery
Thanks
Hari
MCDBA
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:#qBVFFmQEHA.1960@.TK2MSFTNGP10.phx.gbl...
> I wanted to restore a database to a new location on a different server. I
> did a backup to a file and then did a restore of that file onto the new
> server. I got a message that the restore was successful, but when I
opened
> Enterprise Manager the database had "(Loading)" after the database name
and
> was grayed out. When I tried to select properties it gave me "Error 927:
> Database 'wcccu_db' cannot be opened. It is in the middle of a restore."
> Can anyone help? Thanks.
> David
>|||Yup. That was it...had the "Leave database nonoperation..." radio boxed
checked. Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

MSDTC - The Microsoft Clustering Service failed restore a registry

Cluster active-active.
The node B was reinstalled after a hardware failure.
Now the node B is ok. The failover is ok, except because I receive the
message"The Microsoft Clustering Service failed restore a registry key for
resource SQL Server " when I move the Instance B from Node A to Node B.
How can I install MSDTC without affect the Node A (without unistall msdtc
from this node) ?
Node A - Instance A (msdtc resource is online)
thanks for your help.
You don't install DTC, it is already on the machine. It is part of the OS.
You just configure the resource into the cluster. All you should have to do
is edit the properties of the DTC resource and add NodeB as a possible
owner.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Shima" <Shima@.discussions.microsoft.com> wrote in message
news:F5B0FAB8-C20C-4009-96A9-98AB79D5D965@.microsoft.com...
> Cluster active-active.
> The node B was reinstalled after a hardware failure.
> Now the node B is ok. The failover is ok, except because I receive the
> message"The Microsoft Clustering Service failed restore a registry key for
> resource SQL Server " when I move the Instance B from Node A to Node B.
> How can I install MSDTC without affect the Node A (without unistall msdtc
> from this node) ?
> Node A - Instance A (msdtc resource is online)
> thanks for your help.