full text indexing backup

Hi,
Is it possible to backup and restore full text indexing?
If Yes, how to do it?
Can we restore that backup when full text index
is 'recovering'?
Help would be appreciated.
Thanks in advance.
Rajah V.
http://support.microsoft.com/default...b;en-us;240867
"Rajah" <rajah.venkat@.analytics-usa.com> wrote in message
news:1d71101c4231b$cc05d540$a101280a@.phx.gbl...
> Hi,
> Is it possible to backup and restore full text indexing?
> If Yes, how to do it?
> Can we restore that backup when full text index
> is 'recovering'?
> Help would be appreciated.
> Thanks in advance.
> Rajah V.

Full text index removal

RESTORE FILELISTONLY
FROM DISK = 'C:\Full0100.BAK'
Here is the result
database d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database
..mdf
database _Log d:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\database _log.ldf
sysft_PhraseologyIndex d:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\FTData\PhraseologyIndex
I would like to remove the sysft_PhraseologyIndex. The database server does
not have full-text component installed (this particular database was created
with sql server 2000 that supported the full-text indexing and upgraded to
sql server 2005 that does not support full-text index)
Thanks
You need to disable full-text indexing and then remove the
sysft_phraseologyindex file.
"iiman" <nospam@.nospam.com> wrote in message
news:evP$tdahHHA.1220@.TK2MSFTNGP03.phx.gbl...
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Full0100.BAK'
> Here is the result
> database d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database
> .mdf
> database _Log d:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\database _log.ldf
> sysft_PhraseologyIndex d:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTData\PhraseologyIndex
> I would like to remove the sysft_PhraseologyIndex. The database server
> does
> not have full-text component installed (this particular database was
> created
> with sql server 2000 that supported the full-text indexing and upgraded to
> sql server 2005 that does not support full-text index)
> Thanks
>
>
|||I tried,
ALTER DATABASE scope
REMOVE FILE sysft_PhraseologyIndex
I get
Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.
How do I remove the file?
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e$8GzmhhHHA.4596@.TK2MSFTNGP05.phx.gbl...
> You need to disable full-text indexing and then remove the
> sysft_phraseologyindex file.
> "iiman" <nospam@.nospam.com> wrote in message
> news:evP$tdahHHA.1220@.TK2MSFTNGP03.phx.gbl...
>

Full Restore Question

Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?
EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegr oups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>
|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?
|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegr oups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>
|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages
|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegrou ps.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>
|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!

Full Restore Question

Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and
sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and
use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm
:ss'.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time res
tore, you don't specify
the date and time, and you will not see that problem. But why not just execu
te the RESTORE command
from Query Analyzer instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also,
as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writi
ng and executing TSQL
makes us handle those situations quickly and solve the problem with minimal
impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.co
m.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!

Full Restore Question

Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
Database does a full back at 1am
Then from 4am on does a trans log backup
So had all file from 1am to about 5pm ( when the restore was attempted
)
I personally dont feel good about PITR but it was not my call.
Did the point in time recovery to 1206pm on that same day.
The restore ended up being right back at the time the restore was
started.
Any ideas?
I noticed doing the restore from Enterprise Manager when the checkbox
for point in time was clicked the list of backup files with checks by
them did not change.
Dont think they are supposed to.
How can I find out at what point in time the database actually did the
restore too?EM has a but in the PITR dialog, as it natializes that datetime display and sends that string into
SQL Server, which in turn can be misinterpreted. Do the restore from QA and use a language neutral
way of expressing the datetime literals (STOPAT value), like 'YYYYMMDD hh:mm:ss'.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136608836.006898.300840@.g44g2000cwa.googlegroups.com...
> Did a point in time restore on Win2k Server EE MSSQL 2K EE sp3a
> Database does a full back at 1am
> Then from 4am on does a trans log backup
> So had all file from 1am to about 5pm ( when the restore was attempted
> )
> I personally dont feel good about PITR but it was not my call.
> Did the point in time recovery to 1206pm on that same day.
> The restore ended up being right back at the time the restore was
> started.
> Any ideas?
> I noticed doing the restore from Enterprise Manager when the checkbox
> for point in time was clicked the list of backup files with checks by
> them did not change.
> Dont think they are supposed to.
> How can I find out at what point in time the database actually did the
> restore too?
>|||Thanks for the reply.
Question:
How buggy is just applying the full backup and then the transaction
logs that I need?
I have
1 full DB backup 1AM
- trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
12pm, etc
I wanted to go back to the 12PM trans log
so I can check all from 1AM to that 12pm trans log and I should be
good? Or does it natialize that also?|||The dialog problem is datetime related. So if you don't do point in time restore, you don't specify
the date and time, and you will not see that problem. But why not just execute the RESTORE command
from Query Analyzer instead?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136772868.780191.238640@.f14g2000cwb.googlegroups.com...
> Thanks for the reply.
> Question:
> How buggy is just applying the full backup and then the transaction
> logs that I need?
> I have
> 1 full DB backup 1AM
> - trans log at 4AM, 5, 6, 7, 8, 9, 10, 11,
> 12pm, etc
> I wanted to go back to the 12PM trans log
> so I can check all from 1AM to that 12pm trans log and I should be
> good? Or does it natialize that also?
>|||why not do it from where I am? I am not being smart, I just dont know
the benefits vs disadvantages|||Well, since you don't do PITR, you will not get your most recent data. Also, as you have noticed,
there's a bug in EM, and such we will find from time to time. Learning writing and executing TSQL
makes us handle those situations quickly and solve the problem with minimal impact on your
production system. I think that Aaron has an article on EM vs. QA on www.aspfaq.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1136843293.506230.8680@.g14g2000cwa.googlegroups.com...
> why not do it from where I am? I am not being smart, I just dont know
> the benefits vs disadvantages
>|||Thank You Tibor for your time and your professionalism.
I will read that article.
I will continue my walk away from the Oracle world !!!

Full & differential restore

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

Full & differential restore

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