Hi All,
I am trying to restore full backup in Sql Server 2005. I am using the following query to restore full backup.
"Restore database Testdb from Disk = 'C:\Testdbfull.bak'
with move 'Testdb' to 'C:\DB\Testdb.mdf ',
move 'Testdb_log' to 'C:\DB\Testdb_log.ldf ', norecovery "
The above query is running successfully and database restored. But i am not able to access this database since in the database tab it is showing 'Testdb (Restoring)'
The next day I want to restore the differential database backup so i used the 'norecovery' statement.
please help why it is showing as '(Restoring)'
Thanks in advance,
Senthil
The NORECOVERY keyword is used when you want to restore a set of backups and it leaves the database in a restoring state. It doesn't roll backup any uncommitted transactions as it expects futher backups to be applied. In order to bring the database online you just need to run the following command after you've applied your final backup:
RESTORE DATABASE mydb
WITH RECOVERY
Check Books Online for more info.
HTH!
|||Thanks Rich.
If i run the query which you mentioned then the database comes back to online.
But what my problem is for example i have restored full backup on Monday and i made some updates in the database.
On Tuesday I want to restore differential backup which i get from my client side everyday.
If i try to restore it is throwing error as:
Msg 3117, Level 16, State 4, Line 2
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Please help me what should i do.
Thanks in advance!
Senthil
|||All backups except the last one should be restored with no recovery. If you want to pull data incrementally (only the delta) you should take a look at replication or log shipping.
|||Thanks Vlad!
Can you explain or give some article link for log shipping.
I want to restore full backup on Monday and daily differential backup from client from Tuesday onwards.
Please explain how to do
Thanks
Senthil
|||First of all, what are you trying to accomplish? Please describe your environment.
As I understand - you have some client database and you want to have a copy of this database, but the only changed data should be copied, right? Then - do you want to update this secondary database or it will be read-only? What is your goal?
Log shipping documentation in Books Online:
http://msdn2.microsoft.com/en-us/library/ms187103.aspx
Basically log shipping takes transaction log backup on the primary database then copies it to the secondary database and restores it there, the secondary database could either in 'loading' or in 'read-only' mode, thus log shipping is best suitable for disaster recovery or for the reporting needs.|||Vlad,
I explain my requirement below.
Every week Monday morning i will get a full backup from my client side.
So i will restore this full backup as a new database in my local server on Monday. Also i will use this database for read and write.
From Tuesday to Friday i will get the daily differential backup from the client.
I want to restore this daily differential backup as daily basis and i want to retain the changes which i made in my local database server.
This is what my situation.
Currently what i am doing is Monday I will restore full backup as new database and Tuesday I will delete the database and again i restore full backup as new database with norecovery and restore differential backup with recovery clause. Then i will start using the database for Read and Write.
But what happened is whatever i made changes on Monday in my local server to that database will not be available.
Can you help me.
Thanks in advance.
Senthil
|||Ok, so you want to merge your changes with that changes made to the client database. In this case if you have a channel between you and the client, you could implement one-way merge replication (details you can also find in Books Online). You don't need a permanent link for the merge replication, but it should be available occasionally. If you don't have a channel - I think there is no out-of-the box soultion, so you will have to develop it yourself.
For example you can add 'rowversion' column to each table and then pull only new/changed data, and you will need to implement some conflict resolving mechanism in a case changes were done to the same data on both sides. This is one of the possible solutions.
Let me know if you have any questions.
|||Ok Vlad,
If i want to use the database for read-only purpose then how can i restore daily differential backups.
Thanks
Senthil
|||You can put database into stand-by mode. Take a look at the code below:
Code Snippet
use master
go
create database test_client
on
(name = 'test_client_data', filename = 'c:\test_client.mdf')
log on
(name = 'test_client_log', filename = 'c:\test_client.ldf')
go
use test_client
go
create table dbo.test (
i int identity
, data sysname
)
go
-- Some test data
insert into dbo.test (
data
)
select name
from master..sysobjects
go
-- Perform a full backup
backup database test_client to disk = 'c:\test_client_full.bak'
go
-- Restore it on your side as stand-by (read-only), so you will be able to restore additional backups
restore database test_client_standby
from disk = 'c:\test_client_full.bak'
with standby = 'c:\test_client_standby_undo',
move 'test_client_data' to 'c:\test_client_standby_data.mdf',
move 'test_client_log' to 'c:\test_client_standby_log.ldf',
replace
go
use test_client
go
-- Make some changes in the client database
insert into dbo.test (
data
)
values (
'new data from the client'
)
go
-- Perform a differential backup
backup database test_client to disk = 'c:\test_client_diff.bak' with differential
go
-- First in order to perform restore we should terminate all connections to this database
alter database test_client_standby
set single_user
with rollback immediate
go
-- Restore differential backup
restore database test_client_standby
from disk = 'c:\test_client_diff.bak'
with standby = 'c:\test_client_standby_undo'
go
-- Return database to multi-user mode
alter database test_client_standby
set multi_user
go
-- Check the data
use test_client_standby
go
select * from dbo.test where data = 'new data from the client'
go
refer this link for configuring log shipping,
http://sql-articles.com/articles.php and http://sql-articles.com/articles/lship/lship.htm
Now you can configure log shipping and restore the full backup of Mondays and subsequent log or differential backups using with recovery option in the destination server so that the db will be in Read-only mode..........
Senthil, If you restore mondays full backup and tue or weds differentials differential backup then the db will contain all the changes made since monday's full backup...........even if you perform any change on tuesday the diff backup will contain that and hence when you restore the diff backup it will not be the same as it was on monday...........
feel free to ask if you have anymore doubts............
Thanxx
Deepak
|||In my example there is no need for 'with standby = 'c:\test_client_standby_undo'' in the last restore statement, copy-paste, sorry 
this is actually a hand-made mini-version of the log shipping.
upd: arghhh, you do need to supply 'standby' clause if you need to restore additional backups, sorry once again.
|||Hi Vlad,
I have taken the code which you given and I am getting this below error when restore differential backup query runs.
--Query getting error:
restore database test_client_standby
from disk = 'c:\test_client_diff.bak'
--with standby = 'c:\test_client_standby_undo'
go
error msg.
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 DATABASE is terminating abnormally.
Any service tools setting is required to avoid this?
Senthil
|||hmmm, strange. please provide the result of select @.@.version
|||Thanks Deepak!!!!
I understood but i want to restore daily backup and use the database everyday as online.
Is it possible?
Senthil
没有评论:
发表评论