Backup differential

Hello everybody,
I need to get a differential backup in a small file but i am getting a
trouble ...
I got a Full backup with size=800MB
Then i got a differential backup with size=775B
It is an OLAP database then a lot of tables got a "DELETE FROM" command then
data is inserted again, it happens every early morning.
I hope it is correct, but i am not sure, i need some help with that.Leandro
Look at this very useful article
http://vyaskn.tripod.com/sql_server_administration_best_practices.htm#Step1
--administaiting best practices
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||I have to say that this database is Recovery Model SIMPLE ...
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:%234ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Well, if you delete from and insert into many tables, then you are modifying
many extents, so diff backup is nearly as big as full db backup. Can you
change your data population procedure to incrementaly update the OLAP
database? Or at least to delete only a subset of rows, for example younger
than a month, and then insert only new rows?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||If i keep the ETL this way i will have the same problem with Replication?
Thanks ...
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> escreveu
na mensagem news:udmeT3U9DHA.3380@.tk2msftngp13.phx.gbl...
> Well, if you delete from and insert into many tables, then you are
modifying
> many extents, so diff backup is nearly as big as full db backup. Can you
> change your data population procedure to incrementaly update the OLAP
> database? Or at least to delete only a subset of rows, for example younger
> than a month, and then insert only new rows?
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
> news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> > Hello everybody,
> >
> > I need to get a differential backup in a small file but i am getting a
> > trouble ...
> >
> > I got a Full backup with size=800MB
> > Then i got a differential backup with size=775B
> > It is an OLAP database then a lot of tables got a "DELETE FROM" command
> then
> > data is inserted again, it happens every early morning.
> >
> > I hope it is correct, but i am not sure, i need some help with that.
> >
> >
>|||> If i keep the ETL this way i will have the same problem with Replication?
> Thanks ...
Sorry, I don't understand - where does replication fit here? Are you
replicating OLAP database? Which type of replication are you using? Anyway,
replication should gain from smaller updates as well.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Yes, if you replicate (having deleted then re-inserted many rows), the
replication will go very slowly, as it must do the same...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:#4ZEDzU9DHA.2712@.tk2msftngp13.phx.gbl...
> Hello everybody,
> I need to get a differential backup in a small file but i am getting a
> trouble ...
> I got a Full backup with size=800MB
> Then i got a differential backup with size=775B
> It is an OLAP database then a lot of tables got a "DELETE FROM" command
then
> data is inserted again, it happens every early morning.
> I hope it is correct, but i am not sure, i need some help with that.
>|||Leandro
With a datawarehouse (OLAP) system where most of the data
is changing on a daily basis, it might be worth looking at
the source of your data. That is where your backup and
recovery should be pursued. Supplying a new input file to
your system looks to be almost as good as restoring a full
backup.
It is not uncommon with datawarehouses that it is as easy
to recover the database from source as a backup. This is
why it is the exception to the rule that all production
databases should be in full recovery mode.
Regards
Johnsql

backup and restore trouble.

Hello,
We want to do a restore to a point of time.
With recovery mode on full.
Can one do a restore to a point of time before the last full backup
with a transaction log backup made after the full backup ?
Sequence.
(full recovery mode, SQL-server 7. I think.).
a. Somewere in the past a full backup is made.
b. An error is made.
c. A full backup is made.
d. A transaction log backup is made.
We want to restore to a point in time just before
the error (b.) is made. Is this possible ?
(I have got the BOL (from 7) and inside from Kalen but
can not find the anwsers there).
Ben Brugman.> We want to do a restore to a point of time.
> With recovery mode on full.
> Sequence.
> (full recovery mode, SQL-server 7. I think.).
There is no FULL recovery mode in SQL 7. In SQL 7 and earlier versions,
the 'trunc. log on chkpt.' database option turned off is similar.
> a. Somewere in the past a full backup is made.
> b. An error is made.
> c. A full backup is made.
> d. A transaction log backup is made.
> We want to restore to a point in time just before
> the error (b.) is made. Is this possible ?
You can perform point-in-time recovery as follows:
1) Restore full database backup from step 'a' WITH NORECOVERY
2) Restore transaction log backup from step 'd' WITH STOPAT (time
before step 'b' error) and RECOVERY
Note that if you have any other log backups between 'a' and 'b', these
will need to be applied (WITH NORECOVERY) before step '2' above.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"ben brugman" <ben@.niethier.nl> wrote in message
news:bkhf32$mlv$1@.reader10.wxs.nl...
> Hello,
> We want to do a restore to a point of time.
> With recovery mode on full.
> Can one do a restore to a point of time before the last full backup
> with a transaction log backup made after the full backup ?
> Sequence.
> (full recovery mode, SQL-server 7. I think.).
> a. Somewere in the past a full backup is made.
> b. An error is made.
> c. A full backup is made.
> d. A transaction log backup is made.
> We want to restore to a point in time just before
> the error (b.) is made. Is this possible ?
> (I have got the BOL (from 7) and inside from Kalen but
> can not find the anwsers there).
> Ben Brugman.
>|||Greg,
I too have been having problems with the restore to a point in time
and your sample script was useful in trying to come to grips with
this. All this seems to do though , is to restore the datbase to the
state it was in when it was backed up to a_bak1.bak.
I have managed to write a script to add 4 records to the database ,
then do a full backup to a_bak1, then add a few more records then do
another full backup and a transaction log backup. I now want to
restore to a point where I have only entered the first two records,
but all that seems to happen is that I am restored to the point of the
first full backup.
I have attached my script (which is heavily based on the one you
originally posted).
What am I doing wrong ?...it's been driving me mad for days !!
All help gratefully received .
Kind Regards,
Nigel
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> Hi Ben.
> Are you sure you're on SQL 7.0? The term "full recovery" only came along
> with SQL 2000...
> Anyway, this should be simple - restore the last good full backup without
> recovering, then apply the transaction log & stopat the appropriate time.
> Below is a demo script that should work on both 7.0 & 2000, whichever you're
> using. Follow it carefully & you should be able to see that this is
> possible. Note that the table t1 is populated with a row, which is then
> deleted between the two full backups. By performing a normal full database
> restore without recovery, then a log restore with recovery, you can get the
> point in time you're after.
> set nocount on
> go
> use master
> go
> create database a
> on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> go
> use a
> go
> create table t1 (c1 int)
> create table t2 (restoretime varchar(26))
> go
> /* insert a row into t1. we'll expect to see this row again after restore,
> despite it being deleted before full backup 2 */
> insert into t1 values (1)
> go
> use master
> go
> /* your full backup from whenever */
> backup database a to disk='c:\a_bak1.bak'
> go
> use a
> go
> /* insert a time into t2 that can be read as a stopat time accross batches
> */
> insert into t2 values (convert(varchar(26), getdate(), 9))
> go
> /* delay one minute */
> waitfor delay '00:01:00'
> go
> /* delete the row from t1. This represents the mistake we want to recover
> before.. */
> delete from t1
> go
> use master
> go
> /* your secondary, post mistake full backup */
> backup database a to disk='c:\a_bak2.bak'
> go
> /* your log backup */
> backup log a to disk='c:\a_bak3.bak'
> go
> /* we restore to point in time captured in t2. I'm only using t2 so we could
> record that point in time accross batches for the purposes of this example
> script */
> use a
> declare @.restoretime varchar(26)
> select @.restoretime = min(restoretime) from t2
> use master
> restore database a from disk='c:\a_bak1.bak' with norecovery
> restore log a from disk='c:\a_bak3.bak' with recovery, stopat = @.restoretime
> go
> use a
> go
> /* prove that the deleted row from t1 is restored */
> select * from t1
> go
> /* clean up */
> use master
> go
> drop database a
> go
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:bkhf32$mlv$1@.reader10.wxs.nl...
> > Hello,
> >
> > We want to do a restore to a point of time.
> >
> > With recovery mode on full.
> > Can one do a restore to a point of time before the last full backup
> > with a transaction log backup made after the full backup ?
> >
> > Sequence.
> >
> > (full recovery mode, SQL-server 7. I think.).
> > a. Somewere in the past a full backup is made.
> > b. An error is made.
> > c. A full backup is made.
> > d. A transaction log backup is made.
> >
> > We want to restore to a point in time just before
> > the error (b.) is made. Is this possible ?
> > (I have got the BOL (from 7) and inside from Kalen but
> > can not find the anwsers there).
> >
> > Ben Brugman.
> >
> >|||Hi Nigel.
There's no attachment to your post. I'm not sure, but I think these might be
getting dropped by the news-servers at the moment, so please re-post with
your script in the body of your post & I'll look at the script & try to work
it out for you..
Don't email it to me as I have a strong filter on my email & you're not in
my address book, so you won't get through..
Regards,
Greg Linwood
SQL Server MVP
"NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
news:26210f56.0310080734.53626df5@.posting.google.com...
> Greg,
> I too have been having problems with the restore to a point in time
> and your sample script was useful in trying to come to grips with
> this. All this seems to do though , is to restore the datbase to the
> state it was in when it was backed up to a_bak1.bak.
> I have managed to write a script to add 4 records to the database ,
> then do a full backup to a_bak1, then add a few more records then do
> another full backup and a transaction log backup. I now want to
> restore to a point where I have only entered the first two records,
> but all that seems to happen is that I am restored to the point of the
> first full backup.
> I have attached my script (which is heavily based on the one you
> originally posted).
> What am I doing wrong ?...it's been driving me mad for days !!
> All help gratefully received .
>
> Kind Regards,
> Nigel
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > Hi Ben.
> >
> > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > with SQL 2000...
> >
> > Anyway, this should be simple - restore the last good full backup
without
> > recovering, then apply the transaction log & stopat the appropriate
time.
> > Below is a demo script that should work on both 7.0 & 2000, whichever
you're
> > using. Follow it carefully & you should be able to see that this is
> > possible. Note that the table t1 is populated with a row, which is then
> > deleted between the two full backups. By performing a normal full
database
> > restore without recovery, then a log restore with recovery, you can get
the
> > point in time you're after.
> >
> > set nocount on
> > go
> > use master
> > go
> > create database a
> > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > go
> > use a
> > go
> > create table t1 (c1 int)
> > create table t2 (restoretime varchar(26))
> > go
> > /* insert a row into t1. we'll expect to see this row again after
restore,
> > despite it being deleted before full backup 2 */
> > insert into t1 values (1)
> > go
> > use master
> > go
> > /* your full backup from whenever */
> > backup database a to disk='c:\a_bak1.bak'
> > go
> > use a
> > go
> > /* insert a time into t2 that can be read as a stopat time accross
batches
> > */
> > insert into t2 values (convert(varchar(26), getdate(), 9))
> > go
> > /* delay one minute */
> > waitfor delay '00:01:00'
> > go
> > /* delete the row from t1. This represents the mistake we want to
recover
> > before.. */
> > delete from t1
> > go
> > use master
> > go
> > /* your secondary, post mistake full backup */
> > backup database a to disk='c:\a_bak2.bak'
> > go
> > /* your log backup */
> > backup log a to disk='c:\a_bak3.bak'
> > go
> > /* we restore to point in time captured in t2. I'm only using t2 so we
could
> > record that point in time accross batches for the purposes of this
example
> > script */
> > use a
> > declare @.restoretime varchar(26)
> > select @.restoretime = min(restoretime) from t2
> > use master
> > restore database a from disk='c:\a_bak1.bak' with norecovery
> > restore log a from disk='c:\a_bak3.bak' with recovery, stopat =@.restoretime
> > go
> > use a
> > go
> > /* prove that the deleted row from t1 is restored */
> > select * from t1
> > go
> > /* clean up */
> > use master
> > go
> > drop database a
> > go
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > Hello,
> > >
> > > We want to do a restore to a point of time.
> > >
> > > With recovery mode on full.
> > > Can one do a restore to a point of time before the last full backup
> > > with a transaction log backup made after the full backup ?
> > >
> > > Sequence.
> > >
> > > (full recovery mode, SQL-server 7. I think.).
> > > a. Somewere in the past a full backup is made.
> > > b. An error is made.
> > > c. A full backup is made.
> > > d. A transaction log backup is made.
> > >
> > > We want to restore to a point in time just before
> > > the error (b.) is made. Is this possible ?
> > > (I have got the BOL (from 7) and inside from Kalen but
> > > can not find the anwsers there).
> > >
> > > Ben Brugman.
> > >
> > >|||Hi Greg,
The script is as follows..I know its a terrible script, I'm having to
learn as I go with this..
---
set nocount on
go
use master
create database a
on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
go
use a
go
create table t1 (c1 int, restoretime varchar(26))
create table t2 (restoretime varchar (26))
go
backup database a to disk='c:\a_bak0.bak'
go
insert into t1 values(1 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(2 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(3 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
insert into t1 values(4 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
waitfor delay '00:00:05'
go
use master
go
backup database a to disk='c:\a_bak1.bak'
go
use a
go
insert into t1 values(5 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
go
waitfor delay '00:00:05'
insert into t1 values(6 , convert(varchar(26), getdate() , 9))
insert into t2 values(convert(varchar(26), getdate() , 9))
go
waitfor delay '00:00:05'
go
use master
go
backup database a to disk='c:\a_bak2.bak'
go
backup log a to disk='c:\a_bak3.bak'
go
use a
go
----
Basically I'm trying to prove to myself that I can decide to restore
back to any particular transaction, which I assume is what restore to
point in time is all about.
I think I understood your original example script, but as far as i
could see there was no need to use the transaction log as the database
would be put back to the desired point in time simply by restore the
first full backup.
Basically I'm trying to prove to myself that I can decide to restore
back to any particular transaction, which I assume is what restore to
point in time is all about.
What I am attempting to do is try to restore to the point just after
the 2nd or 3rd transaction.
Thanks for your interest in my problem, most appreciated..and thanks
for the quick response.
Nigel
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> Hi Nigel.
> There's no attachment to your post. I'm not sure, but I think these might be
> getting dropped by the news-servers at the moment, so please re-post with
> your script in the body of your post & I'll look at the script & try to work
> it out for you..
> Don't email it to me as I have a strong filter on my email & you're not in
> my address book, so you won't get through..
> Regards,
> Greg Linwood
> SQL Server MVP
> "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> news:26210f56.0310080734.53626df5@.posting.google.com...
> > Greg,
> >
> > I too have been having problems with the restore to a point in time
> > and your sample script was useful in trying to come to grips with
> > this. All this seems to do though , is to restore the datbase to the
> > state it was in when it was backed up to a_bak1.bak.
> >
> > I have managed to write a script to add 4 records to the database ,
> > then do a full backup to a_bak1, then add a few more records then do
> > another full backup and a transaction log backup. I now want to
> > restore to a point where I have only entered the first two records,
> > but all that seems to happen is that I am restored to the point of the
> > first full backup.
> > I have attached my script (which is heavily based on the one you
> > originally posted).
> >
> > What am I doing wrong ?...it's been driving me mad for days !!
> >
> > All help gratefully received .
> >
> >
> > Kind Regards,
> >
> > Nigel
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > Hi Ben.
> > >
> > > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > > with SQL 2000...
> > >
> > > Anyway, this should be simple - restore the last good full backup
> without
> > > recovering, then apply the transaction log & stopat the appropriate
> time.
> > > Below is a demo script that should work on both 7.0 & 2000, whichever
> you're
> > > using. Follow it carefully & you should be able to see that this is
> > > possible. Note that the table t1 is populated with a row, which is then
> > > deleted between the two full backups. By performing a normal full
> database
> > > restore without recovery, then a log restore with recovery, you can get
> the
> > > point in time you're after.
> > >
> > > set nocount on
> > > go
> > > use master
> > > go
> > > create database a
> > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > > go
> > > use a
> > > go
> > > create table t1 (c1 int)
> > > create table t2 (restoretime varchar(26))
> > > go
> > > /* insert a row into t1. we'll expect to see this row again after
> restore,
> > > despite it being deleted before full backup 2 */
> > > insert into t1 values (1)
> > > go
> > > use master
> > > go
> > > /* your full backup from whenever */
> > > backup database a to disk='c:\a_bak1.bak'
> > > go
> > > use a
> > > go
> > > /* insert a time into t2 that can be read as a stopat time accross
> batches
> > > */
> > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > go
> > > /* delay one minute */
> > > waitfor delay '00:01:00'
> > > go
> > > /* delete the row from t1. This represents the mistake we want to
> recover
> > > before.. */
> > > delete from t1
> > > go
> > > use master
> > > go
> > > /* your secondary, post mistake full backup */
> > > backup database a to disk='c:\a_bak2.bak'
> > > go
> > > /* your log backup */
> > > backup log a to disk='c:\a_bak3.bak'
> > > go
> > > /* we restore to point in time captured in t2. I'm only using t2 so we
> could
> > > record that point in time accross batches for the purposes of this
> example
> > > script */
> > > use a
> > > declare @.restoretime varchar(26)
> > > select @.restoretime = min(restoretime) from t2
> > > use master
> > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => @.restoretime
> > > go
> > > use a
> > > go
> > > /* prove that the deleted row from t1 is restored */
> > > select * from t1
> > > go
> > > /* clean up */
> > > use master
> > > go
> > > drop database a
> > > go
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > Hello,
> > > >
> > > > We want to do a restore to a point of time.
> > > >
> > > > With recovery mode on full.
> > > > Can one do a restore to a point of time before the last full backup
> > > > with a transaction log backup made after the full backup ?
> > > >
> > > > Sequence.
> > > >
> > > > (full recovery mode, SQL-server 7. I think.).
> > > > a. Somewere in the past a full backup is made.
> > > > b. An error is made.
> > > > c. A full backup is made.
> > > > d. A transaction log backup is made.
> > > >
> > > > We want to restore to a point in time just before
> > > > the error (b.) is made. Is this possible ?
> > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > can not find the anwsers there).
> > > >
> > > > Ben Brugman.
> > > >
> > > >|||Hi Greg,
Please ignore my last post. I have sorted out the problems with the
restore to a point in time. I have noticed however that when I try to
restore to a time which is later than the last entry in the
transaction log, the database is left in a loading state. I am using
SQL Server 2000 with SP3A installed, and according to MS knowledgebase
article 319697 this particular issue was fixed with SP3. It also
states that the problem was when using Enterprise Manager to carry out
the restore. I am using a script. Have you come across this problem
even when SP3 has been installed ?
Kind Regards,
Nigel
Nigel_Stallard@.hotmail.com (NIgel Stallard) wrote in message news:<26210f56.0310090259.664b6871@.posting.google.com>...
> Hi Greg,
> The script is as follows..I know its a terrible script, I'm having to
> learn as I go with this..
> ---
> set nocount on
> go
> use master
> create database a
> on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
> log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
> go
> use a
> go
> create table t1 (c1 int, restoretime varchar(26))
> create table t2 (restoretime varchar (26))
> go
> backup database a to disk='c:\a_bak0.bak'
> go
> insert into t1 values(1 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(2 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(3 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> insert into t1 values(4 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> waitfor delay '00:00:05'
> go
> use master
> go
> backup database a to disk='c:\a_bak1.bak'
> go
> use a
> go
> insert into t1 values(5 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> go
> waitfor delay '00:00:05'
> insert into t1 values(6 , convert(varchar(26), getdate() , 9))
> insert into t2 values(convert(varchar(26), getdate() , 9))
> go
> waitfor delay '00:00:05'
> go
> use master
> go
> backup database a to disk='c:\a_bak2.bak'
> go
> backup log a to disk='c:\a_bak3.bak'
> go
> use a
> go
> ----
> Basically I'm trying to prove to myself that I can decide to restore
> back to any particular transaction, which I assume is what restore to
> point in time is all about.
> I think I understood your original example script, but as far as i
> could see there was no need to use the transaction log as the database
> would be put back to the desired point in time simply by restore the
> first full backup.
> Basically I'm trying to prove to myself that I can decide to restore
> back to any particular transaction, which I assume is what restore to
> point in time is all about.
> What I am attempting to do is try to restore to the point just after
> the 2nd or 3rd transaction.
> Thanks for your interest in my problem, most appreciated..and thanks
> for the quick response.
> Nigel
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> > Hi Nigel.
> >
> > There's no attachment to your post. I'm not sure, but I think these might be
> > getting dropped by the news-servers at the moment, so please re-post with
> > your script in the body of your post & I'll look at the script & try to work
> > it out for you..
> >
> > Don't email it to me as I have a strong filter on my email & you're not in
> > my address book, so you won't get through..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> > news:26210f56.0310080734.53626df5@.posting.google.com...
> > > Greg,
> > >
> > > I too have been having problems with the restore to a point in time
> > > and your sample script was useful in trying to come to grips with
> > > this. All this seems to do though , is to restore the datbase to the
> > > state it was in when it was backed up to a_bak1.bak.
> > >
> > > I have managed to write a script to add 4 records to the database ,
> > > then do a full backup to a_bak1, then add a few more records then do
> > > another full backup and a transaction log backup. I now want to
> > > restore to a point where I have only entered the first two records,
> > > but all that seems to happen is that I am restored to the point of the
> > > first full backup.
> > > I have attached my script (which is heavily based on the one you
> > > originally posted).
> > >
> > > What am I doing wrong ?...it's been driving me mad for days !!
> > >
> > > All help gratefully received .
> > >
> > >
> > > Kind Regards,
> > >
> > > Nigel
> > >
> > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > > Hi Ben.
> > > >
> > > > Are you sure you're on SQL 7.0? The term "full recovery" only came along
> > > > with SQL 2000...
> > > >
> > > > Anyway, this should be simple - restore the last good full backup
> without
> > > > recovering, then apply the transaction log & stopat the appropriate
> time.
> > > > Below is a demo script that should work on both 7.0 & 2000, whichever
> you're
> > > > using. Follow it carefully & you should be able to see that this is
> > > > possible. Note that the table t1 is populated with a row, which is then
> > > > deleted between the two full backups. By performing a normal full
> database
> > > > restore without recovery, then a log restore with recovery, you can get
> the
> > > > point in time you're after.
> > > >
> > > > set nocount on
> > > > go
> > > > use master
> > > > go
> > > > create database a
> > > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb )
> > > > go
> > > > use a
> > > > go
> > > > create table t1 (c1 int)
> > > > create table t2 (restoretime varchar(26))
> > > > go
> > > > /* insert a row into t1. we'll expect to see this row again after
> restore,
> > > > despite it being deleted before full backup 2 */
> > > > insert into t1 values (1)
> > > > go
> > > > use master
> > > > go
> > > > /* your full backup from whenever */
> > > > backup database a to disk='c:\a_bak1.bak'
> > > > go
> > > > use a
> > > > go
> > > > /* insert a time into t2 that can be read as a stopat time accross
> batches
> > > > */
> > > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > > go
> > > > /* delay one minute */
> > > > waitfor delay '00:01:00'
> > > > go
> > > > /* delete the row from t1. This represents the mistake we want to
> recover
> > > > before.. */
> > > > delete from t1
> > > > go
> > > > use master
> > > > go
> > > > /* your secondary, post mistake full backup */
> > > > backup database a to disk='c:\a_bak2.bak'
> > > > go
> > > > /* your log backup */
> > > > backup log a to disk='c:\a_bak3.bak'
> > > > go
> > > > /* we restore to point in time captured in t2. I'm only using t2 so we
> could
> > > > record that point in time accross batches for the purposes of this
> example
> > > > script */
> > > > use a
> > > > declare @.restoretime varchar(26)
> > > > select @.restoretime = min(restoretime) from t2
> > > > use master
> > > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => @.restoretime
> > > > go
> > > > use a
> > > > go
> > > > /* prove that the deleted row from t1 is restored */
> > > > select * from t1
> > > > go
> > > > /* clean up */
> > > > use master
> > > > go
> > > > drop database a
> > > > go
> > > >
> > > > HTH
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > > Hello,
> > > > >
> > > > > We want to do a restore to a point of time.
> > > > >
> > > > > With recovery mode on full.
> > > > > Can one do a restore to a point of time before the last full backup
> > > > > with a transaction log backup made after the full backup ?
> > > > >
> > > > > Sequence.
> > > > >
> > > > > (full recovery mode, SQL-server 7. I think.).
> > > > > a. Somewere in the past a full backup is made.
> > > > > b. An error is made.
> > > > > c. A full backup is made.
> > > > > d. A transaction log backup is made.
> > > > >
> > > > > We want to restore to a point in time just before
> > > > > the error (b.) is made. Is this possible ?
> > > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > > can not find the anwsers there).
> > > > >
> > > > > Ben Brugman.
> > > > >
> > > > >|||I tried some restores to a point in time. What I experienced was
that I could not restore to before the first transaction log backup.
Could be that I did not use the correct procedure, but I tried
several way to restore to some points in time all times after
the first transaction log backup were possible none before.
My solution is do a transaction backup immediatly after you have
created a database. Then there is no problem.
Could be that your problem is similar ?
Can you confirm the above ?
Thanks for sharing your knowledge,
ben brugman
"NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
news:26210f56.0310090757.2a60601a@.posting.google.com...
> Hi Greg,
> Please ignore my last post. I have sorted out the problems with the
> restore to a point in time. I have noticed however that when I try to
> restore to a time which is later than the last entry in the
> transaction log, the database is left in a loading state. I am using
> SQL Server 2000 with SP3A installed, and according to MS knowledgebase
> article 319697 this particular issue was fixed with SP3. It also
> states that the problem was when using Enterprise Manager to carry out
> the restore. I am using a script. Have you come across this problem
> even when SP3 has been installed ?
> Kind Regards,
> Nigel
> Nigel_Stallard@.hotmail.com (NIgel Stallard) wrote in message
news:<26210f56.0310090259.664b6871@.posting.google.com>...
> > Hi Greg,
> >
> > The script is as follows..I know its a terrible script, I'm having to
> > learn as I go with this..
> > ---
> > set nocount on
> > go
> > use master
> > create database a
> > on (name=a_dat, filename='c:\a_dat.mdf', size=1Mb, filegrowth=1mb)
> > log on (name=a_log, filename='c:\a_log.ldf', size=1mb, filegrowth=1mb)
> > go
> > use a
> > go
> > create table t1 (c1 int, restoretime varchar(26))
> > create table t2 (restoretime varchar (26))
> > go
> > backup database a to disk='c:\a_bak0.bak'
> > go
> > insert into t1 values(1 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(2 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(3 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > insert into t1 values(4 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > waitfor delay '00:00:05'
> > go
> > use master
> > go
> > backup database a to disk='c:\a_bak1.bak'
> > go
> > use a
> > go
> > insert into t1 values(5 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > go
> > waitfor delay '00:00:05'
> > insert into t1 values(6 , convert(varchar(26), getdate() , 9))
> > insert into t2 values(convert(varchar(26), getdate() , 9))
> > go
> > waitfor delay '00:00:05'
> > go
> > use master
> > go
> > backup database a to disk='c:\a_bak2.bak'
> > go
> > backup log a to disk='c:\a_bak3.bak'
> > go
> > use a
> > go
> ----
> >
> > Basically I'm trying to prove to myself that I can decide to restore
> > back to any particular transaction, which I assume is what restore to
> > point in time is all about.
> >
> > I think I understood your original example script, but as far as i
> > could see there was no need to use the transaction log as the database
> > would be put back to the desired point in time simply by restore the
> > first full backup.
> >
> > Basically I'm trying to prove to myself that I can decide to restore
> > back to any particular transaction, which I assume is what restore to
> > point in time is all about.
> > What I am attempting to do is try to restore to the point just after
> > the 2nd or 3rd transaction.
> >
> > Thanks for your interest in my problem, most appreciated..and thanks
> > for the quick response.
> >
> > Nigel
> >
> >
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:<OvviP5ejDHA.2000@.TK2MSFTNGP12.phx.gbl>...
> > > Hi Nigel.
> > >
> > > There's no attachment to your post. I'm not sure, but I think these
might be
> > > getting dropped by the news-servers at the moment, so please re-post
with
> > > your script in the body of your post & I'll look at the script & try
to work
> > > it out for you..
> > >
> > > Don't email it to me as I have a strong filter on my email & you're
not in
> > > my address book, so you won't get through..
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "NIgel Stallard" <Nigel_Stallard@.hotmail.com> wrote in message
> > > news:26210f56.0310080734.53626df5@.posting.google.com...
> > > > Greg,
> > > >
> > > > I too have been having problems with the restore to a point in time
> > > > and your sample script was useful in trying to come to grips with
> > > > this. All this seems to do though , is to restore the datbase to the
> > > > state it was in when it was backed up to a_bak1.bak.
> > > >
> > > > I have managed to write a script to add 4 records to the database ,
> > > > then do a full backup to a_bak1, then add a few more records then do
> > > > another full backup and a transaction log backup. I now want to
> > > > restore to a point where I have only entered the first two records,
> > > > but all that seems to happen is that I am restored to the point of
the
> > > > first full backup.
> > > > I have attached my script (which is heavily based on the one you
> > > > originally posted).
> > > >
> > > > What am I doing wrong ?...it's been driving me mad for days !!
> > > >
> > > > All help gratefully received .
> > > >
> > > >
> > > > Kind Regards,
> > > >
> > > > Nigel
> > > >
> > > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:<#sdQMP4fDHA.2464@.TK2MSFTNGP09.phx.gbl>...
> > > > > Hi Ben.
> > > > >
> > > > > Are you sure you're on SQL 7.0? The term "full recovery" only came
along
> > > > > with SQL 2000...
> > > > >
> > > > > Anyway, this should be simple - restore the last good full backup
> > without
> > > > > recovering, then apply the transaction log & stopat the
appropriate
> > time.
> > > > > Below is a demo script that should work on both 7.0 & 2000,
whichever
> > you're
> > > > > using. Follow it carefully & you should be able to see that this
is
> > > > > possible. Note that the table t1 is populated with a row, which is
then
> > > > > deleted between the two full backups. By performing a normal full
> > database
> > > > > restore without recovery, then a log restore with recovery, you
can get
> > the
> > > > > point in time you're after.
> > > > >
> > > > > set nocount on
> > > > > go
> > > > > use master
> > > > > go
> > > > > create database a
> > > > > on (name=a_dat, filename='c:\a_dat.mdf', size=1mb, filegrowth=1mb)
> > > > > log on (name=a_log, filename='c:\a_log.ldf', size=1mb,
filegrowth=1mb )
> > > > > go
> > > > > use a
> > > > > go
> > > > > create table t1 (c1 int)
> > > > > create table t2 (restoretime varchar(26))
> > > > > go
> > > > > /* insert a row into t1. we'll expect to see this row again after
> > restore,
> > > > > despite it being deleted before full backup 2 */
> > > > > insert into t1 values (1)
> > > > > go
> > > > > use master
> > > > > go
> > > > > /* your full backup from whenever */
> > > > > backup database a to disk='c:\a_bak1.bak'
> > > > > go
> > > > > use a
> > > > > go
> > > > > /* insert a time into t2 that can be read as a stopat time accross
> > batches
> > > > > */
> > > > > insert into t2 values (convert(varchar(26), getdate(), 9))
> > > > > go
> > > > > /* delay one minute */
> > > > > waitfor delay '00:01:00'
> > > > > go
> > > > > /* delete the row from t1. This represents the mistake we want to
> > recover
> > > > > before.. */
> > > > > delete from t1
> > > > > go
> > > > > use master
> > > > > go
> > > > > /* your secondary, post mistake full backup */
> > > > > backup database a to disk='c:\a_bak2.bak'
> > > > > go
> > > > > /* your log backup */
> > > > > backup log a to disk='c:\a_bak3.bak'
> > > > > go
> > > > > /* we restore to point in time captured in t2. I'm only using t2
so we
> > could
> > > > > record that point in time accross batches for the purposes of this
> > example
> > > > > script */
> > > > > use a
> > > > > declare @.restoretime varchar(26)
> > > > > select @.restoretime = min(restoretime) from t2
> > > > > use master
> > > > > restore database a from disk='c:\a_bak1.bak' with norecovery
> > > > > restore log a from disk='c:\a_bak3.bak' with recovery, stopat => > @.restoretime
> > > > > go
> > > > > use a
> > > > > go
> > > > > /* prove that the deleted row from t1 is restored */
> > > > > select * from t1
> > > > > go
> > > > > /* clean up */
> > > > > use master
> > > > > go
> > > > > drop database a
> > > > > go
> > > > >
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Greg Linwood
> > > > > SQL Server MVP
> > > > >
> > > > > "ben brugman" <ben@.niethier.nl> wrote in message
> > > > > news:bkhf32$mlv$1@.reader10.wxs.nl...
> > > > > > Hello,
> > > > > >
> > > > > > We want to do a restore to a point of time.
> > > > > >
> > > > > > With recovery mode on full.
> > > > > > Can one do a restore to a point of time before the last full
backup
> > > > > > with a transaction log backup made after the full backup ?
> > > > > >
> > > > > > Sequence.
> > > > > >
> > > > > > (full recovery mode, SQL-server 7. I think.).
> > > > > > a. Somewere in the past a full backup is made.
> > > > > > b. An error is made.
> > > > > > c. A full backup is made.
> > > > > > d. A transaction log backup is made.
> > > > > >
> > > > > > We want to restore to a point in time just before
> > > > > > the error (b.) is made. Is this possible ?
> > > > > > (I have got the BOL (from 7) and inside from Kalen but
> > > > > > can not find the anwsers there).
> > > > > >
> > > > > > Ben Brugman.
> > > > > >
> > > > > >

backup (SQL2000)/restore (SQL2005) problem in details

Sorry, I have to give more detail about my question.
I have trouble with it. I have tried several ways and talked to some people
in the forum, but it is not working. The following is waht I did.
(1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
COMPLETE backup setup, owners: dbo. Security:Windows mode
and my login name in database: machine_name\my_login_name. OS: 2000server
(2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database with same name DB. Auto
generated the user list which is same as in original database server: dbo
(owner), guest, ASPNET and so on. My login name in new server machine,
displayed in security under the node databases: nwe_machine_name\new
loginname (has a space between two words).
(5)Now I add new user to the stored database DB:
Expand Security under DB.
Rigth click on Users and select new user. Dialog pop up.
Broswer Login Name: nwe_machine_name\new loginname
Type in user name: new
Browser default schema: db_owner
Select Database Role Menbership: db_owner
Click OK.
User new is added in the list.
(6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
work. When I click the tree node Database Diagrams, I get
the error massage. I can not either display the diagram or create new
diagram on the database. The error message:
"Database diagram support objects cannot be installed because this database
does not have valid owner.
To continue, first use the Files page of the Database Properties dialog box
ot the Alter Authorization statement
to set the database owner to a valid login, then add the datatbase diagram
support objects."
I can not either show the diagram or create a new diagram on this restored
database DB.
I do not know the detail steps for doing it. MSDN articles suggest that
For compatiable, run
exec sp_dbcmptlevel 'database_name', '90';
(It is OK for me to exec. it)
For alter authorization, run
ALTER AUTHORIZATION ON DATABASE::databasename to new
which produced:
"Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'new', because it does not exist or you do not
have permission."
I have trouble here. I do not know how to do it. Did I do something wrong?
What is the correct way/steps?
Are there any tricks in backup and restore process?
Thank you for any help.
David
Did you copy the user accounts and logins from SQL Server 2000 to 2005?
"david" <david@.discussions.microsoft.com> wrote in message
news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
> Sorry, I have to give more detail about my question.
> I have trouble with it. I have tried several ways and talked to some
> people
> in the forum, but it is not working. The following is waht I did.
> (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> COMPLETE backup setup, owners: dbo. Security:Windows mode
> and my login name in database: machine_name\my_login_name. OS: 2000server
> (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database with same name DB.
> Auto
> generated the user list which is same as in original database server: dbo
> (owner), guest, ASPNET and so on. My login name in new server machine,
> displayed in security under the node databases: nwe_machine_name\new
> loginname (has a space between two words).
> (5)Now I add new user to the stored database DB:
> Expand Security under DB.
> Rigth click on Users and select new user. Dialog pop up.
> Broswer Login Name: nwe_machine_name\new loginname
> Type in user name: new
> Browser default schema: db_owner
> Select Database Role Menbership: db_owner
> Click OK.
> User new is added in the list.
>
> (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> work. When I click the tree node Database Diagrams, I get
> the error massage. I can not either display the diagram or create new
> diagram on the database. The error message:
> "Database diagram support objects cannot be installed because this
> database
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog
> box
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I can not either show the diagram or create a new diagram on this restored
> database DB.
> I do not know the detail steps for doing it. MSDN articles suggest that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> (It is OK for me to exec. it)
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to new
> which produced:
> "Msg 15151, Level 16, State 1, Line 1
> Cannot find the principal 'new', because it does not exist or you do not
> have permission."
> I have trouble here. I do not know how to do it. Did I do something wrong?
> What is the correct way/steps?
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>
|||Hi David
Could you go to Microsoft SQL Server Management Studio and see under the
Security section of the SQL Server itself (not the database), and see if you
have any of the users in that one correspond/mapped to the database user?
My guess is that if you are only backup and restore the user database, it
will not carry the login information which is stored in system database.
Have you tried to log on as an sa (if it is on mixed mode) and tried to do
the things that you wanted to do?
Lucas
"david" wrote:
[vbcol=seagreen]
> NO, I did not copy user accounts and logins from SQL Server 2000.
> When I made a backup with COMPLETE option, the backup carry all information
> about the database. When I restored the database, all accounts and sttatus
> for old machine automatically appear in the user list for the restored
> database under SQL Server 2005 on new machine.
> David
>
> "bass_player [SBS-MVP]" wrote:

backup (SQL2000)/restore (SQL2005) problem in details

Sorry, I have to give more detail about my question.
I have trouble with it. I have tried several ways and talked to some people
in the forum, but it is not working. The following is waht I did.
(1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
COMPLETE backup setup, owners: dbo. Security:Windows mode
and my login name in database: machine_name\my_login_name. OS: 2000server
(2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database with same name DB. Auto
generated the user list which is same as in original database server: dbo
(owner), guest, ASPNET and so on. My login name in new server machine,
displayed in security under the node databases: nwe_machine_name\new
loginname (has a space between two words).
(5)Now I add new user to the stored database DB:
Expand Security under DB.
Rigth click on Users and select new user. Dialog pop up.
Broswer Login Name: nwe_machine_name\new loginname
Type in user name: new
Browser default schema: db_owner
Select Database Role Menbership: db_owner
Click OK.
User new is added in the list.
(6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
work. When I click the tree node Database Diagrams, I get
the error massage. I can not either display the diagram or create new
diagram on the database. The error message:
"Database diagram support objects cannot be installed because this database
does not have valid owner.
To continue, first use the Files page of the Database Properties dialog box
ot the Alter Authorization statement
to set the database owner to a valid login, then add the datatbase diagram
support objects."
I can not either show the diagram or create a new diagram on this restored
database DB.
I do not know the detail steps for doing it. MSDN articles suggest that
For compatiable, run
exec sp_dbcmptlevel 'database_name', '90';
(It is OK for me to exec. it)
For alter authorization, run
ALTER AUTHORIZATION ON DATABASE::databasename to new
which produced:
"Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'new', because it does not exist or you do not
have permission."
I have trouble here. I do not know how to do it. Did I do something wrong?
What is the correct way/steps?
Are there any tricks in backup and restore process?
Thank you for any help.
DavidDid you copy the user accounts and logins from SQL Server 2000 to 2005?
"david" <david@.discussions.microsoft.com> wrote in message
news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
> Sorry, I have to give more detail about my question.
> I have trouble with it. I have tried several ways and talked to some
> people
> in the forum, but it is not working. The following is waht I did.
> (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> COMPLETE backup setup, owners: dbo. Security:Windows mode
> and my login name in database: machine_name\my_login_name. OS: 2000server
> (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database with same name DB.
> Auto
> generated the user list which is same as in original database server: dbo
> (owner), guest, ASPNET and so on. My login name in new server machine,
> displayed in security under the node databases: nwe_machine_name\new
> loginname (has a space between two words).
> (5)Now I add new user to the stored database DB:
> Expand Security under DB.
> Rigth click on Users and select new user. Dialog pop up.
> Broswer Login Name: nwe_machine_name\new loginname
> Type in user name: new
> Browser default schema: db_owner
> Select Database Role Menbership: db_owner
> Click OK.
> User new is added in the list.
>
> (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> work. When I click the tree node Database Diagrams, I get
> the error massage. I can not either display the diagram or create new
> diagram on the database. The error message:
> "Database diagram support objects cannot be installed because this
> database
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog
> box
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I can not either show the diagram or create a new diagram on this restored
> database DB.
> I do not know the detail steps for doing it. MSDN articles suggest that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> (It is OK for me to exec. it)
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to new
> which produced:
> "Msg 15151, Level 16, State 1, Line 1
> Cannot find the principal 'new', because it does not exist or you do not
> have permission."
> I have trouble here. I do not know how to do it. Did I do something wrong?
> What is the correct way/steps?
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>|||NO, I did not copy user accounts and logins from SQL Server 2000.
When I made a backup with COMPLETE option, the backup carry all information
about the database. When I restored the database, all accounts and sttatus
for old machine automatically appear in the user list for the restored
database under SQL Server 2005 on new machine.
David
"bass_player [SBS-MVP]" wrote:
> Did you copy the user accounts and logins from SQL Server 2000 to 2005?
> "david" <david@.discussions.microsoft.com> wrote in message
> news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
> > Sorry, I have to give more detail about my question.
> > I have trouble with it. I have tried several ways and talked to some
> > people
> > in the forum, but it is not working. The following is waht I did.
> >
> > (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> > COMPLETE backup setup, owners: dbo. Security:Windows mode
> > and my login name in database: machine_name\my_login_name. OS: 2000server
> > (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> > (3) Start SQL Server Management Studio of SQL Server 2005.
> > (4) Use Restore to restore the backup to a database with same name DB.
> > Auto
> > generated the user list which is same as in original database server: dbo
> > (owner), guest, ASPNET and so on. My login name in new server machine,
> > displayed in security under the node databases: nwe_machine_name\new
> > loginname (has a space between two words).
> > (5)Now I add new user to the stored database DB:
> > Expand Security under DB.
> > Rigth click on Users and select new user. Dialog pop up.
> > Broswer Login Name: nwe_machine_name\new loginname
> > Type in user name: new
> > Browser default schema: db_owner
> > Select Database Role Menbership: db_owner
> > Click OK.
> >
> > User new is added in the list.
> >
> >
> >
> > (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> > work. When I click the tree node Database Diagrams, I get
> > the error massage. I can not either display the diagram or create new
> > diagram on the database. The error message:
> > "Database diagram support objects cannot be installed because this
> > database
> > does not have valid owner.
> > To continue, first use the Files page of the Database Properties dialog
> > box
> > ot the Alter Authorization statement
> > to set the database owner to a valid login, then add the datatbase diagram
> > support objects."
> >
> > I can not either show the diagram or create a new diagram on this restored
> > database DB.
> >
> > I do not know the detail steps for doing it. MSDN articles suggest that
> > For compatiable, run
> > exec sp_dbcmptlevel 'database_name', '90';
> > (It is OK for me to exec. it)
> >
> > For alter authorization, run
> > ALTER AUTHORIZATION ON DATABASE::databasename to new
> > which produced:
> > "Msg 15151, Level 16, State 1, Line 1
> > Cannot find the principal 'new', because it does not exist or you do not
> > have permission."
> >
> > I have trouble here. I do not know how to do it. Did I do something wrong?
> > What is the correct way/steps?
> > Are there any tricks in backup and restore process?
> >
> > Thank you for any help.
> >
> > David
> >
>
>|||Hi David
Could you go to Microsoft SQL Server Management Studio and see under the
Security section of the SQL Server itself (not the database), and see if you
have any of the users in that one correspond/mapped to the database user?
My guess is that if you are only backup and restore the user database, it
will not carry the login information which is stored in system database.
Have you tried to log on as an sa (if it is on mixed mode) and tried to do
the things that you wanted to do?
Lucas
"david" wrote:
> NO, I did not copy user accounts and logins from SQL Server 2000.
> When I made a backup with COMPLETE option, the backup carry all information
> about the database. When I restored the database, all accounts and sttatus
> for old machine automatically appear in the user list for the restored
> database under SQL Server 2005 on new machine.
> David
>
> "bass_player [SBS-MVP]" wrote:
> > Did you copy the user accounts and logins from SQL Server 2000 to 2005?
> >
> > "david" <david@.discussions.microsoft.com> wrote in message
> > news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
> > > Sorry, I have to give more detail about my question.
> > > I have trouble with it. I have tried several ways and talked to some
> > > people
> > > in the forum, but it is not working. The following is waht I did.
> > >
> > > (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> > > COMPLETE backup setup, owners: dbo. Security:Windows mode
> > > and my login name in database: machine_name\my_login_name. OS: 2000server
> > > (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> > > (3) Start SQL Server Management Studio of SQL Server 2005.
> > > (4) Use Restore to restore the backup to a database with same name DB.
> > > Auto
> > > generated the user list which is same as in original database server: dbo
> > > (owner), guest, ASPNET and so on. My login name in new server machine,
> > > displayed in security under the node databases: nwe_machine_name\new
> > > loginname (has a space between two words).
> > > (5)Now I add new user to the stored database DB:
> > > Expand Security under DB.
> > > Rigth click on Users and select new user. Dialog pop up.
> > > Broswer Login Name: nwe_machine_name\new loginname
> > > Type in user name: new
> > > Browser default schema: db_owner
> > > Select Database Role Menbership: db_owner
> > > Click OK.
> > >
> > > User new is added in the list.
> > >
> > >
> > >
> > > (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> > > work. When I click the tree node Database Diagrams, I get
> > > the error massage. I can not either display the diagram or create new
> > > diagram on the database. The error message:
> > > "Database diagram support objects cannot be installed because this
> > > database
> > > does not have valid owner.
> > > To continue, first use the Files page of the Database Properties dialog
> > > box
> > > ot the Alter Authorization statement
> > > to set the database owner to a valid login, then add the datatbase diagram
> > > support objects."
> > >
> > > I can not either show the diagram or create a new diagram on this restored
> > > database DB.
> > >
> > > I do not know the detail steps for doing it. MSDN articles suggest that
> > > For compatiable, run
> > > exec sp_dbcmptlevel 'database_name', '90';
> > > (It is OK for me to exec. it)
> > >
> > > For alter authorization, run
> > > ALTER AUTHORIZATION ON DATABASE::databasename to new
> > > which produced:
> > > "Msg 15151, Level 16, State 1, Line 1
> > > Cannot find the principal 'new', because it does not exist or you do not
> > > have permission."
> > >
> > > I have trouble here. I do not know how to do it. Did I do something wrong?
> > > What is the correct way/steps?
> > > Are there any tricks in backup and restore process?
> > >
> > > Thank you for any help.
> > >
> > > David
> > >
> >
> >
> >|||Thank you.
Could you tell me the correct way for backup the database in SQL Server 2000?
Dabin
"Lucas Kartawidjaja" wrote:
> Hi David
> Could you go to Microsoft SQL Server Management Studio and see under the
> Security section of the SQL Server itself (not the database), and see if you
> have any of the users in that one correspond/mapped to the database user?
> My guess is that if you are only backup and restore the user database, it
> will not carry the login information which is stored in system database.
> Have you tried to log on as an sa (if it is on mixed mode) and tried to do
> the things that you wanted to do?
> Lucas
> "david" wrote:
> > NO, I did not copy user accounts and logins from SQL Server 2000.
> > When I made a backup with COMPLETE option, the backup carry all information
> > about the database. When I restored the database, all accounts and sttatus
> > for old machine automatically appear in the user list for the restored
> > database under SQL Server 2005 on new machine.
> >
> > David
> >
> >
> > "bass_player [SBS-MVP]" wrote:
> >
> > > Did you copy the user accounts and logins from SQL Server 2000 to 2005?
> > >
> > > "david" <david@.discussions.microsoft.com> wrote in message
> > > news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
> > > > Sorry, I have to give more detail about my question.
> > > > I have trouble with it. I have tried several ways and talked to some
> > > > people
> > > > in the forum, but it is not working. The following is waht I did.
> > > >
> > > > (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> > > > COMPLETE backup setup, owners: dbo. Security:Windows mode
> > > > and my login name in database: machine_name\my_login_name. OS: 2000server
> > > > (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> > > > (3) Start SQL Server Management Studio of SQL Server 2005.
> > > > (4) Use Restore to restore the backup to a database with same name DB.
> > > > Auto
> > > > generated the user list which is same as in original database server: dbo
> > > > (owner), guest, ASPNET and so on. My login name in new server machine,
> > > > displayed in security under the node databases: nwe_machine_name\new
> > > > loginname (has a space between two words).
> > > > (5)Now I add new user to the stored database DB:
> > > > Expand Security under DB.
> > > > Rigth click on Users and select new user. Dialog pop up.
> > > > Broswer Login Name: nwe_machine_name\new loginname
> > > > Type in user name: new
> > > > Browser default schema: db_owner
> > > > Select Database Role Menbership: db_owner
> > > > Click OK.
> > > >
> > > > User new is added in the list.
> > > >
> > > >
> > > >
> > > > (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> > > > work. When I click the tree node Database Diagrams, I get
> > > > the error massage. I can not either display the diagram or create new
> > > > diagram on the database. The error message:
> > > > "Database diagram support objects cannot be installed because this
> > > > database
> > > > does not have valid owner.
> > > > To continue, first use the Files page of the Database Properties dialog
> > > > box
> > > > ot the Alter Authorization statement
> > > > to set the database owner to a valid login, then add the datatbase diagram
> > > > support objects."
> > > >
> > > > I can not either show the diagram or create a new diagram on this restored
> > > > database DB.
> > > >
> > > > I do not know the detail steps for doing it. MSDN articles suggest that
> > > > For compatiable, run
> > > > exec sp_dbcmptlevel 'database_name', '90';
> > > > (It is OK for me to exec. it)
> > > >
> > > > For alter authorization, run
> > > > ALTER AUTHORIZATION ON DATABASE::databasename to new
> > > > which produced:
> > > > "Msg 15151, Level 16, State 1, Line 1
> > > > Cannot find the principal 'new', because it does not exist or you do not
> > > > have permission."
> > > >
> > > > I have trouble here. I do not know how to do it. Did I do something wrong?
> > > > What is the correct way/steps?
> > > > Are there any tricks in backup and restore process?
> > > >
> > > > Thank you for any help.
> > > >
> > > > David
> > > >
> > >
> > >
> > >|||Sorry, some mistake in my description.
All users under databases should be under security of SQL Server. Restate as
follows:
(1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
COMPLETE backup setup, owners: dbo. Security:Windows mode
and my login name in security of server: machine_name\my_login_name. OS: Win
2000 server
(2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database with same database name
DB. It Automatically
generated the user list (under DB) which is same as under DB in original
database server: dbo
(owner), guest, ASPNET and so on. My login name in new server machine,
displayed in security under the SQL SERVER: nwe_machine_name\new
loginname (has a space between two words).
(5)Now I add new user to the stored database DB:
Expand Security under DB.
Rigth click on Users and select new user. Dialog pop up.
Broswer Login Name: nwe_machine_name\new loginname
Type in user name: new
Browser default schema: db_owner
Select Database Role Menbership: db_owner
Click OK.
User new is added in the list.
(6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
work. When I click the tree node Database Diagrams, I get
the error massage. I can not either display the diagram or create new
diagram on the database. The error message:
"Database diagram support objects cannot be installed because this database
does not have valid owner.
To continue, first use the Files page of the Database Properties dialog box
ot the Alter Authorization statement
to set the database owner to a valid login, then add the datatbase diagram
support objects."
"Lucas Kartawidjaja" wrote:
> Hi David
> Could you go to Microsoft SQL Server Management Studio and see under the
> Security section of the SQL Server itself (not the database), and see if you
> have any of the users in that one correspond/mapped to the database user?
> My guess is that if you are only backup and restore the user database, it
> will not carry the login information which is stored in system database.
> Have you tried to log on as an sa (if it is on mixed mode) and tried to do
> the things that you wanted to do?
> Lucas
> "david" wrote:
> > NO, I did not copy user accounts and logins from SQL Server 2000.
> > When I made a backup with COMPLETE option, the backup carry all information
> > about the database. When I restored the database, all accounts and sttatus
> > for old machine automatically appear in the user list for the restored
> > database under SQL Server 2005 on new machine.
> >
> > David
> >
> >
> > "bass_player [SBS-MVP]" wrote:
> >
> > > Did you copy the user accounts and logins from SQL Server 2000 to 2005?
> > >
> > > "david" <david@.discussions.microsoft.com> wrote in message
> > > news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
> > > > Sorry, I have to give more detail about my question.
> > > > I have trouble with it. I have tried several ways and talked to some
> > > > people
> > > > in the forum, but it is not working. The following is waht I did.
> > > >
> > > > (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> > > > COMPLETE backup setup, owners: dbo. Security:Windows mode
> > > > and my login name in database: machine_name\my_login_name. OS: 2000server
> > > > (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> > > > (3) Start SQL Server Management Studio of SQL Server 2005.
> > > > (4) Use Restore to restore the backup to a database with same name DB.
> > > > Auto
> > > > generated the user list which is same as in original database server: dbo
> > > > (owner), guest, ASPNET and so on. My login name in new server machine,
> > > > displayed in security under the node databases: nwe_machine_name\new
> > > > loginname (has a space between two words).
> > > > (5)Now I add new user to the stored database DB:
> > > > Expand Security under DB.
> > > > Rigth click on Users and select new user. Dialog pop up.
> > > > Broswer Login Name: nwe_machine_name\new loginname
> > > > Type in user name: new
> > > > Browser default schema: db_owner
> > > > Select Database Role Menbership: db_owner
> > > > Click OK.
> > > >
> > > > User new is added in the list.
> > > >
> > > >
> > > >
> > > > (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> > > > work. When I click the tree node Database Diagrams, I get
> > > > the error massage. I can not either display the diagram or create new
> > > > diagram on the database. The error message:
> > > > "Database diagram support objects cannot be installed because this
> > > > database
> > > > does not have valid owner.
> > > > To continue, first use the Files page of the Database Properties dialog
> > > > box
> > > > ot the Alter Authorization statement
> > > > to set the database owner to a valid login, then add the datatbase diagram
> > > > support objects."
> > > >
> > > > I can not either show the diagram or create a new diagram on this restored
> > > > database DB.
> > > >
> > > > I do not know the detail steps for doing it. MSDN articles suggest that
> > > > For compatiable, run
> > > > exec sp_dbcmptlevel 'database_name', '90';
> > > > (It is OK for me to exec. it)
> > > >
> > > > For alter authorization, run
> > > > ALTER AUTHORIZATION ON DATABASE::databasename to new
> > > > which produced:
> > > > "Msg 15151, Level 16, State 1, Line 1
> > > > Cannot find the principal 'new', because it does not exist or you do not
> > > > have permission."
> > > >
> > > > I have trouble here. I do not know how to do it. Did I do something wrong?
> > > > What is the correct way/steps?
> > > > Are there any tricks in backup and restore process?
> > > >
> > > > Thank you for any help.
> > > >
> > > > David
> > > >
> > >
> > >
> > >|||Thank you, Player and Lucas:
It works now. Valid_login = "my machine\my login name" which is under sql
server\security\logins
David
"david" wrote:
> Sorry, I have to give more detail about my question.
> I have trouble with it. I have tried several ways and talked to some people
> in the forum, but it is not working. The following is waht I did.
> (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> COMPLETE backup setup, owners: dbo. Security:Windows mode
> and my login name in database: machine_name\my_login_name. OS: 2000server
> (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database with same name DB. Auto
> generated the user list which is same as in original database server: dbo
> (owner), guest, ASPNET and so on. My login name in new server machine,
> displayed in security under the node databases: nwe_machine_name\new
> loginname (has a space between two words).
> (5)Now I add new user to the stored database DB:
> Expand Security under DB.
> Rigth click on Users and select new user. Dialog pop up.
> Broswer Login Name: nwe_machine_name\new loginname
> Type in user name: new
> Browser default schema: db_owner
> Select Database Role Menbership: db_owner
> Click OK.
> User new is added in the list.
>
> (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> work. When I click the tree node Database Diagrams, I get
> the error massage. I can not either display the diagram or create new
> diagram on the database. The error message:
> "Database diagram support objects cannot be installed because this database
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog box
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I can not either show the diagram or create a new diagram on this restored
> database DB.
> I do not know the detail steps for doing it. MSDN articles suggest that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> (It is OK for me to exec. it)
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to new
> which produced:
> "Msg 15151, Level 16, State 1, Line 1
> Cannot find the principal 'new', because it does not exist or you do not
> have permission."
> I have trouble here. I do not know how to do it. Did I do something wrong?
> What is the correct way/steps?
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>

backup (SQL2000)/restore (SQL2005) problem in details

Sorry, I have to give more detail about my question.
I have trouble with it. I have tried several ways and talked to some people
in the forum, but it is not working. The following is waht I did.
(1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
COMPLETE backup setup, owners: dbo. Security:Windows mode
and my login name in database: machine_name\my_login_name. OS: 2000server
(2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database with same name DB. Auto
generated the user list which is same as in original database server: dbo
(owner), guest, ASPNET and so on. My login name in new server machine,
displayed in security under the node databases: nwe_machine_name\new
loginname (has a space between two words).
(5)Now I add new user to the stored database DB:
Expand Security under DB.
Rigth click on Users and select new user. Dialog pop up.
Broswer Login Name: nwe_machine_name\new loginname
Type in user name: new
Browser default schema: db_owner
Select Database Role Menbership: db_owner
Click OK.
User new is added in the list.
(6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
work. When I click the tree node Database Diagrams, I get
the error massage. I can not either display the diagram or create new
diagram on the database. The error message:
"Database diagram support objects cannot be installed because this database
does not have valid owner.
To continue, first use the Files page of the Database Properties dialog box
ot the Alter Authorization statement
to set the database owner to a valid login, then add the datatbase diagram
support objects."
I can not either show the diagram or create a new diagram on this restored
database DB.
I do not know the detail steps for doing it. MSDN articles suggest that
For compatiable, run
exec sp_dbcmptlevel 'database_name', '90';
(It is OK for me to exec. it)
For alter authorization, run
ALTER AUTHORIZATION ON DATABASE::databasename to new
which produced:
"Msg 15151, Level 16, State 1, Line 1
Cannot find the principal 'new', because it does not exist or you do not
have permission."
I have trouble here. I do not know how to do it. Did I do something wrong?
What is the correct way/steps?
Are there any tricks in backup and restore process?
Thank you for any help.
DavidDid you copy the user accounts and logins from SQL Server 2000 to 2005?
"david" <david@.discussions.microsoft.com> wrote in message
news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
> Sorry, I have to give more detail about my question.
> I have trouble with it. I have tried several ways and talked to some
> people
> in the forum, but it is not working. The following is waht I did.
> (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> COMPLETE backup setup, owners: dbo. Security:Windows mode
> and my login name in database: machine_name\my_login_name. OS: 2000server
> (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database with same name DB.
> Auto
> generated the user list which is same as in original database server: dbo
> (owner), guest, ASPNET and so on. My login name in new server machine,
> displayed in security under the node databases: nwe_machine_name\new
> loginname (has a space between two words).
> (5)Now I add new user to the stored database DB:
> Expand Security under DB.
> Rigth click on Users and select new user. Dialog pop up.
> Broswer Login Name: nwe_machine_name\new loginname
> Type in user name: new
> Browser default schema: db_owner
> Select Database Role Menbership: db_owner
> Click OK.
> User new is added in the list.
>
> (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> work. When I click the tree node Database Diagrams, I get
> the error massage. I can not either display the diagram or create new
> diagram on the database. The error message:
> "Database diagram support objects cannot be installed because this
> database
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog
> box
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I can not either show the diagram or create a new diagram on this restored
> database DB.
> I do not know the detail steps for doing it. MSDN articles suggest that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> (It is OK for me to exec. it)
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to new
> which produced:
> "Msg 15151, Level 16, State 1, Line 1
> Cannot find the principal 'new', because it does not exist or you do not
> have permission."
> I have trouble here. I do not know how to do it. Did I do something wrong?
> What is the correct way/steps?
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>|||NO, I did not copy user accounts and logins from SQL Server 2000.
When I made a backup with COMPLETE option, the backup carry all information
about the database. When I restored the database, all accounts and sttatus
for old machine automatically appear in the user list for the restored
database under SQL Server 2005 on new machine.
David
"bass_player [SBS-MVP]" wrote:

> Did you copy the user accounts and logins from SQL Server 2000 to 2005?
> "david" <david@.discussions.microsoft.com> wrote in message
> news:E45E94E2-C4CF-4488-BF6D-D048A4C6F175@.microsoft.com...
>
>|||Hi David
Could you go to Microsoft SQL Server Management Studio and see under the
Security section of the SQL Server itself (not the database), and see if you
have any of the users in that one correspond/mapped to the database user?
My guess is that if you are only backup and restore the user database, it
will not carry the login information which is stored in system database.
Have you tried to log on as an sa (if it is on mixed mode) and tried to do
the things that you wanted to do?
Lucas
"david" wrote:
[vbcol=seagreen]
> NO, I did not copy user accounts and logins from SQL Server 2000.
> When I made a backup with COMPLETE option, the backup carry all informatio
n
> about the database. When I restored the database, all accounts and sttatus
> for old machine automatically appear in the user list for the restored
> database under SQL Server 2005 on new machine.
> David
>
> "bass_player [SBS-MVP]" wrote:
>|||Thank you.
Could you tell me the correct way for backup the database in SQL Server 2000
?
Dabin
"Lucas Kartawidjaja" wrote:
[vbcol=seagreen]
> Hi David
> Could you go to Microsoft SQL Server Management Studio and see under the
> Security section of the SQL Server itself (not the database), and see if y
ou
> have any of the users in that one correspond/mapped to the database user?
> My guess is that if you are only backup and restore the user database, it
> will not carry the login information which is stored in system database.
> Have you tried to log on as an sa (if it is on mixed mode) and tried to do
> the things that you wanted to do?
> Lucas
> "david" wrote:
>|||Sorry, some mistake in my description.
All users under databases should be under security of SQL Server. Restate as
follows:
(1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
COMPLETE backup setup, owners: dbo. Security:Windows mode
and my login name in security of server: machine_name\my_login_name. OS: Win
2000 server
(2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
(3) Start SQL Server Management Studio of SQL Server 2005.
(4) Use Restore to restore the backup to a database with same database name
DB. It Automatically
generated the user list (under DB) which is same as under DB in original
database server: dbo
(owner), guest, ASPNET and so on. My login name in new server machine,
displayed in security under the SQL SERVER: nwe_machine_name\new
loginname (has a space between two words).
(5)Now I add new user to the stored database DB:
Expand Security under DB.
Rigth click on Users and select new user. Dialog pop up.
Broswer Login Name: nwe_machine_name\new loginname
Type in user name: new
Browser default schema: db_owner
Select Database Role Menbership: db_owner
Click OK.
User new is added in the list.
(6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
work. When I click the tree node Database Diagrams, I get
the error massage. I can not either display the diagram or create new
diagram on the database. The error message:
"Database diagram support objects cannot be installed because this database
does not have valid owner.
To continue, first use the Files page of the Database Properties dialog box
ot the Alter Authorization statement
to set the database owner to a valid login, then add the datatbase diagram
support objects."
"Lucas Kartawidjaja" wrote:
[vbcol=seagreen]
> Hi David
> Could you go to Microsoft SQL Server Management Studio and see under the
> Security section of the SQL Server itself (not the database), and see if y
ou
> have any of the users in that one correspond/mapped to the database user?
> My guess is that if you are only backup and restore the user database, it
> will not carry the login information which is stored in system database.
> Have you tried to log on as an sa (if it is on mixed mode) and tried to do
> the things that you wanted to do?
> Lucas
> "david" wrote:
>|||Thank you, Player and Lucas:
It works now. Valid_login = "my machine\my login name" which is under sql
server\security\logins
David
"david" wrote:

> Sorry, I have to give more detail about my question.
> I have trouble with it. I have tried several ways and talked to some peopl
e
> in the forum, but it is not working. The following is waht I did.
> (1) Backup, say DB.BAk from SQL Server 2000: use Enterprise Manager with
> COMPLETE backup setup, owners: dbo. Security:Windows mode
> and my login name in database: machine_name\my_login_name. OS: 2000server
> (2) Copy backup file to another machine with SQL Server 2005. OS: XP pro
> (3) Start SQL Server Management Studio of SQL Server 2005.
> (4) Use Restore to restore the backup to a database with same name DB. Aut
o
> generated the user list which is same as in original database server: dbo
> (owner), guest, ASPNET and so on. My login name in new server machine,
> displayed in security under the node databases: nwe_machine_name\new
> loginname (has a space between two words).
> (5)Now I add new user to the stored database DB:
> Expand Security under DB.
> Rigth click on Users and select new user. Dialog pop up.
> Broswer Login Name: nwe_machine_name\new loginname
> Type in user name: new
> Browser default schema: db_owner
> Select Database Role Menbership: db_owner
> Click OK.
> User new is added in the list.
>
> (6) Tables, Viewers, and SPs are OK. But Database Diagrams need to
> work. When I click the tree node Database Diagrams, I get
> the error massage. I can not either display the diagram or create new
> diagram on the database. The error message:
> "Database diagram support objects cannot be installed because this databas
e
> does not have valid owner.
> To continue, first use the Files page of the Database Properties dialog bo
x
> ot the Alter Authorization statement
> to set the database owner to a valid login, then add the datatbase diagram
> support objects."
> I can not either show the diagram or create a new diagram on this restored
> database DB.
> I do not know the detail steps for doing it. MSDN articles suggest that
> For compatiable, run
> exec sp_dbcmptlevel 'database_name', '90';
> (It is OK for me to exec. it)
> For alter authorization, run
> ALTER AUTHORIZATION ON DATABASE::databasename to new
> which produced:
> "Msg 15151, Level 16, State 1, Line 1
> Cannot find the principal 'new', because it does not exist or you do not
> have permission."
> I have trouble here. I do not know how to do it. Did I do something wrong?
> What is the correct way/steps?
> Are there any tricks in backup and restore process?
> Thank you for any help.
> David
>