Restore mdf file: live

Showing posts with label live. Show all posts

Friday, March 30, 2012

Restore DB to new Database Name Cannot see new Database SQL2000

Hi
I wanted to create a test database based on formally live data in SQL2000.
I created the new Database and then forced a restore from a backup.
Unfortunately the client workstations cannot see the new database.
Any ideas?
Many thanks
Mike
When you say client workstations... do mean via the QUery Analyzer?
Perhaps the user information is messed up in the restored database and
the users/roles need to be dropped and re-added? I'm guessing you
restored via EM.
"Mike" <mikevl@.paradise.net.nz> wrote in message
news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi
> I wanted to create a test database based on formally live data in SQL2000.
> I created the new Database and then forced a restore from a backup.
> Unfortunately the client workstations cannot see the new database.
> Any ideas?
> Many thanks
> Mike
>
|||Thanks for your help
It appears that the financial application based on SQL would only find
databases with a certain prefix name. Real cleaver. Took a while to find.
Many thanks
Mike
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:OhWVVsdzEHA.1404@.TK2MSFTNGP11.phx.gbl...
> When you say client workstations... do mean via the QUery Analyzer?
> Perhaps the user information is messed up in the restored database and
> the users/roles need to be dropped and re-added? I'm guessing you
> restored via EM.
> "Mike" <mikevl@.paradise.net.nz> wrote in message
> news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Restore DB to new Database Name Cannot see new Database SQL2000

Hi
I wanted to create a test database based on formally live data in SQL2000.
I created the new Database and then forced a restore from a backup.
Unfortunately the client workstations cannot see the new database.
Any ideas?
Many thanks
MikeWhen you say client workstations... do mean via the QUery Analyzer?
Perhaps the user information is messed up in the restored database and
the users/roles need to be dropped and re-added? I'm guessing you
restored via EM.
"Mike" <mikevl@.paradise.net.nz> wrote in message
news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi
> I wanted to create a test database based on formally live data in SQL2000.
> I created the new Database and then forced a restore from a backup.
> Unfortunately the client workstations cannot see the new database.
> Any ideas?
> Many thanks
> Mike
>|||Thanks for your help
It appears that the financial application based on SQL would only find
databases with a certain prefix name. Real cleaver. Took a while to find.
Many thanks
Mike
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:OhWVVsdzEHA.1404@.TK2MSFTNGP11.phx.gbl...
> When you say client workstations... do mean via the QUery Analyzer?
> Perhaps the user information is messed up in the restored database and
> the users/roles need to be dropped and re-added? I'm guessing you
> restored via EM.
> "Mike" <mikevl@.paradise.net.nz> wrote in message
> news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
>> Hi
>> I wanted to create a test database based on formally live data in
>> SQL2000.
>> I created the new Database and then forced a restore from a backup.
>> Unfortunately the client workstations cannot see the new database.
>> Any ideas?
>> Many thanks
>> Mike
>>
>

Restore DB to new Database Name Cannot see new Database SQL2000

Hi
I wanted to create a test database based on formally live data in SQL2000.
I created the new Database and then forced a restore from a backup.
Unfortunately the client workstations cannot see the new database.
Any ideas?
Many thanks
MikeWhen you say client workstations... do mean via the QUery Analyzer?
Perhaps the user information is messed up in the restored database and
the users/roles need to be dropped and re-added? I'm guessing you
restored via EM.
"Mike" <mikevl@.paradise.net.nz> wrote in message
news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi
> I wanted to create a test database based on formally live data in SQL2000.
> I created the new Database and then forced a restore from a backup.
> Unfortunately the client workstations cannot see the new database.
> Any ideas?
> Many thanks
> Mike
>|||Thanks for your help
It appears that the financial application based on SQL would only find
databases with a certain prefix name. Real cleaver. Took a while to find.
Many thanks
Mike
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:OhWVVsdzEHA.1404@.TK2MSFTNGP11.phx.gbl...
> When you say client workstations... do mean via the QUery Analyzer?
> Perhaps the user information is messed up in the restored database and
> the users/roles need to be dropped and re-added? I'm guessing you
> restored via EM.
> "Mike" <mikevl@.paradise.net.nz> wrote in message
> news:uXNxm2bzEHA.2624@.TK2MSFTNGP11.phx.gbl...
>

Monday, March 26, 2012

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?
Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?
|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?
|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:

> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>
|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:

> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:

> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:

> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>
>

Restore database project problem

I have a development db, test db and live db.
I want to copy my development db to my test db.
I've backed both up and then restored the test db using the development db
backup
changing the file and log paths.
However when I go into the test db the data in the tables is not correct.
For example in the development db I have 10 rows in one table but in the
corresponding table in test db I only have the 2 original Test db rows.
Can you explain why?Hi
Did youn follow theses steps
1) BACKUP DATABASE TO..... (Develop database)
2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
Darabase (Step1))
Probably during the RESTORE from the Develop database ,users do Insert to
the Develop DataBase, do not they?
As result you see different data.
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Perhaps your backup file contains more than one database backup and the
first (oldest) is restored by default. You can list the contents with
RESTORE HEADERONLY and specify the desired one with the FILE parameter on
the RESTORE statement.
RESTORE HEADERONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Also, you can specify the INIT option on your BACKUP command to overwrite
instead of appending.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"lindab" <lindab@.discussions.microsoft.com> wrote in message
news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
>I have a development db, test db and live db.
> I want to copy my development db to my test db.
> I've backed both up and then restored the test db using the development db
> backup
> changing the file and log paths.
> However when I go into the test db the data in the tables is not correct.
> For example in the development db I have 10 rows in one table but in the
> corresponding table in test db I only have the 2 original Test db rows.
> Can you explain why?|||Hi,
No -one was working on either db while I did the backup and restore.
Linda
"Uri Dimant" wrote:
> Hi
> Did youn follow theses steps
> 1) BACKUP DATABASE TO..... (Develop database)
> 2) RESTORE DATABASE Test FROM DISK ='......' (.BAK file from the Develop
> Darabase (Step1))
> Probably during the RESTORE from the Develop database ,users do Insert to
> the Develop DataBase, do not they?
> As result you see different data.
>
>
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
> >I have a development db, test db and live db.
> > I want to copy my development db to my test db.
> > I've backed both up and then restored the test db using the development db
> > backup
> > changing the file and log paths.
> > However when I go into the test db the data in the tables is not correct.
> > For example in the development db I have 10 rows in one table but in the
> > corresponding table in test db I only have the 2 original Test db rows.
> > Can you explain why?
>
>|||I have retried the backup and restore - this time making sure I had selected
the overwrite option and the restore has worked properly.
Many thanks to both of you for your help.
"Dan Guzman" wrote:
> Perhaps your backup file contains more than one database backup and the
> first (oldest) is restored by default. You can list the contents with
> RESTORE HEADERONLY and specify the desired one with the FILE parameter on
> the RESTORE statement.
> RESTORE HEADERONLY
> FROM DISK='C:\Backups\MyDatabase.bak'
> Also, you can specify the INIT option on your BACKUP command to overwrite
> instead of appending.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "lindab" <lindab@.discussions.microsoft.com> wrote in message
> news:08DCAF73-D293-4896-8AFE-230504DAFF07@.microsoft.com...
> >I have a development db, test db and live db.
> > I want to copy my development db to my test db.
> > I've backed both up and then restored the test db using the development db
> > backup
> > changing the file and log paths.
> > However when I go into the test db the data in the tables is not correct.
> > For example in the development db I have 10 rows in one table but in the
> > corresponding table in test db I only have the 2 original Test db rows.
> > Can you explain why?
>
>

Wednesday, March 21, 2012

restore database based on log file

Dear sir,
We have a live database, we thought that we backup it everyday. However we
made a terrible mistake today. We did not find any backup file, so our only
hope is on the log file.
Does any one know how can I restore the database based on log file?
The mistake we made is that our programmer runs a update script
"UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
However, our programmer made a mistake on the WHERE clause, so every user is
updated now.
Any help on this?
regards,
Guoqi Zheng
http://www.ureader.comThis is a multi-part message in MIME format.
--090703010304090403040702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
guoqi zheng wrote:
>Dear sir,
>We have a live database, we thought that we backup it everyday. However we
>made a terrible mistake today. We did not find any backup file, so our only
>hope is on the log file.
>Does any one know how can I restore the database based on log file?
>
You cannot. A RESTORE LOG statement must come after a RESTORE DATABASE
... WITH NORECOVERY statement. So if you have no database backup file
and only transaction log backup files, you may as well throw those
transaction log backup files in the bin as they are useless to you.
>The mistake we made is that our programmer runs a update script
>"UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
>However, our programmer made a mistake on the WHERE clause, so every user is
>updated now.
>Any help on this?
>
Got a copy on paper? Are there many rows in this table? You may have
to re-enter them by hand (assuming you know what the old usernames &
passwords were, that is). I did something similar in my humble
beginnings - I updated an insurance policy table to change the broker
for a particular policy but I forgot the WHERE clause (hey, it was when
I was first starting, I was just a lowly programmer), and at that stage
we hadn't set up a proper backup strategy. We had to re-enter all the
correct broker codes for each policy by hand (there were about 4000
policies in the table) - took half a dozen people 2 or 3 days (luckily
we had a policy listing print out that was only a couple days old).
The most important thing I learnt from that embarrassing mistake was to
start each batch with a BEGIN TRAN statement and only COMMIT the
transaction when you've checked the data to make sure it's right
(otherwise do a ROLLBACK).
Good luck.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
--090703010304090403040702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
guoqi zheng wrote:
<blockquote cite="mid2c47e60de8294ace94a284ffc1ea6492@.ureader.com"
type="cite">
<pre wrap="">Dear sir,
We have a live database, we thought that we backup it everyday. However we
made a terrible mistake today. We did not find any backup file, so our only
hope is on the log file.
Does any one know how can I restore the database based on log file?
</pre>
</blockquote>
<tt>You cannot. A RESTORE LOG statement must come after a RESTORE
DATABASE ... WITH NORECOVERY statement. So if you have no database
backup file and only transaction log backup files, you may as well
throw those transaction log backup files in the bin as they are useless
to you. 
</tt>
<blockquote cite="mid2c47e60de8294ace94a284ffc1ea6492@.ureader.com"
type="cite">
<pre wrap="">The mistake we made is that our programmer runs a update script
"UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
However, our programmer made a mistake on the WHERE clause, so every user is
updated now.
Any help on this?
</pre>
</blockquote>
<tt>Got a copy on paper? Are there many rows in this table? You may
have to re-enter them by hand (assuming you know what the old usernames
& passwords were, that is). I did something similar in my humble
beginnings - I updated an insurance policy table to change the broker
for a particular policy but I forgot the WHERE clause (hey, it was when
I was first starting, I was just a lowly programmer), and at that stage
we hadn't set up a proper backup strategy. We had to re-enter all the
correct broker codes for each policy by hand (there were about 4000
policies in the table) - took half a dozen people 2 or 3 days (luckily
we had a policy listing print out that was only a couple days old). 
 
The most important thing I learnt from that embarrassing mistake was to
start each batch with a BEGIN TRAN statement and only COMMIT the
transaction when you've checked the data to make sure it's right
(otherwise do a ROLLBACK). 
 
Good luck. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
</body>
</html>
--090703010304090403040702--|||"guoqi zheng" wrote:
> Dear sir,
> We have a live database, we thought that we backup it everyday. However we
> made a terrible mistake today. We did not find any backup file, so our only
> hope is on the log file.
> Does any one know how can I restore the database based on log file?
> The mistake we made is that our programmer runs a update script
> "UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
> However, our programmer made a mistake on the WHERE clause, so every user is
> updated now.
> Any help on this?
> regards,
> Guoqi Zheng
> http://www.ureader.com
>
You should use LogExplorer .This tool scan log files and you can rollback
your operation.Don't worry,it's easy.|||A quick search a few products on the net is named "Log Explorer", could you
clarify which one you're talking about?
"luyan" <luyan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
> You should use LogExplorer .This tool scan log files and you can rollback
> your operation.Don't worry,it's easy.|||Lumigent Log Explorer
--
HTH. Ryan
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:OOzCgl0GGHA.984@.tk2msftngp13.phx.gbl...
>A quick search a few products on the net is named "Log Explorer", could you
>clarify which one you're talking about?
> "luyan" <luyan@.discussions.microsoft.com>
> ¼¶¼g©ó¶l¥ó·s»D:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
>> You should use LogExplorer .This tool scan log files and you can rollback
>> your operation.Don't worry,it's easy.
>|||Just curious how this works. The transaction file ought to (I'm not sure)
save the steps of changes made to the database only. Is it supposed to store
the origional value of the UPDATE action? And store the whole set of records
when perform delete?
"luyan" <luyan@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
>
> "guoqi zheng" wrote:
>> Dear sir,
>> We have a live database, we thought that we backup it everyday. However
>> we
>> made a terrible mistake today. We did not find any backup file, so our
>> only
>> hope is on the log file.
>> Does any one know how can I restore the database based on log file?
>> The mistake we made is that our programmer runs a update script
>> "UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
>> However, our programmer made a mistake on the WHERE clause, so every user
>> is
>> updated now.
>> Any help on this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>
> You should use LogExplorer .This tool scan log files and you can rollback
> your operation.Don't worry,it's easy.|||Note that if the database is in simple recovery mode or if no database backup has ever been
produced, the log file is un "auto-truncate mode" meaning that the log records necessary to undo the
operations might not exist in the ldf file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"luyan" <luyan@.discussions.microsoft.com> wrote in message
news:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
>
> "guoqi zheng" wrote:
>> Dear sir,
>> We have a live database, we thought that we backup it everyday. However we
>> made a terrible mistake today. We did not find any backup file, so our only
>> hope is on the log file.
>> Does any one know how can I restore the database based on log file?
>> The mistake we made is that our programmer runs a update script
>> "UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId = xxx"
>> However, our programmer made a mistake on the WHERE clause, so every user is
>> updated now.
>> Any help on this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>
> You should use LogExplorer .This tool scan log files and you can rollback
> your operation.Don't worry,it's easy.|||That's a very good point, before going down the Log Explorer route what does
the following give you :-
SELECT DATABASEPROPERTYEX('YourDbNameHere', 'Recovery')
If it returns SIMPLE there is no log for the Lumigent tools to work with...
--
HTH. Ryan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23$y2$U1GGHA.3728@.tk2msftngp13.phx.gbl...
> Note that if the database is in simple recovery mode or if no database
> backup has ever been produced, the log file is un "auto-truncate mode"
> meaning that the log records necessary to undo the operations might not
> exist in the ldf file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "luyan" <luyan@.discussions.microsoft.com> wrote in message
> news:FEB5D8ED-008A-4B7A-BD77-4A229F535B13@.microsoft.com...
>>
>> "guoqi zheng" wrote:
>> Dear sir,
>> We have a live database, we thought that we backup it everyday. However
>> we
>> made a terrible mistake today. We did not find any backup file, so our
>> only
>> hope is on the log file.
>> Does any one know how can I restore the database based on log file?
>> The mistake we made is that our programmer runs a update script
>> "UPDATE tblUser SET UserName=xxxx,Password=232xxx,... WHERE UserId =>> xxx"
>> However, our programmer made a mistake on the WHERE clause, so every
>> user is
>> updated now.
>> Any help on this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>>
>> You should use LogExplorer .This tool scan log files and you can rollback
>> your operation.Don't worry,it's easy.
>

restore database based on log file

Friday, March 9, 2012

Restore at the point of failure

I m doing a test restore from my live to test db( to test restore at the
point of failure)
Below is my script :
-- Back up the currently active transaction log of Live DB
BACKUP LOG LIVEDB
TO disk = 'C:\Log2.TRN'
WITH NO_TRUNCATE
GO
-- Restore the database full backup from Live DB to Test DB
RESTORE DATABASE TESTDB
FROM DISK='C:\8A.M'
WITH NORECOVERY
GO
-- Restore the first transaction log backup of LIVEDB TO TESTDB
RESTORE LOG TESTDB
FROM DISK ='C:\LOG1.TRN'
WITH NORECOVERY
GO
-- Restore the final transaction log backup.(the last transaction log backup
)
RESTORE LOG TESTDB
FROM DISK ='C:\Log2.TRN'
WITH RECOVERY
GO
Somehow, I got the error as below:
Processed 1954 pages for database 'IMS', file 'Production60_Log' on file 1.
BACKUP LOG successfully processed 1954 pages in 0.432 seconds (37.037 MB/sec
).
Processed 88984 pages for database 'IERP60', file 'Production60_Data' on
file 1.
Processed 1 pages for database 'IERP60', file 'Production60_Log' on file 1.
RESTORE DATABASE successfully processed 88985 pages in 57.809 seconds
(12.609 MB/sec).
Server: Msg 4326, Level 16, State 1, Line 3
The log in this backup set terminates at LSN 42947000000004100001, which is
too early to apply to the database. A more recent log backup that includes
LSN 42947000000146100001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Processed 1954 pages for database 'IERP60', file 'Production60_Log' on file
1.
RESTORE LOG successfully processed 1954 pages in 2.127 seconds (7.522 MB/sec
).
I checked the transaction log that I applied is actually the first
transaction log backup of the day itself.Please note that my transaction log
backup date is 7 a.m and the full backup is 8a.m
Please advise
ThanksHi
If you use RESTORE HEADERONLY you will see the LSNs and therefore use the
correct file. You may find this discussion useful:
http://tinyurl.com/4kqkf
John
"sql fren" wrote:

> I m doing a test restore from my live to test db( to test restore at the
> point of failure)
> Below is my script :
> -- Back up the currently active transaction log of Live DB
> BACKUP LOG LIVEDB
> TO disk = 'C:\Log2.TRN'
> WITH NO_TRUNCATE
> GO
> -- Restore the database full backup from Live DB to Test DB
> RESTORE DATABASE TESTDB
> FROM DISK='C:\8A.M'
> WITH NORECOVERY
> GO
> -- Restore the first transaction log backup of LIVEDB TO TESTDB
> RESTORE LOG TESTDB
> FROM DISK ='C:\LOG1.TRN'
> WITH NORECOVERY
> GO
> -- Restore the final transaction log backup.(the last transaction log back
up)
> RESTORE LOG TESTDB
> FROM DISK ='C:\Log2.TRN'
> WITH RECOVERY
> GO
> Somehow, I got the error as below:
> Processed 1954 pages for database 'IMS', file 'Production60_Log' on file 1
.
> BACKUP LOG successfully processed 1954 pages in 0.432 seconds (37.037 MB/s
ec).
> Processed 88984 pages for database 'IERP60', file 'Production60_Data' on
> file 1.
> Processed 1 pages for database 'IERP60', file 'Production60_Log' on file 1
.
> RESTORE DATABASE successfully processed 88985 pages in 57.809 seconds
> (12.609 MB/sec).
> Server: Msg 4326, Level 16, State 1, Line 3
> The log in this backup set terminates at LSN 42947000000004100001, which i
s
> too early to apply to the database. A more recent log backup that includes
> LSN 42947000000146100001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> Processed 1954 pages for database 'IERP60', file 'Production60_Log' on fil
e 1.
> RESTORE LOG successfully processed 1954 pages in 2.127 seconds (7.522 MB/s
ec).
>
> I checked the transaction log that I applied is actually the first
> transaction log backup of the day itself.Please note that my transaction l
og
> backup date is 7 a.m and the full backup is 8a.m
> Please advise
> Thanks
>|||Sorry, how to determine from the LSNs which db backup is the latest?
"John Bell" wrote:
> Hi
> If you use RESTORE HEADERONLY you will see the LSNs and therefore use the
> correct file. You may find this discussion useful:
> http://tinyurl.com/4kqkf
> John
>
> "sql fren" wrote:
>|||Hi
RESTORE HEADERONLY returns the first and last LSN. In your case you
want the backup where
FirstLSN <= 42947000000146100001 <= LastLSN.
John
sql fren wrote:
> Sorry, how to determine from the LSNs which db backup is the latest?
>
> "John Bell" wrote:
>
use the
at the
log backup)
on file 1.
(37.037 MB/sec).
'Production60_Data' on
on file 1.
seconds
42947000000004100001, which is
includes
'Production60_Log' on file 1.
(7.522 MB/sec).
first
transaction log|||It seems that you are trying to apply log backup that made before the full
backup .
C:\LOG1.TRN contain log backup that made before the full backup .(NOT OK)
C:\LOG2.TRN contain log backup that made after the full backup . (OK)
You can see all backup operation in msdb..backupset table.
select *
from msdb..backupset
where datbaseName = 'db_name'
order by backup_set_id
TLV
"sql fren" <sqlfren@.discussions.microsoft.com> wrote in message
news:7A7D973A-2FEB-4824-B557-07172AD86A39@.microsoft.com...
> Sorry, how to determine from the LSNs which db backup is the latest?
>
> "John Bell" wrote:
>

Friday, March 30, 2012

Restore DB to new Database Name Cannot see new Database SQL2000

Restore DB to new Database Name Cannot see new Database SQL2000

Restore DB to new Database Name Cannot see new Database SQL2000

Monday, March 26, 2012

Restore database project problem

Restore database project problem

Restore database project problem

Wednesday, March 21, 2012

restore database based on log file

restore database based on log file

restore database based on log file

Friday, March 9, 2012

Restore at the point of failure

Restore mdf file

Blog Archive

About Me