PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
&start=20&
hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as w
ell. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). D
id you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different na
me
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log
),
-- and all files in the restored filegroup (in this example, mywind_data_2 i
s the
-- only file in sales) are moved to a new location. The log is then recover
ed:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msft
ngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups includi
ng
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get t
he
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirm
s
> my suspisions from someone with a microsoft.com email address
>
&start=20&hl=en&lr=&ie=UTF
-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a fu
ll
> database backup. Partial database restore of file backups is not supported
."
> What it says is "Partial database restore of file backups is not supported
"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

Partial searches over a lot of fields

Hi All,
I have the following scenario. I have a table called Invoice. This
has around 30 columns of which i have to do a retrieval based on
filter conditions on 10 columns. These filters need to be partial
searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
'Aaron', now i should be able to search the customer as 'ar' and it
should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
on which this like '%x%' search has to be done and there will
practically be hudreds of thousands of rows. can anybody suggest me to
improve the performance of such a query. Currently what i am thinkin
of is
select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
'%' and etc.
P.S. am using ASP.Net as the front end.ArunPrakash (arunprakashb@.yahoo.com) writes:
> I have the following scenario. I have a table called Invoice. This
> has around 30 columns of which i have to do a retrieval based on
> filter conditions on 10 columns. These filters need to be partial
> searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
> 'Aaron', now i should be able to search the customer as 'ar' and it
> should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
> on which this like '%x%' search has to be done and there will
> practically be hudreds of thousands of rows. can anybody suggest me to
> improve the performance of such a query. Currently what i am thinkin
> of is
> select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
> '%' and etc.

You can use SELECT TOP or SET ROWCOUNT to restrict the number of rows
returned. A good value is probably 2000. If you get 2000 rows, you tell
the user to refine his conditions.

Also keep in mind, that there could be a great difference in performance
when searching for names that start with 'Ar', or have 'ar' anywhere in
the name. If the search column is indexed, that index can be used
for the case "starts with", but not "contains".

There are also a couple of considerations of how to compose the query
to make the search effective. You may be interested in the article
http://www.sommarskog.se/dyn-search.html on my web site.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

partial search performance

To search for partial string we are using WHERE column_name LIKE '%str%'.
But its taking long time to return the results. Is there any way to improve
the performance.
What is the datatype of the column you are searching against. An index would
help majorly but it has to been an allowable type.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
> To search for partial string we are using WHERE column_name LIKE
> '%str%'.
> But its taking long time to return the results. Is there any way to
> improve
> the performance.
|||datatype of the column is varchar(max). This column contains the description
of the product. Users want to search for a string rather than a word. If
search by a word is the requirement, I would have gone with FTE.
"Warren Brunk" wrote:

> What is the datatype of the column you are searching against. An index would
> help majorly but it has to been an allowable type.
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
> "Ramu" <Ramu@.discussions.microsoft.com> wrote in message
> news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
>
>
|||using a wildcard at the start of a LIKE expression does not use an INDEX SEEK
but rather an INDEX SCAN so the query will be slow even if you created
indexes on this column
http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
Misbah Arefin
"Ramu" wrote:
[vbcol=seagreen]
> datatype of the column is varchar(max). This column contains the description
> of the product. Users want to search for a string rather than a word. If
> search by a word is the requirement, I would have gone with FTE.
>
> "Warren Brunk" wrote:

Partial row at end of file - best way to handle?

Hi,

I have a file where there is a partial row at the end. It doesn't cause an error, but I get a "partial row" warning during execution.

What do most people do with these partial rows? Do they just ignore them as long as they don't cause errors? Or is it better to handle the partial row with a conditional split, for example?

Just wondering what other people's thoughts on this are. I tend to be of the "get rid of it" camp, but maybe that's overkill? Just looking for opinions, best practices.

Thanks

Hi Sadie,

I'm not sure I know what you mean. Can you paste the last few lines of the file up here? Also, tell us how you have the connection manager configured (i.e. delimited, fixed-length etc...)

-Jamie

|||

Data:

col1, col2, col3, col4, etc

col1, col2, col3, col4, etc

col1, col2, col3, col4, etc

col1, col2, col3, col4, etc

col1 --> partial row

This gives a warning, but doesn't cause an error.

Comma delimited conn mgr.

|||Is it always the last row, or can it be in the middle?|||

It's only the last row.

If you have partial rows in the middle of the file, this causes an error (unless you're ignoring errors). But SSIS ignores the partial row at the very end without any special error handling.

|||

sadie519590 wrote:

It's only the last row.

If you have partial rows in the middle of the file, this causes an error (unless you're ignoring errors). But SSIS ignores the partial row at the very end without any special error handling.

Then you can leave it. No harm, no foul. Why is the last row incomplete though? Can it be fixed at the source?|||

Can't be fixed at the source, these are automatically generated extracts that get automatically ftp'd and loaded.

Which goes back to my original question:

What is the best practice for handling these partial rows at the end of file?

If they're ignored, SSIS gives a warning, but no errors. So the package still runs.

But I can also add a conditional split to remove it, which is extra work, but ensures all junk rows are removed.

I was just wondering what other people do with these partial end of file rows?

|||Is the row a footer or something? What makes it "partial"?

I don't accept "bad" files for input into SSIS. Unless the mandate is given from above, I consider files like this to be bad and those who build it need to fix it.

With that said, it's up to you. I don't think there is a best practice. Use whatever you're comfortable with. If you log warnings, and get tired of sifting through them, then perhaps you want to graciously handle the bad rows.|||Yes, these are footers, not bad rows, per se.|||Not sure there is a best practice on this, as Phil said. If it is not causing a problem, I'd leave it.

Partial Restore from Log

We have a database for our Beta site whose backups were not working successfully. A developer inadvertently dropped and recreated a
table with important data in it today, and so we are stuck without a backup.
We would like to restore the database to the state it was in this morning, and hoped to be able to do it from the transaction log.
The problem is, I need to apply the log only up until the point that the DROP TABLE was issued as I don't want to lose the data
again.
All the documentation seems to indicate that what we want to do is possible, but I can't find how to do specifically what we want to
do. Can someone point me in the right direction?
Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
The STOPAT parameter allows you to identify the point in time where you
would like to restore your database to. The time you would want to use is
the point just before your table got deleted.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:O6LTBdRPEHA.2132@.TK2MSFTNGP11.phx.gbl...
> We have a database for our Beta site whose backups were not working
successfully. A developer inadvertently dropped and recreated a
> table with important data in it today, and so we are stuck without a
backup.
> We would like to restore the database to the state it was in this morning,
and hoped to be able to do it from the transaction log.
> The problem is, I need to apply the log only up until the point that the
DROP TABLE was issued as I don't want to lose the data
> again.
> All the documentation seems to indicate that what we want to do is
possible, but I can't find how to do specifically what we want to
> do. Can someone point me in the right direction?
>
>
|||> Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
Thanks- I was just trying to get STOPAT to work, but I seem to be missing a basic concept or two about the RESTORE LOG command.
I created a copy of my real database by scripting out all the tables and running the script. I figured I would restore the copy of
the database so I could leave all the other data in the production database in tact, and after the restore, I could just copy over
the data from the lost table. I tried:
RESTORE LOG CP2
FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log from the real database. (I have taken CPO offline
temporarily). But when I run the code above, I get the following message:
"Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH
NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally."
All the examples I can find seem to assume that I have a database backup that I am restoring from first, and _then_ I will apply the
log.
|||The basic concept of getting this to work, is making sure you have a
complete sequence of backups. Meaning you need at a mimimum a full database
backup and a transaction log backup where the table was deleted (provided no
other backups occured between the full and the transaction log backup) If
there were a number of transaction logs since the last full backup you need
all these backups. Also if you performed differential backups then based on
which files you are using for the restore you might need one of those. Do
you have all these backups, a complete sequence of backups from the full to
that last transaction log backup? If you don't have a complete set of
backups you will not be able to perform what I suggested.
Also the transaction log backup is not the actual transaction log (file
ending in .LDF), but separate file that is created when you issue a BACKUP
LOG command. Based on your restore command looks like you are trying to use
the actual transcation log for your "FROM DISK=" option.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:%23MqGiBSPEHA.308@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
Online.
> Thanks- I was just trying to get STOPAT to work, but I seem to be missing
a basic concept or two about the RESTORE LOG command.
> I created a copy of my real database by scripting out all the tables and
running the script. I figured I would restore the copy of
> the database so I could leave all the other data in the production
database in tact, and after the restore, I could just copy over
> the data from the lost table. I tried:
> RESTORE LOG CP2
> FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
> WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
> Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log
from the real database. (I have taken CPO offline
> temporarily). But when I run the code above, I get the following message:
> "Server: Msg 4306, Level 16, State 1, Line 1
> The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH
> NORECOVERY or WITH STANDBY for all but the final step.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally."
> All the examples I can find seem to assume that I have a database backup
that I am restoring from first, and _then_ I will apply the
> log.
>
>
|||> The basic concept of getting this to work, is making sure you have a
> complete sequence of backups.
Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the log was not backup up
at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in size).
I was hoping to accomplish this by starting with an empty database (the way it was when the log was started), and just applying the
complete log.
I'm getting a sinking feeling that this may not be possible?
|||If you don't have a full backup of you database then you are correct and you
can't restore from the transaction log. One glimmer of hope might be to try
and use a third party tools to get information out of the transaction log.
I've never used these. There is a tool called "Log Explorer" from Lumigent
which might help you out.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the log was not backup up
> at all, it should contain all the transactions since the birth of the
database. (The log file is some 5GB in size).
> I was hoping to accomplish this by starting with an empty database (the
way it was when the log was started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>
|||Thanks, I really appreciate your help. We're looking at alternative means of restoring the data from a sencondary source.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message news:uSmtaXSPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> If you don't have a full backup of you database then you are correct and you
> can't restore from the transaction log. One glimmer of hope might be to try
> and use a third party tools to get information out of the transaction log.
> I've never used these. There is a tool called "Log Explorer" from Lumigent
> which might help you out.
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
> news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> But as I understand it, since the log was not backup up
> database. (The log file is some 5GB in size).
> way it was when the log was started), and just applying the
>
|||<<Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up at all, it should contain all the transactions since the birth of the database.>>
Above is a common misconception. Until you do your first database backup, the database is in "auto log
truncate mode" which means that SQL Server will truncate the log when 70% full or at checkpoint. So, you
cannot be certain to find your log records even when using some log reader tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up
> at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in
size).
> I was hoping to accomplish this by starting with an empty database (the way it was when the log was
started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>

Partial Restore from Log

We have a database for our Beta site whose backups were not working successf
ully. A developer inadvertently dropped and recreated a
table with important data in it today, and so we are stuck without a backup.
We would like to restore the database to the state it was in this morning, a
nd hoped to be able to do it from the transaction log.
The problem is, I need to apply the log only up until the point that the DRO
P TABLE was issued as I don't want to lose the data
again.
All the documentation seems to indicate that what we want to do is possible,
but I can't find how to do specifically what we want to
do. Can someone point me in the right direction?Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
The STOPAT parameter allows you to identify the point in time where you
would like to restore your database to. The time you would want to use is
the point just before your table got deleted.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:O6LTBdRPEHA.2132@.TK2MSFTNGP11.phx.gbl...
> We have a database for our Beta site whose backups were not working
successfully. A developer inadvertently dropped and recreated a
> table with important data in it today, and so we are stuck without a
backup.
> We would like to restore the database to the state it was in this morning,
and hoped to be able to do it from the transaction log.
> The problem is, I need to apply the log only up until the point that the
DROP TABLE was issued as I don't want to lose the data
> again.
> All the documentation seems to indicate that what we want to do is
possible, but I can't find how to do specifically what we want to
> do. Can someone point me in the right direction?
>
>|||> Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Onlin
e.
Thanks- I was just trying to get STOPAT to work, but I seem to be missing a
basic concept or two about the RESTORE LOG command.
I created a copy of my real database by scripting out all the tables and run
ning the script. I figured I would restore the copy of
the database so I could leave all the other data in the production database
in tact, and after the restore, I could just copy over
the data from the lost table. I tried:
RESTORE LOG CP2
FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log f
rom the real database. (I have taken CPO offline
temporarily). But when I run the code above, I get the following message:
"Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STAN
DBY. Restart the restore sequence, specifying WITH
NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally."
All the examples I can find seem to assume that I have a database backup tha
t I am restoring from first, and _then_ I will apply the
log.|||The basic concept of getting this to work, is making sure you have a
complete sequence of backups. Meaning you need at a mimimum a full database
backup and a transaction log backup where the table was deleted (provided no
other backups occured between the full and the transaction log backup) If
there were a number of transaction logs since the last full backup you need
all these backups. Also if you performed differential backups then based on
which files you are using for the restore you might need one of those. Do
you have all these backups, a complete sequence of backups from the full to
that last transaction log backup? If you don't have a complete set of
backups you will not be able to perform what I suggested.
Also the transaction log backup is not the actual transaction log (file
ending in .LDF), but separate file that is created when you issue a BACKUP
LOG command. Based on your restore command looks like you are trying to use
the actual transcation log for your "FROM DISK=" option.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:%23MqGiBSPEHA.308@.TK2MSFTNGP11.phx.gbl...
Online.[vbcol=seagreen]
> Thanks- I was just trying to get STOPAT to work, but I seem to be missing
a basic concept or two about the RESTORE LOG command.
> I created a copy of my real database by scripting out all the tables and
running the script. I figured I would restore the copy of
> the database so I could leave all the other data in the production
database in tact, and after the restore, I could just copy over
> the data from the lost table. I tried:
> RESTORE LOG CP2
> FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
> WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
> Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log
from the real database. (I have taken CPO offline
> temporarily). But when I run the code above, I get the following message:
> "Server: Msg 4306, Level 16, State 1, Line 1
> The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH
> NORECOVERY or WITH STANDBY for all but the final step.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally."
> All the examples I can find seem to assume that I have a database backup
that I am restoring from first, and _then_ I will apply the
> log.
>
>|||> The basic concept of getting this to work, is making sure you have a
> complete sequence of backups.
Well the problem is, there are _no_ backups of the database or of the log. B
ut as I understand it, since the log was not backup up
at all, it should contain all the transactions since the birth of the databa
se. (The log file is some 5GB in size).
I was hoping to accomplish this by starting with an empty database (the way
it was when the log was started), and just applying the
complete log.
I'm getting a sinking feeling that this may not be possible?|||If you don't have a full backup of you database then you are correct and you
can't restore from the transaction log. One glimmer of hope might be to try
and use a third party tools to get information out of the transaction log.
I've never used these. There is a tool called "Log Explorer" from Lumigent
which might help you out.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the log was not backup up
> at all, it should contain all the transactions since the birth of the
database. (The log file is some 5GB in size).
> I was hoping to accomplish this by starting with an empty database (the
way it was when the log was started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>|||Thanks, I really appreciate your help. We're looking at alternative means of
restoring the data from a sencondary source.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message news:uSmtaXSPEHA.2636@.TK2MSFT
NGP10.phx.gbl...
> If you don't have a full backup of you database then you are correct and y
ou
> can't restore from the transaction log. One glimmer of hope might be to t
ry
> and use a third party tools to get information out of the transaction log.
> I've never used these. There is a tool called "Log Explorer" from Lumigen
t
> which might help you out.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
> news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> But as I understand it, since the log was not backup up
> database. (The log file is some 5GB in size).
> way it was when the log was started), and just applying the
>|||<<Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the
log was not backup up at all, it should contain all the transactions since t
he birth of the database.>>
Above is a common misconception. Until you do your first database backup, th
e database is in "auto log
truncate mode" which means that SQL Server will truncate the log when 70% fu
ll or at checkpoint. So, you
cannot be certain to find your log records even when using some log reader t
ool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message news:OK668PSPEHA.1512@.TK2MSFT
NGP10.phx.gbl...
> Well the problem is, there are _no_ backups of the database or of the log. But as
I understand it, since the
log was not backup up
> at all, it should contain all the transactions since the birth of the database. (T
he log file is some 5GB in
size).
> I was hoping to accomplish this by starting with an empty database (the way it was
when the log was
started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>

Partial Restore from Log

We have a database for our Beta site whose backups were not working successfully. A developer inadvertently dropped and recreated a
table with important data in it today, and so we are stuck without a backup.
We would like to restore the database to the state it was in this morning, and hoped to be able to do it from the transaction log.
The problem is, I need to apply the log only up until the point that the DROP TABLE was issued as I don't want to lose the data
again.
All the documentation seems to indicate that what we want to do is possible, but I can't find how to do specifically what we want to
do. Can someone point me in the right direction?Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
The STOPAT parameter allows you to identify the point in time where you
would like to restore your database to. The time you would want to use is
the point just before your table got deleted.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:O6LTBdRPEHA.2132@.TK2MSFTNGP11.phx.gbl...
> We have a database for our Beta site whose backups were not working
successfully. A developer inadvertently dropped and recreated a
> table with important data in it today, and so we are stuck without a
backup.
> We would like to restore the database to the state it was in this morning,
and hoped to be able to do it from the transaction log.
> The problem is, I need to apply the log only up until the point that the
DROP TABLE was issued as I don't want to lose the data
> again.
> All the documentation seems to indicate that what we want to do is
possible, but I can't find how to do specifically what we want to
> do. Can someone point me in the right direction?
>
>|||> Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books Online.
Thanks- I was just trying to get STOPAT to work, but I seem to be missing a basic concept or two about the RESTORE LOG command.
I created a copy of my real database by scripting out all the tables and running the script. I figured I would restore the copy of
the database so I could leave all the other data in the production database in tact, and after the restore, I could just copy over
the data from the lost table. I tried:
RESTORE LOG CP2
FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log from the real database. (I have taken CPO offline
temporarily). But when I run the code above, I get the following message:
"Server: Msg 4306, Level 16, State 1, Line 1
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH
NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally."
All the examples I can find seem to assume that I have a database backup that I am restoring from first, and _then_ I will apply the
log.|||The basic concept of getting this to work, is making sure you have a
complete sequence of backups. Meaning you need at a mimimum a full database
backup and a transaction log backup where the table was deleted (provided no
other backups occured between the full and the transaction log backup) If
there were a number of transaction logs since the last full backup you need
all these backups. Also if you performed differential backups then based on
which files you are using for the restore you might need one of those. Do
you have all these backups, a complete sequence of backups from the full to
that last transaction log backup? If you don't have a complete set of
backups you will not be able to perform what I suggested.
Also the transaction log backup is not the actual transaction log (file
ending in .LDF), but separate file that is created when you issue a BACKUP
LOG command. Based on your restore command looks like you are trying to use
the actual transcation log for your "FROM DISK=" option.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:%23MqGiBSPEHA.308@.TK2MSFTNGP11.phx.gbl...
> > Look at the "STOPAT" parameter of the "RESTORE LOG" command in Books
Online.
> Thanks- I was just trying to get STOPAT to work, but I seem to be missing
a basic concept or two about the RESTORE LOG command.
> I created a copy of my real database by scripting out all the tables and
running the script. I figured I would restore the copy of
> the database so I could leave all the other data in the production
database in tact, and after the restore, I could just copy over
> the data from the lost table. I tried:
> RESTORE LOG CP2
> FROM DISK = 'E:\DATA\MAC\MSSQL\LOG\CPO_LOG.LDF'
> WITH NORECOVERY , STOPAT = '2004-02-12 8:30:00'
> Where CP2 is the fresh, empty copy of the database, and CPO_LOG is the log
from the real database. (I have taken CPO offline
> temporarily). But when I run the code above, I get the following message:
> "Server: Msg 4306, Level 16, State 1, Line 1
> The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH
> NORECOVERY or WITH STANDBY for all but the final step.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally."
> All the examples I can find seem to assume that I have a database backup
that I am restoring from first, and _then_ I will apply the
> log.
>
>|||> The basic concept of getting this to work, is making sure you have a
> complete sequence of backups.
Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the log was not backup up
at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in size).
I was hoping to accomplish this by starting with an empty database (the way it was when the log was started), and just applying the
complete log.
I'm getting a sinking feeling that this may not be possible?|||If you don't have a full backup of you database then you are correct and you
can't restore from the transaction log. One glimmer of hope might be to try
and use a third party tools to get information out of the transaction log.
I've never used these. There is a tool called "Log Explorer" from Lumigent
which might help you out.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> > The basic concept of getting this to work, is making sure you have a
> > complete sequence of backups.
> Well the problem is, there are _no_ backups of the database or of the log.
But as I understand it, since the log was not backup up
> at all, it should contain all the transactions since the birth of the
database. (The log file is some 5GB in size).
> I was hoping to accomplish this by starting with an empty database (the
way it was when the log was started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>|||Thanks, I really appreciate your help. We're looking at alternative means of restoring the data from a sencondary source.
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message news:uSmtaXSPEHA.2636@.TK2MSFTNGP10.phx.gbl...
> If you don't have a full backup of you database then you are correct and you
> can't restore from the transaction log. One glimmer of hope might be to try
> and use a third party tools to get information out of the transaction log.
> I've never used these. There is a tool called "Log Explorer" from Lumigent
> which might help you out.
> --
> ----
> ----
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message
> news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> > > The basic concept of getting this to work, is making sure you have a
> > > complete sequence of backups.
> >
> > Well the problem is, there are _no_ backups of the database or of the log.
> But as I understand it, since the log was not backup up
> > at all, it should contain all the transactions since the birth of the
> database. (The log file is some 5GB in size).
> >
> > I was hoping to accomplish this by starting with an empty database (the
> way it was when the log was started), and just applying the
> > complete log.
> >
> > I'm getting a sinking feeling that this may not be possible?
> >
> >
> >
> >
> >
>|||<<Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up at all, it should contain all the transactions since the birth of the database.>>
Above is a common misconception. Until you do your first database backup, the database is in "auto log
truncate mode" which means that SQL Server will truncate the log when 70% full or at checkpoint. So, you
cannot be certain to find your log records even when using some log reader tool.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erik Moore" <erikmoore@.austin.rr.nospam.com> wrote in message news:OK668PSPEHA.1512@.TK2MSFTNGP10.phx.gbl...
> > The basic concept of getting this to work, is making sure you have a
> > complete sequence of backups.
> Well the problem is, there are _no_ backups of the database or of the log. But as I understand it, since the
log was not backup up
> at all, it should contain all the transactions since the birth of the database. (The log file is some 5GB in
size).
> I was hoping to accomplish this by starting with an empty database (the way it was when the log was
started), and just applying the
> complete log.
> I'm getting a sinking feeling that this may not be possible?
>
>
>