November 11, 2008 at 6:36 am
USE master
Declare @RestoreFile varchar(255)
Set @RestoreFile='D:\Sql backup\Diff_'+ CONVERT(varchar, CURRENT_TIMESTAMP, 112) + '.BAK'
alter database dbname set offline with rollback immediate
RESTORE FILELISTONLY FROM DISK=@RestoreFile
RESTORE DATABASE dbname
FROM DISK=@RestoreFile
WITH
MOVE 'dbname _Data' TO 'D:\Sql Data\dbname _Data.mdf',
MOVE 'dbname _Log' TO 'D:\Sql Data\dbname _Log.ldf',
RECOVERY
alter database dbname set online with rollback immediate
this code give error follwing:
Server: Msg 4306, Level 16, State 1, Line 6
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.
So give some one suggestion......
how restore diffrential backup at existing database.
thanks
November 11, 2008 at 6:40 am
To restore a differential backup, you first need to restore the full back up with the NO RECOVERY option.
November 11, 2008 at 6:41 am
amitgupta.gec (11/11/2008)
USE masterDeclare @RestoreFile varchar(255)
Set @RestoreFile='D:\Sql backup\Diff_'+ CONVERT(varchar, CURRENT_TIMESTAMP, 112) + '.BAK'
alter database dbname set offline with rollback immediate
RESTORE FILELISTONLY FROM DISK=@RestoreFile
RESTORE DATABASE dbname
FROM DISK=@RestoreFile
WITH
MOVE 'dbname _Data' TO 'D:\Sql Data\dbname _Data.mdf',
MOVE 'dbname _Log' TO 'D:\Sql Data\dbname _Log.ldf',
RECOVERY
alter database dbname set online with rollback immediate
this code give error follwing:
Server: Msg 4306, Level 16, State 1, Line 6
The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step.
Server: Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.
So give some one suggestion......thanks
It looks like your restore file is a differential backup or a log backup and not a full database backup. When you restore differential or log backup, you need to restore a full backup first and use the switch with norecovery or with standby, so the server will know not to recover the database and that he should use other backup files. After the restore of the full backup finishes you can use the log or differential backup to continue the restore operation.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 11, 2008 at 6:45 am
yes i have taken with norecovery backup then its items show is blank means table,storedprocedure,etc
November 11, 2008 at 6:55 am
When you restore a database WITH NORECOVERY the database is not accessible until you finish the restore sequence WITH RECOVERY.
You must complete the final diff or log restore WITH RECOVERY and then the database will be fully accessible again.
November 11, 2008 at 9:59 am
What exactly are you trying to do here?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2008 at 9:11 pm
My main object is that i have daily deferential backup from a server and restore to another server by DTS.
So pls give me a sutable solution?
Thanks......
November 12, 2008 at 12:36 am
That's easy enough. Restore the full with no recovery and each day copy the diff over and restore that with norecovery.
What do you want to do with the second server? If it's just a hot standby, this will work (but log shipping would be as effective and less manual work). If it's not a hot standby that you want, please explain what you want the second server to be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 1:14 am
1st server have more work load and i want reporting from 2nd server daily.
November 12, 2008 at 1:24 am
Backup restore isn't a good option here. Two things you can consider.
Database mirroring with a database snapshot on the mirror. The snapshot is readonly and is an image of the DB at the time it was created. It can be queried.
Transactional/snapshot replication to create a copy of the DB elsewhere.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2008 at 1:55 am
yes i have other plan in a feature suggest me solution.
November 12, 2008 at 6:36 am
Amit Gupta (11/12/2008)
yes i have other plan in a feature suggest me solution.
Sorry, I don't understand.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply