Transaction Log Restore Procedure
Oct 18, 2007
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
View 14 Replies
ADVERTISEMENT
May 30, 2008
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.
View 2 Replies
View Related
Jun 10, 2015
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 Related
Mar 31, 2008
I have a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
Any help on this would be appreciated.
View 1 Replies
View Related
Feb 7, 2001
I 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
View 2 Replies
View Related
Aug 23, 1999
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
View 1 Replies
View Related
Dec 12, 2006
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!!
View 2 Replies
View Related
Feb 19, 2004
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.
View 6 Replies
View Related
Aug 23, 2006
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
View 5 Replies
View Related
Jun 5, 2006
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
View 4 Replies
View Related
Apr 18, 2008
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
View 2 Replies
View Related
Jan 29, 2007
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"
View 1 Replies
View Related
Oct 22, 2007
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
View 10 Replies
View Related
Feb 29, 2008
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?
View 16 Replies
View Related
Jul 20, 2005
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 Related
Apr 13, 2007
i 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
View 3 Replies
View Related
Mar 10, 2008
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
View 1 Replies
View Related
Feb 1, 2001
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
View 1 Replies
View Related
Dec 8, 2000
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.
View 1 Replies
View Related
Jan 7, 2005
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
View 2 Replies
View Related
May 18, 2005
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
View 1 Replies
View Related
Feb 21, 2008
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
View 1 Replies
View Related
Nov 14, 2014
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
View 6 Replies
View Related
Jun 22, 2007
I want to create the procedure that when we restore the database backup few fields should not be updated from backup. ?
table1
field1, field2, field3,.......
when restore the backup, from table1 of field2 should not be
update ?
how can create the procdures ?
Martin
View 1 Replies
View Related
May 31, 2008
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
View 1 Replies
View Related
Feb 11, 2008
hi.. im having a hard time restoring my .bak file.... i have a stored procedure like this.. and every time i call this in my program an error displays... (database in use. the system administrator must have exclusive use ot the database to run the restore procedure)
CREATE PROCEDURE sp_db_restore
AS
RESTORE DATABASE Inventory
FROM DISK = 'C:MSSQL7BACKUPInventory.bak'
WITH MOVE 'Inventory_data' TO 'C:Inventory_data.mdf',
MOVE 'Inventory_log' TO 'C:Inventory_log.ldf',
REPLACE
'can u pls help me wd this.. tnx
View 4 Replies
View Related
Oct 5, 2006
Hi there
I just want to create Stored Procedure which will do following thing
i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)
ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)
I mean to say, the file should be act as the full backup of database, in plain text.
The same need to be done in the reverse order,this SP will do following things
i)Open the file created by first SP, create new database, with same name as backup in SP one
ii) create the tables, by reading the Schema script.
iii) Insert all the data in corrsponding tables.
For EX:
--creating backup
sp_CreateDatbaseBackup('DatabaseName')
--Restoring
sp_RestoreDatabaseBackup('FileName')
I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)
I want an editable full Backup and Restore.
Please help me in creating these SPs or any other idea, solution ?
OR
any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.
Regards,
Thanks.
Gurpreet S. Gill
View 5 Replies
View Related
May 5, 2015
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.
View 6 Replies
View Related
Feb 22, 2007
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.
View 3 Replies
View Related
Jun 22, 2006
I have a stored procedure that registers a user. It also checks whether an email or username exists before registering them.Does this need to be in a transaction to prevent duplication of email/username? The data is highly sensitive and should not be duplicated at all.I tried writing methods in .net like CheckEmail() and CheckUsername() but that probably wont protect against duplicates if 2 people submit at the same time on a busy server. So the next best thing is a stored procedure but should it be in a transaction or not is what I'm wondering.Thanks again,you guys are great!
View 2 Replies
View Related
Nov 10, 2006
I have a function in which I use a stored procedure and a transaction.I use the following code, but I get the exception e (last try-catch): This SqlTransaction has completed; it is no longer usable.(btw, the SendEmail function currently returns False, so the transaction should ALWAYS roll back) Public Shared Function SaveGBEntry(ByVal myGBEntry As GBEntry, ByVal Language As String) As Boolean Dim bSuccess As Boolean = False Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("spNewGBEntry", MyConnection) Dim fBeginTransCalled As Boolean = False Dim myTrans As SqlTransaction Try MyConnection.Open() myTrans = MyConnection.BeginTransaction() cmd.CommandType = Data.CommandType.StoredProcedure cmd.Transaction = myTrans fBeginTransCalled = True Dim UserCodeOwner As Integer = UserFunctions.GetUserCode(myGBEntry.UserNameOwner) Dim OwnerEmail As String = UserFunctions.GetUserEmail(myGBEntry.UserNameOwner) cmd.Parameters.Add(New SqlParameter("@UserCodeSender", myGBEntry.UserCodeSender)) cmd.Parameters.Add(New SqlParameter("@GBText", myGBEntry.Text)) cmd.Parameters.Add(New SqlParameter("@GBUpdateDate", myGBEntry.UpdateDate)) cmd.Parameters.Add(New SqlParameter("@UserCode", UserCodeOwner)) cmd.ExecuteNonQuery() myTrans.Commit()
If MailFunctions.SendEmail(myGBEntry.UserNameOwner, OwnerEmail, ConfigurationManager.AppSettings("emailINFOname"), ConfigurationManager.AppSettings("emailINFOAddress"), True, "NewGBEntry", Language) Then bSuccess = True fBeginTransCalled = False End If Catch ex As Exception GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", ex.Message) Finally If fBeginTransCalled Then Try myTrans.Rollback() Catch e As System.Exception GlobalFunctions.ReportError("GUESTBOOKDAL:SaveGBEntry", e.Message) End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 1 Replies
View Related
Oct 17, 2007
hello,
i'm executing a select statement inside the BEGIN TRANSACTION block on a Stored Procedure. my question is, does it lock the table? actually, i'm getting the maximum value of a field and i don't want other users to read until the transaction is done. do i still need to use tablockx to manually lock the table or does the transaction handles the locking?
please help
thanks!
View 2 Replies
View Related
Apr 11, 2008
hi,
Can any one tell me which is better suitable for web applicaiton Stored Procedure or transaction for regural process in the table..
can you explain me why..?
View 2 Replies
View Related
