Manually roll-forward transactions

I have a full backup of my database, a transaction log backup (taken after
the full). The data files have been deleted, but I do have the transaction
log. After the last transaction log backup, there where a few important
transactions that happened that I need to roll-forward. Is there a way to
get those transactions back if I only have the transaction log, and not a
backup of it?
Thanks in advance.Use the following command:
BACKUP LOG databasename WITH NO_TRUNCATE
This will back up your current log even if the data files are not available.
Then you'll be able to restore your database using your last full backup and
subsequent log backups.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> I have a full backup of my database, a transaction log backup (taken after
> the full). The data files have been deleted, but I do have the
transaction
> log. After the last transaction log backup, there where a few important
> transactions that happened that I need to roll-forward. Is there a way to
> get those transactions back if I only have the transaction log, and not a
> backup of it?
> Thanks in advance.
>|||So if I start up SQL Server, after I have lost my data files (from a lost
disk that housed them), I will be able to do a backup of the log? Wouldn't
the currpted status of my database prevent me from doing the tran log
backup?
Paul
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> Use the following command:
> BACKUP LOG databasename WITH NO_TRUNCATE
> This will back up your current log even if the data files are not
available.
> Then you'll be able to restore your database using your last full backup
and
> subsequent log backups.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 Programming by Example
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > I have a full backup of my database, a transaction log backup (taken
after
> > the full). The data files have been deleted, but I do have the
> transaction
> > log. After the last transaction log backup, there where a few important
> > transactions that happened that I need to roll-forward. Is there a way
to
> > get those transactions back if I only have the transaction log, and not
a
> > backup of it?
> >
> > Thanks in advance.
> >
> >
>|||No, it doesn't prevent you from doing it. Actually, it's something very easy
to test. Just stop sql server, delete data files, restart sql server and
then issue the "magic" command.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:uKVQ0$p6DHA.2644@.TK2MSFTNGP11.phx.gbl...
> So if I start up SQL Server, after I have lost my data files (from a lost
> disk that housed them), I will be able to do a backup of the log?
Wouldn't
> the currpted status of my database prevent me from doing the tran log
> backup?
> Paul
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Use the following command:
> > BACKUP LOG databasename WITH NO_TRUNCATE
> > This will back up your current log even if the data files are not
> available.
> > Then you'll be able to restore your database using your last full backup
> and
> > subsequent log backups.
> >
> > --
> > Carlos E. Rojas
> > SQL Server MVP
> > Co-Author SQL Server 2000 Programming by Example
> >
> >
> > "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> > news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
> > > I have a full backup of my database, a transaction log backup (taken
> after
> > > the full). The data files have been deleted, but I do have the
> > transaction
> > > log. After the last transaction log backup, there where a few
important
> > > transactions that happened that I need to roll-forward. Is there a
way
> to
> > > get those transactions back if I only have the transaction log, and
not
> a
> > > backup of it?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>

Manually roll-forward transactions

I have a full backup of my database, a transaction log backup (taken after
the full). The data files have been deleted, but I do have the transaction
log. After the last transaction log backup, there where a few important
transactions that happened that I need to roll-forward. Is there a way to
get those transactions back if I only have the transaction log, and not a
backup of it?
Thanks in advance.Use the following command:
BACKUP LOG databasename WITH NO_TRUNCATE
This will back up your current log even if the data files are not available.
Then you'll be able to restore your database using your last full backup and
subsequent log backups.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I have a full backup of my database, a transaction log backup (taken after
> the full). The data files have been deleted, but I do have the

---

transaction

quote:

---

> log. After the last transaction log backup, there where a few important
> transactions that happened that I need to roll-forward. Is there a way to
> get those transactions back if I only have the transaction log, and not a
> backup of it?
> Thanks in advance.
>

---

|||So if I start up SQL Server, after I have lost my data files (from a lost
disk that housed them), I will be able to do a backup of the log? Wouldn't
the currpted status of my database prevent me from doing the tran log
backup?
Paul
"Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Use the following command:
> BACKUP LOG databasename WITH NO_TRUNCATE
> This will back up your current log even if the data files are not

---

available.

quote:

---

> Then you'll be able to restore your database using your last full backup

---

and

quote:

---

> subsequent log backups.
> --
> Carlos E. Rojas
> SQL Server MVP
> Co-Author SQL Server 2000 programming by Example
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:u1ehu4p6DHA.1052@.TK2MSFTNGP12.phx.gbl...
after[QUOTE]
> transaction
to[QUOTE]
a[QUOTE]
>

---

|||No, it doesn't prevent you from doing it. Actually, it's something very easy
to test. Just stop sql server, delete data files, restart sql server and
then issue the "magic" command.
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:uKVQ0$p6DHA.2644@.TK2MSFTNGP11.phx.gbl...

quote:

---

> So if I start up SQL Server, after I have lost my data files (from a lost
> disk that housed them), I will be able to do a backup of the log?

---

Wouldn't

quote:

---

> the currpted status of my database prevent me from doing the tran log
> backup?
> Paul
> "Carlos Eduardo Rojas" <carloser@.mindspring.com> wrote in message
> news:uurB46p6DHA.1948@.TK2MSFTNGP12.phx.gbl...
> available.
> and
> after
important[QUOTE]
way[QUOTE]
> to
not[QUOTE]
> a
>

---

Manipulate the 'deleted record' flag on DBF files

Hi,

I am using OLE DB provider for Foxpro (VFPOLEDB.1) to query DBF files. I need to migrate the content of these files to a SQL Server 2005 database.

These DBF files have some (actually a lot) records marked as deleted using the DBF 'deleted' flag. When I submit a SELECT command to the OLE DB Provider, it returns me all the non-deleted records from the file.

It is very Ok as long as the 'deleted' rows actually have no more business value, but in my case, I need to do some processing on them, and even to migrate their data.

What are the options available for me to be able to query and differentiate the 'deleted' records ?

Thank you in advance,

Bertrand Larsy

Hi Bertrand,

Here's some example VB code to work with the deleted status of a row:

Try

Dim cn1 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn1.Open()
'-- Make some VFP data to play with
Dim cmd1 As New OleDbCommand( _
"Create Table TestDBF (Field1 I, Field2 C(10))", cn1)
Dim cmd2 As New OleDbCommand( _
"Insert Into TestDBF Values (1, 'Hello')", cn1)
Dim cmd3 As New OleDbCommand( _
"Insert Into TestDBF Values (2, 'World')", cn1)
Dim cmd4 As New OleDbCommand( _
"Delete From TestDBF Where Field1 = 1", cn1)
cmd1.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
cmd3.ExecuteNonQuery()
cmd4.ExecuteNonQuery()
cn1.Close()

Dim cn2 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn2.Open()

Dim cmd5 As New OleDbCommand( _
"Select * From TestDBF", cn2)
Dim da1 As New OleDbDataAdapter(cmd5)
Dim ds1 As New DataSet
Dim dr1 As DataRow
da1.Fill(ds1)
For Each dr1 In ds1.Tables(0).Rows
Console.WriteLine( _
dr1.Item(0).ToString() & ", " & dr1.Item(1).ToString)
Next
Console.ReadLine()
cn2.Close()

Dim cn3 As New OleDbConnection( _
"Provider=VFPOLEDB.1;Data Source=C:\Temp\;")
cn3.Open()

Dim cmd6 As New OleDbCommand( _
"Set Deleted Off", cn3)
cmd6.ExecuteNonQuery()
Dim cmd7 As New OleDbCommand( _
"Select Deleted('TestDBF') As IsDeleted, TestDBF.* From TestDBF", cn3)
Dim da2 As New OleDbDataAdapter(cmd7)
Dim ds2 As New DataSet
Dim dr2 As DataRow
da2.Fill(ds2)
For Each dr2 In ds2.Tables(0).Rows
Console.WriteLine( _
dr2.Item(0).ToString() & ", " & dr2.Item(1).ToString() & ", " & dr2.Item(2).ToString())
Next
Console.ReadLine()
cn2.Close()

Catch e As Exception
MsgBox(e.ToString())
End Try

|||

Thank you,

This is indeed a workable solution.

Now, is there a way to perform the same work in a T-SQL script ?

I tried, but could not manage to "chain" successfully a 'SET DELETED OFF' statement and a SELECT with OPENQUERY or OPENDATASOURCE:

SELECT [name]

FROM OPENQUERY(linkedServer,'SELECT name FROM Members')

This returns all records, but not the deleted ones

SELECT [name]

FROM OPENQUERY(linkedServer,'SET Deleted OFF;

SELECT name FROM Members')

This produces the error '

Msg 7357, Level 16, State 2, Line 1

Cannot process the object "SET Deleted OFF;

SELECT name FROM Members". The OLE DB provider "VFPOLEDB" for linked server "linkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

'

When doing it with an EXECUTE statement as pass-through query on a linked server (using OLE DB provider for FoxPro, of course), it says it successfully executes, but does not return the result of the select query:

DECLARE @.Query varchar(max)

SET @.Query='SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This returns all records, but not the deleted ones

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

This produces the query output 'Command(s) completed successfully.', but does not return any result.

Kind regards,

Bertrand Larsy

|||

Hi Bertrand,

I don't have time to try it now but it might work with a semicolon: "Set Deleted Off;Select * From MyTable..."

|||

Neither of the 2 following samples give any result:

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

DECLARE @.Query varchar(max)

SET @.Query='SET Deleted OFF;' + CHAR(13) + 'SELECT name FROM Members'

EXECUTE (@.Query) AT linkedServer

Anyway, thank you for your answers, I will make a CLR stored procedure of your first reply.

Kind regards,

Bertrand Larsy