Restore transaction log?????

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.if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say :)|||Originally posted by ms_sql_dba
if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say :)

thank you,
i do follow your guide, but it does not work. please show me again
best regard.|||You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.|||Originally posted by rhigdon
You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.

Thanks, but i can not understan what you mean, please show me.
Best regard|||Do you have BOL installed? This is from BOL - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp

Try this script to illustrate:
--Add devices
USE master
EXEC sp_addumpdevice 'disk', 'MyFull',
'c:\myfull.dat'
EXEC sp_addumpdevice 'disk', 'MyDiff',
'c:\MyDiff.dat'
EXEC sp_addumpdevice 'disk', 'MyLog',
'c:\MyLog.dat'
--create database
create database blah
--set recovery model
alter database blah set recovery full
--do our backups
backup database blah to MyFull

use blah
create table afterfull(ident int identity(1,1))

BACKUP DATABASE blah TO MyDiff WITH DIFFERENTIAL

use blah
drop table afterfull
create table afterdiff (ident int identity(1,1))

backup log blah to MyLog

--Now start our restores
use master
RESTORE DATABASE blah
FROM MyFull
--The above works, this was the full backup
RESTORE DATABASE blah
FROM MyDiff
--The above does not work as you did the full restore without specifying "with recovery"
RESTORE DATABASE blah
FROM MyFull
with norecovery
RESTORE DATABASE blah
FROM MyDiff
--the above works as we specified norecovery with the full restore
--now try the tran log
RESTORE log blah
FROM MyLog
--Look familiar? We need to use the NORECOVERY with the full and DIFF so we can apply the log backup
RESTORE DATABASE blah
FROM MyFull
with norecovery
RESTORE DATABASE blah
FROM MyDiff
with norecovery
RESTORE log blah
FROM MyLog
--Voila!|||1. Create New Database
2. right click all task and restore database
3. tab options at Recovery completion state select "Leave database read-only and able to restore additional transaction logs"

Restore Timing Transaction Logs versus Differentials

Currently we backup out SQL Server 2005 databases with a combination of full, differential and transaction log backups and plan on continuing this practice. Currently all backups are written to physical servers other than the servers where the DBs are running and all backups are also written to tape. Our tape backups are costly and I am considering no longer writing the differential backups to tape to reduce costs. Before making this decision I would like to understand how much longer it would take to recover several days of transaction log backups, versus a single differential and only a few hours of transation log backups. Does anyone have any any information about the time difference? Any ratios or rules of thumb?

Thanks,
Julia

Stopping the tape copies of differentials seems like a reasonable plan IF you kept two or three of the DIFFERENTIAL BACKUP copies on disk. (I prefer to keep all differentials since the last FULL BACKUP, tossing them only after the next FULL BACKUP is verified.

Time to recover: Seems like the major time issue is the manual process of handling each individual restore. The fewer files to restore, the less 'manual' time. The greated the number of files involved, the greater the risk of a corrupt or damaged file.

|||
Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?
|||

There are so many variables on that, I wouldn't hazard a guess.

You could run a comparision test on another server, comparing the timings.

|||Has anyone run these types of timed tests comparing the restore times from 2 types of backups?|||

That is purely depends upon the number of transactions on your database to restore, see this blog http://sqlserver-qa.net/blogs/perftune/archive/2007/06/12/get-backup-and-restore-performance-stats-with-a-dmv.aspx on the performance stats for backup & restore tasks.

EgleK wrote:

Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?

Restore only data on a database

Hi to all,
I have created a program that weekly restore a database with differential backup.

It works fine the only thing is that when I restore the database the new user are dropped.

There is a way to restore only the data ?

code:

use master
ALTER DATABASE GOROB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE GOROB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\GOROBFULL'
with NORECOVERY

RESTORE DATABASE GOROB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Differential'
WITH FILE =1 ,
RECOVERY

ALTER DATABASE GOROB SET MULTI_USER

Thank you in advance

And data added to the database SINCE the differential backup will be lost when you do a restore.

Database Users are 'data' in the database.

You may wish to add a job step to script out the Users when you make the differential backup, and then add a job step to the restore process to run that script.

|||

Anrie thank you

for your reply, there is a way to look insite the users of the database and grant the access to it?

My problem is that when I do the restore and some database administrator create new user on a database

it cannot access to data .

So I have thinked to grant the access to each user of the db .

The thinks is how this cold be done?