Combination of Filegroup and Point in Time restore possible?

SQL2K5
Ive been doing some testing this morning and it appears that it is not
possible to combine PIT restores with Filegroup restores, is that correct? I
restore a Filegroup backup, and then the Trans Log backup, but there is no
option to stop at a specific time when doing this. It would sort of make
sense I suppose. It could be argued that doing so would leave the DB in an
inconsistent state if a table that resided outside of the restored Filegroup
had been updated before the restores began, but I just wanted to confirm my
findings(?).
I think the down side to this protection would be that if you are relying on
Filegroup backups/ restores, how would you recover a data loss that had
occured due to human error (someone deletes something they shouldn't have)?
After all, to bring the tables in the restored filegroup online I MUST take a
Trans Log backup and restore it, therefore my accidentally deleted data is
still not recovered.
Does this all sound right, or am I missing something here?
All insights are appreciated.Hi Chris
This sounds like your understanding is correct.
When doing a filegroup restore, SQL Server is assuming that other filegroups
are available, with transactions that might have occurred after the backup
of the filegroup that you are restoring. Restoring a FG requires that you
apply transaction logs to bring the FG up to the same point as the rest of
the database.
The point of FG restore is to recover from hardware errors on a single file
or fg. If you need to recover from user error, you'll need to restore the
whole db.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9D74B6E1-CE97-4B5F-9E97-C0F946ECA811@.microsoft.com...
> SQL2K5
> Ive been doing some testing this morning and it appears that it is not
> possible to combine PIT restores with Filegroup restores, is that correct?
> I
> restore a Filegroup backup, and then the Trans Log backup, but there is no
> option to stop at a specific time when doing this. It would sort of make
> sense I suppose. It could be argued that doing so would leave the DB in an
> inconsistent state if a table that resided outside of the restored
> Filegroup
> had been updated before the restores began, but I just wanted to confirm
> my
> findings(?).
> I think the down side to this protection would be that if you are relying
> on
> Filegroup backups/ restores, how would you recover a data loss that had
> occured due to human error (someone deletes something they shouldn't
> have)?
> After all, to bring the tables in the restored filegroup online I MUST
> take a
> Trans Log backup and restore it, therefore my accidentally deleted data is
> still not recovered.
> Does this all sound right, or am I missing something here?
> All insights are appreciated.|||Thanks!
"Kalen Delaney" wrote:
> Hi Chris
> This sounds like your understanding is correct.
> When doing a filegroup restore, SQL Server is assuming that other filegroups
> are available, with transactions that might have occurred after the backup
> of the filegroup that you are restoring. Restoring a FG requires that you
> apply transaction logs to bring the FG up to the same point as the rest of
> the database.
> The point of FG restore is to recover from hardware errors on a single file
> or fg. If you need to recover from user error, you'll need to restore the
> whole db.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:9D74B6E1-CE97-4B5F-9E97-C0F946ECA811@.microsoft.com...
> > SQL2K5
> >
> > Ive been doing some testing this morning and it appears that it is not
> > possible to combine PIT restores with Filegroup restores, is that correct?
> > I
> > restore a Filegroup backup, and then the Trans Log backup, but there is no
> > option to stop at a specific time when doing this. It would sort of make
> > sense I suppose. It could be argued that doing so would leave the DB in an
> > inconsistent state if a table that resided outside of the restored
> > Filegroup
> > had been updated before the restores began, but I just wanted to confirm
> > my
> > findings(?).
> >
> > I think the down side to this protection would be that if you are relying
> > on
> > Filegroup backups/ restores, how would you recover a data loss that had
> > occured due to human error (someone deletes something they shouldn't
> > have)?
> > After all, to bring the tables in the restored filegroup online I MUST
> > take a
> > Trans Log backup and restore it, therefore my accidentally deleted data is
> > still not recovered.
> >
> > Does this all sound right, or am I missing something here?
> >
> > All insights are appreciated.
>
>

Collation problem migrating 2000 > 2005

Hi,
Migrating SQL from 2000 to 2005 I did:
1/ backup of SQL2000 db :
BACKUP DATABASE [HCW] TO DISK = 'C:\data2\db.bak'
2/ restore it on 2005 :
RESTORE FILELISTONLY FROM DISK= 'C:\data2\db.bak'
RESTORE DATABASE [HCW] FROM DISK= 'C:\data2\db.bak' WITH
MOVE 'db_moss_Data' TO 'C:\Program files\Microsoft SQL
Server\MSSQL.2\MSSQL\Data\db_Data.mdf',
MOVE 'db_moss_Log' TO 'C:\Program files\Microsoft SQL
Server\MSSQL.2\MSSQL\Data\db_Log.ldf',
REPLACE
After updating connection string my .Net site works like a charm now on
2005.
Except FTS which returns following error:
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS"
and "Latin1_General_CI_AS" in the equal to operation.
What is the good practice to deal with this issue?
Thx for any help.This happened to me when some old SQL 7.0 table were brought into SQL
2000 with the conversion being done. I believe the term
"Latin1_General_CI_AS" is the old term for collation on 7.0. To solve
it I went into the table definitions with this collation and deleted
the user defined collation. When no collation is specified the
database default is inserted making it compatible with the current
database. You could also put in the value you wanted if you do not
want the default.
This fixed my problem.|||thank you.
solved by recreating the table using collation that I needed.
"miathet" <miathet@.gmail.com> wrote in message
news:1194295291.875474.263720@.o80g2000hse.googlegroups.com...
> This happened to me when some old SQL 7.0 table were brought into SQL
> 2000 with the conversion being done. I believe the term
> "Latin1_General_CI_AS" is the old term for collation on 7.0. To solve
> it I went into the table definitions with this collation and deleted
> the user defined collation. When no collation is specified the
> database default is inserted making it compatible with the current
> database. You could also put in the value you wanted if you do not
> want the default.
> This fixed my problem.
>

Collation Problem - Migration 7.0 to 2000

Hi,
I have a SQL7 user database backup that I load into the
SQL2000 without problem. When the restore operation is
finished I got the database loaded with
Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command
alter database Glb
COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
the on-line book but I got this message
Line 1: Incorrect syntax
near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with
the same tempdb COLLATION to avoid further problem
like "Cannot resolve collation conflict for equal to
operation."This is a multi-part message in MIME format.
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Leave out the quotes:
alter database Glb COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
Hi,
I have a SQL7 user database backup that I load into the SQL2000 without problem. When the restore operation is finished I got the database loaded with Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in the on-line book but I got this message
Line 1: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with the same tempdb COLLATION to avoid further problem like "Cannot resolve collation conflict for equal to operation."
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Leave out the quotes:
alter database Glb =COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...Hi,I have a SQL7 user database backup that I load into the SQL2000 =without problem. When the restore operation is finished I got the database =loaded with Compatibility_52_409_300003 COLLATION.How could I =change the COLLATION set for that database?I am trying to use this following =command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as =described in the on-line book but I got this messageLine 1: Incorrect =syntax near 'SQL_Latin1_General_CP1_CI_AS'.In fact, I am trying to =do this to have my database with the same tempdb COLLATION to avoid further =problem like "Cannot resolve collation conflict for equal to operation."

---

--=_NextPart_000_0101_01C35748.686076C0--|||Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just curious, what is the database compatibility level? Have you =changed it to 80? Check out sp_dbcmptlevel in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the ISQL too and put the database in the restricted use... and nothing.
Thanks in advance and if you have another suggestion, please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the >SQL2000 without problem. When the restore operation is >finished I got the database loaded with >Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book but I got this message
>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with >the same tempdb COLLATION to avoid further problem >like "Cannot resolve collation conflict for equal to >operation."
>
>
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Just curious, what is the database =compatibility level? Have you changed it to 80? Check out sp_dbcmptlevel =in the BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0e7301c3576d$ae=023d60$a301280a@.phx.gbl...Thanks TomI got the same error without the quotes. I tried in the =ISQL too and put the database in the restricted use... and =nothing.Thanks in advance and if you have another suggestion, please send it.>--Original Message-->Leave out the quotes:>>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Marc=elo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...>Hi,>>I have a SQL7 user database backup that I load into the >SQL2000 =without problem. When the restore operation is >finished I got the =database loaded with >Compatibility_52_409_300003 =COLLATION.>>How could I change the COLLATION set for that database?>I am trying =to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book =but I got this message>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.>>In fact, I am trying to =do this to have my database with >the same tempdb COLLATION to avoid =further problem >like "Cannot resolve collation conflict for equal to >operation.">>>

---

--=_NextPart_000_0172_01C3574D.102E6480--|||Unfortunately, changing the database's default collation won't change the
collation of any existing tables or columns; it only affects new tables
that are created in that database. In other words, it won't have any
effect on your collation conflict errors.
It is not trivial to change the collation of all the columns in an existing
database. If you have the option, probably the easiest solution by far is
to modify your stored procs slightly so that they use "COLLATE
database_default" when create temp tables. This will tell SQL that the
temp table's collation should be inherited from the current user database,
not tempdb. From that point forward your code will be "server
collation-agnostic", meaning you can move this database to any server with
any collation and the code will run fine. An example:
CREATE #temptbl (
col1 int,
col2 varchar(20) COLLATE database_default,
col3 nchar(4) COLLATE database_default
)
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Tom Moreau" <tom@.dont.spam.me.cips.ca>
References: <0f3101c35768$1eefe780$a101280a@.phx.gbl>
<#J4n2n2VDHA.392@.TK2MSFTNGP11.phx.gbl>
<0e7301c3576d$ae023d60$a301280a@.phx.gbl>
Subject: Re: Collation Problem - Migration 7.0 to 2000
Date: Thu, 31 Jul 2003 10:18:19 -0400
Lines: 154
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="--=_NextPart_000_0172_01C3574D.102E6480"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
Message-ID: <e78Jk62VDHA.3220@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: 142.108.149.181
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298816
X-Tomcat-NG: microsoft.public.sqlserver.server
Just curious, what is the database compatibility level? Have you changed
it to 80? Check out sp_dbcmptlevel in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message
news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>