Problem:
A database has been deleted. I have a development version, which dates from
some time in the past. (8/23/03). I have transactions logs dataing from
2/15/04. This is not a heavily used system and some data will be better
than anything. However, when I try to apply the first transaction log, the
system tells me, not surprisingly, that this is not a valid log for this
database.
Assistance Requested:
Is there some way to "fool" SQL server into thinking that the transaction
log should apply? Are their date/time stamps recorded in a system table that
I could overwrite? Even if some data is corrupted, something is better than
nothing.
Moral of the story:
Do not believe you operations people when they tell you that tape backups
are working. VERIFY. Force them to do regular tests. (the backup files on
the tape are either missing, or are 16 KB a piece)
John Willard
(company name withheld to protect the innocent -- me!)
Hi John,
As my understanding of your problem, you have delete one of you database on
the production SQL server, then you only have the database on the develope
server. You also have a 'transactions log dating from 2/15/04', you want to
recover the database, right? If I misunderstood you, please feel free to
let me know.
For your 'transactional log dating from 2/15/04', I assume it is a
transaction log backup. However, based on my knowledge, it is not possible
to apply a transaction log backup:
1) Unless the database or differential database backup preceding the
transaction log backup is restored first.
2) Unless all preceding transaction logs created since the database or
differential database were backed up are applied first.
3) If the database has already recovered and all outstanding transactions
have either been rolled back or rolled forward.
You cannot restore the transaction log backup on an old database.
Thanks. For any question, please feel free to post message here and we are
glad to help. Thanks
Best regards
Baisong Wei
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
|||You have understood the problem correctly. I was hoping to be able to change
the timestamp or in some other way trick SQL server into accepting those
logs. However, your message confirms for me that the database is
unrecoverable. Oh, well -- NMF (not my fault)
Thanks
John Willard
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:m8zCMncLEHA.3900@.cpmsftngxa10.phx.gbl...
> Hi John,
> As my understanding of your problem, you have delete one of you database
on
> the production SQL server, then you only have the database on the develope
> server. You also have a 'transactions log dating from 2/15/04', you want
to
> recover the database, right? If I misunderstood you, please feel free to
> let me know.
> For your 'transactional log dating from 2/15/04', I assume it is a
> transaction log backup. However, based on my knowledge, it is not possible
> to apply a transaction log backup:
> 1) Unless the database or differential database backup preceding the
> transaction log backup is restored first.
> 2) Unless all preceding transaction logs created since the database or
> differential database were backed up are applied first.
> 3) If the database has already recovered and all outstanding transactions
> have either been rolled back or rolled forward.
> You cannot restore the transaction log backup on an old database.
> Thanks. For any question, please feel free to post message here and we are
> glad to help. Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
sql
Showing posts with label deleted. Show all posts
Showing posts with label deleted. Show all posts
Wednesday, March 28, 2012
restore with dev database and transaction log.
Problem:
A database has been deleted. I have a development version, which dates from
some time in the past. (8/23/03). I have transactions logs dataing from
2/15/04. This is not a heavily used system and some data will be better
than anything. However, when I try to apply the first transaction log, the
system tells me, not surprisingly, that this is not a valid log for this
database.
Assistance Requested:
Is there some way to "fool" SQL server into thinking that the transaction
log should apply? Are their date/time stamps recorded in a system table that
I could overwrite? Even if some data is corrupted, something is better than
nothing.
Moral of the story:
Do not believe you operations people when they tell you that tape backups
are working. VERIFY. Force them to do regular tests. (the backup files on
the tape are either missing, or are 16 KB a piece)
John Willard
(company name withheld to protect the innocent -- me!)Hi John,
As my understanding of your problem, you have delete one of you database on
the production SQL server, then you only have the database on the develope
server. You also have a 'transactions log dating from 2/15/04', you want to
recover the database, right? If I misunderstood you, please feel free to
let me know.
For your 'transactional log dating from 2/15/04', I assume it is a
transaction log backup. However, based on my knowledge, it is not possible
to apply a transaction log backup:
1) Unless the database or differential database backup preceding the
transaction log backup is restored first.
2) Unless all preceding transaction logs created since the database or
differential database were backed up are applied first.
3) If the database has already recovered and all outstanding transactions
have either been rolled back or rolled forward.
You cannot restore the transaction log backup on an old database.
Thanks. For any question, please feel free to post message here and we are
glad to help. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||You have understood the problem correctly. I was hoping to be able to change
the timestamp or in some other way trick SQL server into accepting those
logs. However, your message confirms for me that the database is
unrecoverable. Oh, well -- NMF (not my fault)
Thanks
John Willard
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:m8zCMncLEHA.3900@.cpmsftngxa10.phx.gbl...
> Hi John,
> As my understanding of your problem, you have delete one of you database
on
> the production SQL server, then you only have the database on the develope
> server. You also have a 'transactions log dating from 2/15/04', you want
to
> recover the database, right? If I misunderstood you, please feel free to
> let me know.
> For your 'transactional log dating from 2/15/04', I assume it is a
> transaction log backup. However, based on my knowledge, it is not possible
> to apply a transaction log backup:
> 1) Unless the database or differential database backup preceding the
> transaction log backup is restored first.
> 2) Unless all preceding transaction logs created since the database or
> differential database were backed up are applied first.
> 3) If the database has already recovered and all outstanding transactions
> have either been rolled back or rolled forward.
> You cannot restore the transaction log backup on an old database.
> Thanks. For any question, please feel free to post message here and we are
> glad to help. Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
A database has been deleted. I have a development version, which dates from
some time in the past. (8/23/03). I have transactions logs dataing from
2/15/04. This is not a heavily used system and some data will be better
than anything. However, when I try to apply the first transaction log, the
system tells me, not surprisingly, that this is not a valid log for this
database.
Assistance Requested:
Is there some way to "fool" SQL server into thinking that the transaction
log should apply? Are their date/time stamps recorded in a system table that
I could overwrite? Even if some data is corrupted, something is better than
nothing.
Moral of the story:
Do not believe you operations people when they tell you that tape backups
are working. VERIFY. Force them to do regular tests. (the backup files on
the tape are either missing, or are 16 KB a piece)
John Willard
(company name withheld to protect the innocent -- me!)Hi John,
As my understanding of your problem, you have delete one of you database on
the production SQL server, then you only have the database on the develope
server. You also have a 'transactions log dating from 2/15/04', you want to
recover the database, right? If I misunderstood you, please feel free to
let me know.
For your 'transactional log dating from 2/15/04', I assume it is a
transaction log backup. However, based on my knowledge, it is not possible
to apply a transaction log backup:
1) Unless the database or differential database backup preceding the
transaction log backup is restored first.
2) Unless all preceding transaction logs created since the database or
differential database were backed up are applied first.
3) If the database has already recovered and all outstanding transactions
have either been rolled back or rolled forward.
You cannot restore the transaction log backup on an old database.
Thanks. For any question, please feel free to post message here and we are
glad to help. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||You have understood the problem correctly. I was hoping to be able to change
the timestamp or in some other way trick SQL server into accepting those
logs. However, your message confirms for me that the database is
unrecoverable. Oh, well -- NMF (not my fault)
Thanks
John Willard
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:m8zCMncLEHA.3900@.cpmsftngxa10.phx.gbl...
> Hi John,
> As my understanding of your problem, you have delete one of you database
on
> the production SQL server, then you only have the database on the develope
> server. You also have a 'transactions log dating from 2/15/04', you want
to
> recover the database, right? If I misunderstood you, please feel free to
> let me know.
> For your 'transactional log dating from 2/15/04', I assume it is a
> transaction log backup. However, based on my knowledge, it is not possible
> to apply a transaction log backup:
> 1) Unless the database or differential database backup preceding the
> transaction log backup is restored first.
> 2) Unless all preceding transaction logs created since the database or
> differential database were backed up are applied first.
> 3) If the database has already recovered and all outstanding transactions
> have either been rolled back or rolled forward.
> You cannot restore the transaction log backup on an old database.
> Thanks. For any question, please feel free to post message here and we are
> glad to help. Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
restore with dev database and transaction log.
Problem:
A database has been deleted. I have a development version, which dates from
some time in the past. (8/23/03). I have transactions logs dataing from
2/15/04. This is not a heavily used system and some data will be better
than anything. However, when I try to apply the first transaction log, the
system tells me, not surprisingly, that this is not a valid log for this
database.
Assistance Requested:
Is there some way to "fool" SQL server into thinking that the transaction
log should apply? Are their date/time stamps recorded in a system table that
I could overwrite? Even if some data is corrupted, something is better than
nothing.
Moral of the story:
Do not believe you operations people when they tell you that tape backups
are working. VERIFY. Force them to do regular tests. (the backup files on
the tape are either missing, or are 16 KB a piece)
John Willard
(company name withheld to protect the innocent -- me!)Hi John,
As my understanding of your problem, you have delete one of you database on
the production SQL server, then you only have the database on the develope
server. You also have a 'transactions log dating from 2/15/04', you want to
recover the database, right? If I misunderstood you, please feel free to
let me know.
For your 'transactional log dating from 2/15/04', I assume it is a
transaction log backup. However, based on my knowledge, it is not possible
to apply a transaction log backup:
1) Unless the database or differential database backup preceding the
transaction log backup is restored first.
2) Unless all preceding transaction logs created since the database or
differential database were backed up are applied first.
3) If the database has already recovered and all outstanding transactions
have either been rolled back or rolled forward.
You cannot restore the transaction log backup on an old database.
Thanks. For any question, please feel free to post message here and we are
glad to help. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||You have understood the problem correctly. I was hoping to be able to change
the timestamp or in some other way trick SQL server into accepting those
logs. However, your message confirms for me that the database is
unrecoverable. Oh, well -- NMF (not my fault)
Thanks
John Willard
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:m8zCMncLEHA.3900@.cpmsftngxa10.phx.gbl...
> Hi John,
> As my understanding of your problem, you have delete one of you database
on
> the production SQL server, then you only have the database on the develope
> server. You also have a 'transactions log dating from 2/15/04', you want
to
> recover the database, right? If I misunderstood you, please feel free to
> let me know.
> For your 'transactional log dating from 2/15/04', I assume it is a
> transaction log backup. However, based on my knowledge, it is not possible
> to apply a transaction log backup:
> 1) Unless the database or differential database backup preceding the
> transaction log backup is restored first.
> 2) Unless all preceding transaction logs created since the database or
> differential database were backed up are applied first.
> 3) If the database has already recovered and all outstanding transactions
> have either been rolled back or rolled forward.
> You cannot restore the transaction log backup on an old database.
> Thanks. For any question, please feel free to post message here and we are
> glad to help. Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
A database has been deleted. I have a development version, which dates from
some time in the past. (8/23/03). I have transactions logs dataing from
2/15/04. This is not a heavily used system and some data will be better
than anything. However, when I try to apply the first transaction log, the
system tells me, not surprisingly, that this is not a valid log for this
database.
Assistance Requested:
Is there some way to "fool" SQL server into thinking that the transaction
log should apply? Are their date/time stamps recorded in a system table that
I could overwrite? Even if some data is corrupted, something is better than
nothing.
Moral of the story:
Do not believe you operations people when they tell you that tape backups
are working. VERIFY. Force them to do regular tests. (the backup files on
the tape are either missing, or are 16 KB a piece)
John Willard
(company name withheld to protect the innocent -- me!)Hi John,
As my understanding of your problem, you have delete one of you database on
the production SQL server, then you only have the database on the develope
server. You also have a 'transactions log dating from 2/15/04', you want to
recover the database, right? If I misunderstood you, please feel free to
let me know.
For your 'transactional log dating from 2/15/04', I assume it is a
transaction log backup. However, based on my knowledge, it is not possible
to apply a transaction log backup:
1) Unless the database or differential database backup preceding the
transaction log backup is restored first.
2) Unless all preceding transaction logs created since the database or
differential database were backed up are applied first.
3) If the database has already recovered and all outstanding transactions
have either been rolled back or rolled forward.
You cannot restore the transaction log backup on an old database.
Thanks. For any question, please feel free to post message here and we are
glad to help. Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||You have understood the problem correctly. I was hoping to be able to change
the timestamp or in some other way trick SQL server into accepting those
logs. However, your message confirms for me that the database is
unrecoverable. Oh, well -- NMF (not my fault)
Thanks
John Willard
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:m8zCMncLEHA.3900@.cpmsftngxa10.phx.gbl...
> Hi John,
> As my understanding of your problem, you have delete one of you database
on
> the production SQL server, then you only have the database on the develope
> server. You also have a 'transactions log dating from 2/15/04', you want
to
> recover the database, right? If I misunderstood you, please feel free to
> let me know.
> For your 'transactional log dating from 2/15/04', I assume it is a
> transaction log backup. However, based on my knowledge, it is not possible
> to apply a transaction log backup:
> 1) Unless the database or differential database backup preceding the
> transaction log backup is restored first.
> 2) Unless all preceding transaction logs created since the database or
> differential database were backed up are applied first.
> 3) If the database has already recovered and all outstanding transactions
> have either been rolled back or rolled forward.
> You cannot restore the transaction log backup on an old database.
> Thanks. For any question, please feel free to post message here and we are
> glad to help. Thanks
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
Friday, March 23, 2012
restore the dts
Hi,
By mistake i have deleted the dts package. how can i restore that.
any advice pls.......
regards
bharatDTS packages are contained in the MSDB database. You must restore a backup copy of MSDB.
Wednesday, March 21, 2012
Restore SQL 2000 Tables from Transaction Log Files
I inadvertently deleted related records from one table when I deleted anothe
r table. I am not sure how often the server admin backups the data or how h
e is doing it. I thought that I read somewhere that one could do that from
the log files. Can someone
please help me with this?
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,
r table. I am not sure how often the server admin backups the data or how h
e is doing it. I thought that I read somewhere that one could do that from
the log files. Can someone
please help me with this?
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,
Restore SQL 2000 Tables from Transaction Log Files
I inadvertently deleted related records from one table when I deleted another table. I am not sure how often the server admin backups the data or how he is doing it. I thought that I read somewhere that one could do that from the log files. Can someone please help me with this
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,
Restore sp_grantdbaccess
I need to know how to restore a stored procedure that was deleted. I created
a new one, but it is set to user type and doesn't work. I don't know how to
pull it out of a master db backup. Any help would be GREAT.
Thanks,
FrankI assume this is 2000. I'd check the installation scripts in the install folder of your SQL Server
installation. You will fine one or a few which installs the system procedures and based on that code
within that script you will see how to add it as a system procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
>I need to know how to restore a stored procedure that was deleted. I created
> a new one, but it is set to user type and doesn't work. I don't know how to
> pull it out of a master db backup. Any help would be GREAT.
> Thanks,
> Frank|||Yes, 2000. I have the SQL out of the the SP, but when I recreate it it has a
user type and not system. Tells me i don't have access to active directory.
"Tibor Karaszi" wrote:
> I assume this is 2000. I'd check the installation scripts in the install folder of your SQL Server
> installation. You will fine one or a few which installs the system procedures and based on that code
> within that script you will see how to add it as a system procedures.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
> >I need to know how to restore a stored procedure that was deleted. I created
> > a new one, but it is set to user type and doesn't work. I don't know how to
> > pull it out of a master db backup. Any help would be GREAT.
> > Thanks,
> > Frank
>|||You need to dig further into that script file. In the beginning of the script, it should execute a
system stored procedure which result in every following procedures created are classified as system
stored procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:26C50283-2EC2-4A5D-8436-1027E5B79D08@.microsoft.com...
> Yes, 2000. I have the SQL out of the the SP, but when I recreate it it has a
> user type and not system. Tells me i don't have access to active directory.
> "Tibor Karaszi" wrote:
>> I assume this is 2000. I'd check the installation scripts in the install folder of your SQL
>> Server
>> installation. You will fine one or a few which installs the system procedures and based on that
>> code
>> within that script you will see how to add it as a system procedures.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
>> >I need to know how to restore a stored procedure that was deleted. I created
>> > a new one, but it is set to user type and doesn't work. I don't know how to
>> > pull it out of a master db backup. Any help would be GREAT.
>> > Thanks,
>> > Frank
>>
a new one, but it is set to user type and doesn't work. I don't know how to
pull it out of a master db backup. Any help would be GREAT.
Thanks,
FrankI assume this is 2000. I'd check the installation scripts in the install folder of your SQL Server
installation. You will fine one or a few which installs the system procedures and based on that code
within that script you will see how to add it as a system procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
>I need to know how to restore a stored procedure that was deleted. I created
> a new one, but it is set to user type and doesn't work. I don't know how to
> pull it out of a master db backup. Any help would be GREAT.
> Thanks,
> Frank|||Yes, 2000. I have the SQL out of the the SP, but when I recreate it it has a
user type and not system. Tells me i don't have access to active directory.
"Tibor Karaszi" wrote:
> I assume this is 2000. I'd check the installation scripts in the install folder of your SQL Server
> installation. You will fine one or a few which installs the system procedures and based on that code
> within that script you will see how to add it as a system procedures.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
> >I need to know how to restore a stored procedure that was deleted. I created
> > a new one, but it is set to user type and doesn't work. I don't know how to
> > pull it out of a master db backup. Any help would be GREAT.
> > Thanks,
> > Frank
>|||You need to dig further into that script file. In the beginning of the script, it should execute a
system stored procedure which result in every following procedures created are classified as system
stored procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:26C50283-2EC2-4A5D-8436-1027E5B79D08@.microsoft.com...
> Yes, 2000. I have the SQL out of the the SP, but when I recreate it it has a
> user type and not system. Tells me i don't have access to active directory.
> "Tibor Karaszi" wrote:
>> I assume this is 2000. I'd check the installation scripts in the install folder of your SQL
>> Server
>> installation. You will fine one or a few which installs the system procedures and based on that
>> code
>> within that script you will see how to add it as a system procedures.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
>> >I need to know how to restore a stored procedure that was deleted. I created
>> > a new one, but it is set to user type and doesn't work. I don't know how to
>> > pull it out of a master db backup. Any help would be GREAT.
>> > Thanks,
>> > Frank
>>
Tuesday, March 20, 2012
Restore Rows from Table
Is it possible to restore certain Rows from a backup to a specific Table?
Also, is there a log to determine which Rows where deleted when?
TIA,
--
Brandon Moser
Marketing & Systems
Spence, Driscoll & Company, Inc.
bmoser@.(nospam)spencedriscoll.com> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
Also, is there a log to determine which Rows where deleted when?
TIA,
--
Brandon Moser
Marketing & Systems
Spence, Driscoll & Company, Inc.
bmoser@.(nospam)spencedriscoll.com> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
Restore Rows from Table
Is it possible to restore certain Rows from a backup to a specific Table?
Also, is there a log to determine which Rows where deleted when?
TIA,
Brandon Moser
Marketing & Systems
Spence, Driscoll & Company, Inc.
bmoser@.(nospam)spencedriscoll.com
> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
|||> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
Also, is there a log to determine which Rows where deleted when?
TIA,
Brandon Moser
Marketing & Systems
Spence, Driscoll & Company, Inc.
bmoser@.(nospam)spencedriscoll.com
> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
|||> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
http://www.aspfaq.com/
(Reverse address to reply.)
Restore Rows from Table
Is it possible to restore certain Rows from a backup to a specific Table?
Also, is there a log to determine which Rows where deleted when?
TIA,
--
Brandon Moser
Marketing & Systems
Spence, Driscoll & Company, Inc.
bmoser@.(nospam)spencedriscoll.com>Is it possible to restore certain Rows from a backup to a
specific Table?
Not unless you have done filegroup backups. You could
restore the whole db to another name, then extract the
data you need.
>Also, is there a log to determine which Rows where
deleted when?
>
The only thing Ive heard of is Log Explorer from Lumigent.
>--Original Message--
>Is it possible to restore certain Rows from a backup to a
specific Table?
>Also, is there a log to determine which Rows where
deleted when?
>TIA,
>--
>Brandon Moser
>Marketing & Systems
>Spence, Driscoll & Company, Inc.
>bmoser@.(nospam)spencedriscoll.com
>
>.
>|||> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
--
http://www.aspfaq.com/
(Reverse address to reply.)
Also, is there a log to determine which Rows where deleted when?
TIA,
--
Brandon Moser
Marketing & Systems
Spence, Driscoll & Company, Inc.
bmoser@.(nospam)spencedriscoll.com>Is it possible to restore certain Rows from a backup to a
specific Table?
Not unless you have done filegroup backups. You could
restore the whole db to another name, then extract the
data you need.
>Also, is there a log to determine which Rows where
deleted when?
>
The only thing Ive heard of is Log Explorer from Lumigent.
>--Original Message--
>Is it possible to restore certain Rows from a backup to a
specific Table?
>Also, is there a log to determine which Rows where
deleted when?
>TIA,
>--
>Brandon Moser
>Marketing & Systems
>Spence, Driscoll & Company, Inc.
>bmoser@.(nospam)spencedriscoll.com
>
>.
>|||> Is it possible to restore certain Rows from a backup to a specific Table?
No. You can't even restore an entire table on its own, unless you have
performed individual filegroup backups and the table lives on the filegroup
by itself. You could restore the backup to another instance, then copy out
the rows you want.
> Also, is there a log to determine which Rows where deleted when?
http://www.aspfaq.com/2449
--
http://www.aspfaq.com/
(Reverse address to reply.)
Restore ReportServer Database
Accidentally the key tables were deleted from our ReportServer SQL Database.
We tried copying the tables over from the ReportServer TempDB, but this then broke the encryption key (which we did not have access to the backup of).
We then ran rsconfig -c to restructure the db connection and generate a new key. But unfortunately our old data was encrypted by the old key.
We are also getting the following error on the http://localhost/Reports page:
The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.6.51'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase)
The recomended fix for this error is to reinstall Reporting Services from scratch.
We were hoping that there maybe another way around this, preferably away we can just restore from he TempDB.
Thanks for your attention.
The tempdb is not a copy of the main report server database. It contains a different set of data. The main database stores report defintitions, data sources, security settings, schedules, etc. The tempdb stores dynamic data , such as report snapshot that are current being viewed. You can't restore one from the other.
If you don't have a backup of the encryption key, you will need to delete all encrypted content in the database via the config tool. If you have lost your entire report server database (which sounds like it is the case) and don't have a backup, you will need to rebuild it from scratch.
Wednesday, March 7, 2012
Restore nText from Transaction Log
A user of mine deleted a chunk of data from one of our databases that
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?
Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?
Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com
Restore nText from Transaction Log
A user of mine deleted a chunk of data from one of our databases that
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com
Restore nText from Transaction Log
A user of mine deleted a chunk of data from one of our databases that
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com
was, unfortunately, not properly backed up. I have captured the
transaction log and using ApexSQL Log I am able to view all of the
delete statements. ApexSQL Log generates undo scripts for these
deletes, but there is an issue...
The deleted records contained nText fields. In the transaction log,
these are logged as hex values and thus can't be reassigned to the
nText fields. Any operation I can perform to turn the data in the
transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
original nText?Derek wrote:
> A user of mine deleted a chunk of data from one of our databases that
> was, unfortunately, not properly backed up. I have captured the
> transaction log and using ApexSQL Log I am able to view all of the
> delete statements. ApexSQL Log generates undo scripts for these
> deletes, but there is an issue...
> The deleted records contained nText fields. In the transaction log,
> these are logged as hex values and thus can't be reassigned to the
> nText fields. Any operation I can perform to turn the data in the
> transaction log (i.e. 0x0000ad2400000000fa12000001000100) into the
> original nText?
I would check with Apex Software to see what they say. If the text
involved is less than 4,000 bytes, you can probably cast the value back
to a nvarchar. For example:
SELECT CAST(N'ABC123' as VARBINARY(100)) -- 0x410042004300310032003300
SELECT CAST(0x410042004300310032003300 as NVARCHAR(4000)) -- ABC123
David Gugick
Quest Software
www.imceda.com
www.quest.com
Subscribe to:
Posts (Atom)