Restoring Transactional Backups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restoring Transactional Backups

Author

Topic

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-12-17 : 17:20:02

Guys,

In 2000, if I need to restore Transactional backups I have to do it one by one followed by Full Backup or Differntial backups.

I am wondering is there any way I can restore all Transactional files in One Batch in SQL Server 2005?

Many Thanks

SKR

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-17 : 17:28:06

Your first sentence is incorrect. You must first restore the full backup. You can then choose to restore the differentials plus the tlogs after the diffs or all of the tlogs since the last full.

What do you mean by one batch? Each restore takes one backup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-17 : 20:01:08

If you restore in gui interface, you can choose all logs to restore then click on ok.

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-12-17 : 20:45:44

Tara, Thanks for that. What I meant in my first sentense, that for restoring 50 Tlogs, I have to restore first Full backup, Followed by Differential Backup (if any) and then Restore 50 Tlogs one by one 50 times...This is what I use to do and still know the same practice.

Now I wanted to know that is there any way I can select all 50 Tlogs and kick start Restore process in one hits? as per rmio answer I will try this and let you know...Thanks guys.

SKR

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-12-17 : 20:56:16

No It didn't work...
I restored Full Backup with NORECOVERY
Then Diff Backup with NOCOVERY
then Try to add transactional Log in the list, but when I select Multiple Logs, here is what I got

TITLE: Microsoft SQL Server Management Studio
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

The volume on device 'D:\Backups\Tempo\Tempo_backup_200712171845.trn' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3259)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=3259&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Any help guy..

SKR

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-12-17 : 21:13:48

As far as I know there is way to deal with this.
For Multiple Trasactional log restoration, grab the list of all avaiable log files and put them in a temp table and fire a restore command within loop.

Any other way to deal with this?

Many Thanks

SKR

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-17 : 21:37:26

Is 'D:\Backups\Tempo\Tempo_backup_200712171845.trn' a correct one?

rikleo2001
Posting Yak Master

185 Posts

Posted - 2007-12-17 : 21:44:51

yes.. Here is complete set, I can restore them one by one..but when I try to restore them as a bunch, it comes up with error message mentioned above.
'D:\Backups\Tempo\Tempo_backup_200712171830.trn'
'D:\Backups\Tempo\Tempo_backup_200712171845.trn'
'D:\Backups\Tempo\Tempo_backup_200712171900.trn'

Thanks

SKR

montu
Yak Posting Veteran

60 Posts

Posted - 2007-12-19 : 00:51:58

try following queries in QA
---Restore full
restore database database name
from disk = 'Location where bakup file exist'
WITH NORECOVERY,
replace--use if location of data and log files is same otherwise use MOVE 'Specify the location where uwant to store data and log files, need to specify twice for data and log'
--restore differential
restore database database name
from disk = 'Location where differential bakup file exist'
WITH noRECOVERY
--restore log
restore log database name
from disk = 'Location where trnsaction bakup file exist'
with norecovery

with last transaction log change norecovery with recovery. u can write all these queries in a file(.sql) and execute

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources