Dear All,
We accidentally overwrite some data in our DB. Bad news is we never back-up our DB. However, the system are set in full-recovery mode. From website and book, i found that it's possible to recover back the data using "Recovery DB to a Point Time" but when we try it, we found that it cannot work without any backup data.
Anyone know how to resolve this issue?
Please kindly help.. Thanks alot!!
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies View RelatedI have a db which was never backed up!!!
Yesterday a script was run on this database which deleted my existing tables and created new ones...pretty dumb i know...just one of those days when everything goes wrong...all i have is the transaction log(which had a truncate log on checkpoint enabled..)..
is there some way in which i can recover those tables again???
i tried to run dbcc log (dbname,4) which showed me the transaction log with lots of rows but i could not read it ...can anywone help me to understand that transaction log???
any help appreciated
regards,
resh
How do I restore from a transaction Log? I use 2 separate database devices for my database (Data & Log) If data is corrupted at 3PM, can I just go and do a
LOAD TRANSACTION DatabaseName
from the LogDeviceName
StopAt 2:59
When I try to run that, it doesn't work. There is no option in the REstore Interface to specify to restore from only the transaction log. Do I need to DUMP the transaction log several times a day in order to do the restore?
Thanks!
JOyce
---------------------------------------
Subject:
From:
Date: Help!!!!!!! Lost DB! (reply)
Ray Miao ()
8/20/99 8:32:18 AM
Do you backup transaction log periodically? If not, sorry for that.
------------
Joyce at 8/19/99 5:32:12 PM
I need help ASAP!
For some reason, we lost a dozen of our tables with valuable data. could be a virus or something that cleared out our data.
The last backup we have is last night at 11PM. If we recover from that backup, all the users lose all of today's work!
Is there a way I can just roll back the transactions from today?????!
I'd really appreciate any help urgently
Joyce
Hi all,
I had been made full backup, differential backup, transaction log backup. I want to create new database from these file backup.
I did it follow:
1. Create new database from full backup file, it is ok. then
2. restore this database use transaction log backup file, an message raise :
"The proceding restore operation did not specify WITH NORECOVERY or WITH STANBY. Restart the restore sequence, specfying WITH NORECOVERY or WITH SATNBY for all but the final step... "
thank for reading.
dear all,
I want to restore transaction log to other server using transact-SQL.
can we restore serveral transaction log files at the same time ?
anybody have experience on this one.
thanks
Hi,
How can I restore a DB without TRN log. As database transaction logs grows too much and I only want to restore the database.
Thanks
Shafiq
Hi All,
I am facing one problem.
My Testing Server is down. I have taken full backup 1 hr backup. After that i have taken additional 5 TL backups. All the backups were moved to different server.
Now DB Server is corrupted. Now i have to rebuild the Database Server from the available Backup and TLog Backup.
But i have reliased that all the Transaction Log backup are having no naming convention in File name.
I am wondering after restoring full backup, which tlog backup i should use for next restore.
Thanks in advance.
-- Chetan
Simon writes "Hi
using the following syntax in SQL 2005 (win 2003) to restore a log to a marked transaction. Code runs ok but I'm never getting back to the point in time where id 13 still exists , help !
kind regards
Simon
/*backup and overwrite */
BACKUP DATABASE AdventureWorks
TO DISK = N'C:SQL Backupsadw.bak'
WITH FORMAT, INIT, NAME = N'AdventureWorks-Full Database Backup'
GO
/* verify data */
restore verifyonly
from disk = 'C:SQL Backupsadw.bak'
with file = 1
go
-- with MARK to place a marker in the tranny log
BEGIN TRANSACTION CandidateDelete13a
WITH MARK N'Deleting a Job Candidate';
GO
USE AdventureWorks;
GO
DELETE FROM AdventureWorks.HumanResources.JobCandidate
WHERE JobCandidateID = 13;
GO
COMMIT TRANSACTION CandidateDelete13a;
GO
BACKUP LOG AdventureWorks
TO DISK = N'C:SQL Backupsadw_log.bak'
with format, NAME = N'AdventureWorks-Trans Log Backup'
GO
/*
RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, RECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
*/
use master
RESTORE DATABASE AdventureWorks FROM DISK = N'C:SQL Backupsadw.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE,STATS = 10
GO
use master
RESTORE LOG AdventureWorks FROM DISK = N'C:SQL Backupsadw_log.bak'
WITH recovery, STOPatMARK = 'CandidateDelete13a'
GO"
Dear Gurus
I want to restore my transaction log backup.(Latest Tlog Backup)
I have tried to restore the backup in following way
database->tasks->restore-> transactionlog..
But in my server the transactionlog option is grayed(disabled).
Now how can i do restore. Am i wrong anywhere..?
Please give me your valuable suggessions gurus.
Thanks
Krishna
Actually instead of creating initial snapshot ,I thought of doing Transaction Replication in 2005 with backup/Restore. Everthing worked fine for DML changes which is being replicated to subscriber.
But my question is:
1)In 2005, DDL changes also replicate by default, Why i am not getting that feature while doing tran rep with backup/Restore?
Any replication Experts?
Hi,Currently, I am on SQL2000 SP3 and Windows 2003.I am trying to restore the full database backup and transaction logbackup on the development server. The full database restore is not aproblem, but the transaction log restore happened during the day is.Everytime when I try to compare the transaction in the database(select the last update date from one of my table as a benchmark, thenbackup the transaction log for restore) to the development db with therestored transaction log, I got none of the transaction from the logbackup.What did I do wrong? Is my concept about using the transaction logrestore wrong?Thanks in advance.
View 4 Replies View Relatedi have been doing tests and i cannot manage to do this. here is what i have done and am trying to do:
I created a full backup of the adventureworks database
I made some updates to tables.
I created a differential backup.
I done an update update to another table, dropped a table, and done another update.
Lets say in a production envrionment i just realised the table was dropped and i wanted to go to the transaction just before the table was dropped. is this possible, or are all transactions in the log going to be lost? All i have found remotely related to this are marked transactions, but they are not what i am looking for. i want a list of transactions in the log and be able to select one as the backup point ( via SSMS or TSQL). i am not looking for a point in time restore.
Thanks all
Hi
We have a database on sqlserver 2005 on a schedule for transaction log backups is in place already which is happening at interval of 30mins.All these transaction logs are appended to file called 'BKP' on E drive of our server. Now i have to configure logshipping for this database.Normally logshipping process will backup the transaction log,stores it in a shared folder on primary server and then copies the backupfile to another folder on secondary server and then restored ..plz correct me till now if im wrong.Now let us say our normal trans log backup schedule is as follows
T1 at 9.30am
T2 at 10.00am
T3 at 10.30am
T4 at 11.00 am
Now i decided to schedule the log shipping transaction log backup interval once in 30mins and start transaction log backup at following intervals
T11 AT 9.45am
T22 AT 10.15AM
T33 at 10.45AM
T44 AT 11.15AM
So T11,T22 etc these backups are restored on standby server sequentially.
Now if Primary database goes down at 11.25am , then do i have to restore the Tlogs T1,T2,T3,T4 and make the Primary database up or i should restore T1,T11,T2,T22,T3,T33,T4,T44 for making my primary database UP..
Regards
Arvind L
I have this database "DB1" which is in FULL recovery mode.
I run full daily backup -Monday to Saturday
DB1_Mon.bak
DB1_Tue.bak
DB1_Wed.bak
DB1_Thur.bak
DB1_Fri.bak
DB1_Sat.bak
Come Sunday - for the first time I have run transaction log backup.
DB1_Sun.trn
And now, I need to restore DB1 - but the only full backup that I have is the Monday - "DB1_Mon.bak"
Is my database complete if I restore only "DB1_Mon.bak" plus the "DB1_Sun.trn"?
Jeboy
hi, I would like to know the correct reaction for a crash in both senarios.
First senario,
I made a full back up at 6 am , then scheduled sql server to make transaction log back up every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 12:30. How would I resotre the data in the first senario....Can I restore the full back up done at 6 am then restore the last transaction log backup ( which is 12 Noon ) . I am not sure If I need to resotre the whole tran from 6 am till the time it was crashed.
Second senario,
I made a full back up at 6 am, then scheduled sql server to make Incremental backup every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 3:00 pm. How would I resotre the data in the second senario. ....Do I restore the full backup at 6 am then restore each incremental backup backwords ( 2,12,10,8)
AS you can see, I am not sure how to deal with this issue, I do appreciate your feedback.
Best regards
Ahmed
Hi,
We have an order posting stored procedure that's been executing for 14 hours now. This procedure has simple update statements but all the tables updated have triggers which update other tables with triggers and so on.
This procedure hasn't commited yet. I have a full database backup from last night and transaction log backup from this morning while it was running. I want to kill this job and restore database from last backup. Only thing I am afraid of is that it will take too long to roll back which I have no estimate of time. Is there any faster way to get rid of all the uncommited transactions? Has anybody rolled back such a massive transaction? Any idea on how long it can take? Can it affect the overall server performance as this is the main production ERP server.
I can see in profiler that this procedure is still running and not hung.
I am currently running MSSQL on DB1 server with 12 DBs
I am running a full backup nightly at 8:00pm and coping it over to DB2.
I am sending transaction logs every 15 minutes to DB2.
Is there a script or procedure for rolling in all the logs once the restore is finished. I realize the basic way to do this is here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpc_7veh.asp
But writing a script with say 22 files each for each db is fairly time consuming.
I have contemplated writing a script that takes the content of the logs directory, parses it based on db name and builds the sql statements but did not want to go through all of it if someone else has already done the leg work.
Any help on this would be appreciated.
Chris
If I have a database backup from sunday, and a failure occurs monday... Can the backup .mdf and .ldf files be attached, and the backup log after the point of failure be applied to them?
The problem I am having is it looks like you can only restore from a .bak file, and then apply the log at the point of failure. IT doesn't look like you can restore the .ldf/.mdf files, and then apply the backup log from the point of failure.
Can someone please help? I'm in desparate need of fixing this !
Thanks,dp
Hi guys, just wanna ask about the backup and restore database method. What's the best way for database and restore which able to view all the transaction logs after the database being restored. Currently I backup my database daily for recovery purposes. However, if I restore the backup file at another server and use SQL log application for viewing the prefer database's transaction log, it shows all the previous log had been truncated.
Therefore, I want to know is there any way that able to get the transaction logs after restore from a database backup file? Hope able to get any assistance here as soon as possible. Thank you.
Best Regards,
Hans
from BOL:
"
Restoring a database clears the plan cache for the instance of SQL Server.
"
i'm seeing this behavior after log restores as well.
what's the reason the whole procedure cache is flushed?
thanks.
Got this situation, trying to do Use SignleMode to recover my handing db, after that lost ldf (and physically too). Tried all things thru SSMS and scripts (below) that I know with no result, is there anything else I can try to recover it, I don't need log file.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not open new database 'MyLostDB'. CREATE DATABASE is aborted.
File activation failure. The physical file name "C:xxxMyLostDB.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure. (Microsoft SQL Server, Error: 1813)
EXEC sp_attach_single_file_db @dbname='Commissions',
@physname=N'C:SQLDataMyLostDB.mdf'
GO
CREATE DATABASE Commissions ON
(FILENAME = N'C:SQLDataMyLostDB.mdf')
FOR ATTACH_REBUILD_LOG
GO
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
i have a sequence container in my my sequence container i have a script task for drop the existing tables. This seq. container connected to another seq. container. all these are in for each loop container when i run the package it's work fine for 1st looop but it gives me error for second execution.
Message is like this:
Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
Hi,
i am getting this error "Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.".
my transations have been done using LINKED SERVER. when i manually call the store procedure from Server 1 it works but when i call it through Service broker it dosen't work and gives me this error.
Thanks in advance.
I neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
While migrating Report services in SQL Server 2005 to 2014, I am trying to restore the Encryption Key in RS Configuration Manager in2014. But I cannot click the 'Restore' button in RS Configuration Manager. So if I should be grant more right to do so or any other action?
View 2 Replies View RelatedIn Windows Server 2012. How do I do a System Restore to a previous restore point?I need to install the 64 bit and 32 bit Oracle Client Install for connections in SSIS and to create Oracle Linked Servers.
If you make a mistake it is not fun removing it. Sometimes it corrupts the machine and it is difficult to uninstall since there is not an Oracle Universal installer for Oracle 11g.If you install the 32 bit before the 64 you mess up the machine.how to create a restore point.
I am looking for a SQL Backup/Restore tools which can restore multiple environments. Here is high level requirements.
1. We have 4 DBs, range from 1 TB - 1.5 TB Each Database. When we restore to QA, DEV, or Staging, we usually restore 4 of them.
2. I am looking for the speed to complete restoring between 1 - 2 hours for 4 DBs.
I am evaluating the Dephix Software but the setup is very complex and its given us a lot of issues with Windows Authentions, and failure in the middle of the backup. I used Guess Software many years ago but can't find it on the web site any more. Speed is very important for us mean complete restoring as fast as possible. We are on SQL 2012 and SQL 2008 R2.We are currently using NETAPP Technology and I have Redgate Backup Tool but I am mainly looking for fast Restore Process.
I want to list out the pending transaction for transaction replication by publication.
Help needed.