backup again

Dear All,
does sql2005(std.) have any tools for backup whole database to file for
other Backup software backup?
thx,
William
William
Can you elaborate a little bit? What are yopu trying to achive?
"William Cheung" <chun_hei@.msn.com> wrote in message
news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
> Dear All,
> does sql2005(std.) have any tools for backup whole database to file for
> other Backup software backup?
> thx,
> William
>
|||Dear,
i want to backup the SQL Server while without advance backup software(CA,
symantec...).
reg,
William
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
> William
> Can you elaborate a little bit? What are yopu trying to achive?
> "William Cheung" <chun_hei@.msn.com> wrote in message
> news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||Hi
See ,BACKUP DATABASE command in the BOL
"William Cheung" <chun_hei@.msn.com> wrote in message
news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear,
> i want to backup the SQL Server while without advance backup software(CA,
> symantec...).
> reg,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
>
|||With SQL 2005 Standard Edition, you can create a "Maintenance Job" to backup
on schedule, or you can create a SQL Agent Job to execute a SQL Backup
command.
SQL Backup is quite robust, and you may be OK without any other software.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
> Dear All,
> does sql2005(std.) have any tools for backup whole database to file for
> other Backup software backup?
> thx,
> William
>
|||Hi,
can i be schedule run? how to restore? can you pls send me some reference
for futher study.
thx,
William
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Hi
>
> See ,BACKUP DATABASE command in the BOL
>
>
> "William Cheung" <chun_hei@.msn.com> wrote in message
> news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>
|||Hi
> can i be schedule run?
Sure, create a job with BACKUP DATABASE... command and schedule it
? how to restore?
Take a look at RESTORE DATABASE ...command in the BOL
?can you pls send me some reference
> for futher study.
Start with BOL (Books On Line ) which ships with SQL Server
"William Cheung" <chun_hei@.msn.com> wrote in message
news:OEfjMPkAHHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> can i be schedule run? how to restore? can you pls send me some reference
> for futher study.
> thx,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
>
|||Hi
you have to create backup script first and schedule it through sql job.
Schedule depends on your requirements and how much data you can afford to
lose
Here is the very basic script
full backup
BACKUP DATABASE [myDB] TO DISK=N'c:\myDBbackup.bak' WITH
NOINIT,Name='myDBbackup'
differential
BACKUP DATABASE [myDB] TO DISK=N'c:\myDBbackup.bak' WITH
NOINIT,DIFFERENTIAL,Name='myDBbackup'
log
BACKUP LOG [myDB] TO DISK = N'c:\myDBbackup.bak' WITH NOINIT , NOUNLOAD ,
NAME = 'myDBbackup', NOSKIP , STATS = 10, NOFORMAT
You need to understand how restore works... BOL is the best tool..
You will get some custom script from the following site
www.sqlservercentral.com
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www.databasejournal.com/features/mssql/article.php/3389831
vinu
"William Cheung" <chun_hei@.msn.com> wrote in message
news:OEfjMPkAHHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> can i be schedule run? how to restore? can you pls send me some reference
> for futher study.
> thx,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
>
|||William,
You don't need to purchase the third party open file, SQL
agents to do SQL Server backups. They aren't used that often
and are often problematic - it's more common to just use
SQL Server native backups and backup to disk. Enterprise
backup software or tape backups can then just backup the SQL
Server backup file that you create with the SQL Server
native backup. As others have pointed out, you just use
T-SQL backup commands, you can schedule these using SQL
Server Agent.
-Sue
On Tue, 7 Nov 2006 15:16:17 +0800, "William Cheung"
<chun_hei@.msn.com> wrote:

>Dear All,
>does sql2005(std.) have any tools for backup whole database to file for
>other Backup software backup?
>thx,
>William
>

backup again

Dear All,
does sql2005(std.) have any tools for backup whole database to file for
other Backup software backup?
thx,
William
William
Can you elaborate a little bit? What are yopu trying to achive?
"William Cheung" <chun_hei@.msn.com> wrote in message
news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
> Dear All,
> does sql2005(std.) have any tools for backup whole database to file for
> other Backup software backup?
> thx,
> William
>
|||Dear,
i want to backup the SQL Server while without advance backup software(CA,
symantec...).
reg,
William
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
> William
> Can you elaborate a little bit? What are yopu trying to achive?
> "William Cheung" <chun_hei@.msn.com> wrote in message
> news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||Hi
See ,BACKUP DATABASE command in the BOL
"William Cheung" <chun_hei@.msn.com> wrote in message
news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear,
> i want to backup the SQL Server while without advance backup software(CA,
> symantec...).
> reg,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
>
|||With SQL 2005 Standard Edition, you can create a "Maintenance Job" to backup
on schedule, or you can create a SQL Agent Job to execute a SQL Backup
command.
SQL Backup is quite robust, and you may be OK without any other software.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
> Dear All,
> does sql2005(std.) have any tools for backup whole database to file for
> other Backup software backup?
> thx,
> William
>
|||Hi,
can i be schedule run? how to restore? can you pls send me some reference
for futher study.
thx,
William
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Hi
>
> See ,BACKUP DATABASE command in the BOL
>
>
> "William Cheung" <chun_hei@.msn.com> wrote in message
> news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>
|||Hi
> can i be schedule run?
Sure, create a job with BACKUP DATABASE... command and schedule it
? how to restore?
Take a look at RESTORE DATABASE ...command in the BOL
?can you pls send me some reference
> for futher study.
Start with BOL (Books On Line ) which ships with SQL Server
"William Cheung" <chun_hei@.msn.com> wrote in message
news:OEfjMPkAHHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> can i be schedule run? how to restore? can you pls send me some reference
> for futher study.
> thx,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
>
|||Hi
you have to create backup script first and schedule it through sql job.
Schedule depends on your requirements and how much data you can afford to
lose
Here is the very basic script
full backup
BACKUP DATABASE [myDB] TO DISK=N'c:\myDBbackup.bak' WITH
NOINIT,Name='myDBbackup'
differential
BACKUP DATABASE [myDB] TO DISK=N'c:\myDBbackup.bak' WITH
NOINIT,DIFFERENTIAL,Name='myDBbackup'
log
BACKUP LOG [myDB] TO DISK = N'c:\myDBbackup.bak' WITH NOINIT , NOUNLOAD ,
NAME = 'myDBbackup', NOSKIP , STATS = 10, NOFORMAT
You need to understand how restore works... BOL is the best tool..
You will get some custom script from the following site
www.sqlservercentral.com
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www.databasejournal.com/features/mssql/article.php/3389831
vinu
"William Cheung" <chun_hei@.msn.com> wrote in message
news:OEfjMPkAHHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> can i be schedule run? how to restore? can you pls send me some reference
> for futher study.
> thx,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
>
|||William,
You don't need to purchase the third party open file, SQL
agents to do SQL Server backups. They aren't used that often
and are often problematic - it's more common to just use
SQL Server native backups and backup to disk. Enterprise
backup software or tape backups can then just backup the SQL
Server backup file that you create with the SQL Server
native backup. As others have pointed out, you just use
T-SQL backup commands, you can schedule these using SQL
Server Agent.
-Sue
On Tue, 7 Nov 2006 15:16:17 +0800, "William Cheung"
<chun_hei@.msn.com> wrote:

>Dear All,
>does sql2005(std.) have any tools for backup whole database to file for
>other Backup software backup?
>thx,
>William
>

backup again

Dear All,
does sql2005(std.) have any tools for backup whole database to file for
other Backup software backup?
thx,
WilliamWilliam
Can you elaborate a little bit? What are yopu trying to achive?
"William Cheung" <chun_hei@.msn.com> wrote in message
news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
> Dear All,
> does sql2005(std.) have any tools for backup whole database to file for
> other Backup software backup?
> thx,
> William
>|||Dear,
i want to backup the SQL Server while without advance backup software(CA,
symantec...).
reg,
William
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
> William
> Can you elaborate a little bit? What are yopu trying to achive?
> "William Cheung" <chun_hei@.msn.com> wrote in message
> news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>> Dear All,
>> does sql2005(std.) have any tools for backup whole database to file for
>> other Backup software backup?
>> thx,
>> William
>|||Hi
See ,BACKUP DATABASE command in the BOL
"William Cheung" <chun_hei@.msn.com> wrote in message
news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear,
> i want to backup the SQL Server while without advance backup software(CA,
> symantec...).
> reg,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
>> William
>> Can you elaborate a little bit? What are yopu trying to achive?
>> "William Cheung" <chun_hei@.msn.com> wrote in message
>> news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>> Dear All,
>> does sql2005(std.) have any tools for backup whole database to file for
>> other Backup software backup?
>> thx,
>> William
>>
>|||With SQL 2005 Standard Edition, you can create a "Maintenance Job" to backup
on schedule, or you can create a SQL Agent Job to execute a SQL Backup
command.
SQL Backup is quite robust, and you may be OK without any other software.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
> Dear All,
> does sql2005(std.) have any tools for backup whole database to file for
> other Backup software backup?
> thx,
> William
>|||Hi,
can i be schedule run? how to restore? can you pls send me some reference
for futher study.
thx,
William
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Hi
>
> See ,BACKUP DATABASE command in the BOL
>
>
> "William Cheung" <chun_hei@.msn.com> wrote in message
> news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>> Dear,
>> i want to backup the SQL Server while without advance backup software(CA,
>> symantec...).
>> reg,
>> William
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
>> William
>> Can you elaborate a little bit? What are yopu trying to achive?
>> "William Cheung" <chun_hei@.msn.com> wrote in message
>> news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>> Dear All,
>> does sql2005(std.) have any tools for backup whole database to file for
>> other Backup software backup?
>> thx,
>> William
>>
>>
>|||Hi
> can i be schedule run?
Sure, create a job with BACKUP DATABASE... command and schedule it
? how to restore?
Take a look at RESTORE DATABASE ...command in the BOL
?can you pls send me some reference
> for futher study.
Start with BOL (Books On Line ) which ships with SQL Server
"William Cheung" <chun_hei@.msn.com> wrote in message
news:OEfjMPkAHHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> can i be schedule run? how to restore? can you pls send me some reference
> for futher study.
> thx,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
>> Hi
>>
>> See ,BACKUP DATABASE command in the BOL
>>
>>
>> "William Cheung" <chun_hei@.msn.com> wrote in message
>> news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>> Dear,
>> i want to backup the SQL Server while without advance backup
>> software(CA, symantec...).
>> reg,
>> William
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
>> William
>> Can you elaborate a little bit? What are yopu trying to achive?
>> "William Cheung" <chun_hei@.msn.com> wrote in message
>> news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>> Dear All,
>> does sql2005(std.) have any tools for backup whole database to file
>> for other Backup software backup?
>> thx,
>> William
>>
>>
>>
>|||Hi
you have to create backup script first and schedule it through sql job.
Schedule depends on your requirements and how much data you can afford to
lose
Here is the very basic script
full backup
BACKUP DATABASE [myDB] TO DISK=N'c:\myDBbackup.bak' WITH
NOINIT,Name='myDBbackup'
differential
BACKUP DATABASE [myDB] TO DISK=N'c:\myDBbackup.bak' WITH
NOINIT,DIFFERENTIAL,Name='myDBbackup'
log
BACKUP LOG [myDB] TO DISK = N'c:\myDBbackup.bak' WITH NOINIT , NOUNLOAD ,
NAME = 'myDBbackup', NOSKIP , STATS = 10, NOFORMAT
You need to understand how restore works... BOL is the best tool..
You will get some custom script from the following site
www.sqlservercentral.com
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx
http://www.databasejournal.com/features/mssql/article.php/3389831
vinu
"William Cheung" <chun_hei@.msn.com> wrote in message
news:OEfjMPkAHHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> can i be schedule run? how to restore? can you pls send me some reference
> for futher study.
> thx,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
>> Hi
>>
>> See ,BACKUP DATABASE command in the BOL
>>
>>
>> "William Cheung" <chun_hei@.msn.com> wrote in message
>> news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>> Dear,
>> i want to backup the SQL Server while without advance backup
>> software(CA, symantec...).
>> reg,
>> William
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
>> William
>> Can you elaborate a little bit? What are yopu trying to achive?
>> "William Cheung" <chun_hei@.msn.com> wrote in message
>> news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>> Dear All,
>> does sql2005(std.) have any tools for backup whole database to file
>> for other Backup software backup?
>> thx,
>> William
>>
>>
>>
>|||William,
You don't need to purchase the third party open file, SQL
agents to do SQL Server backups. They aren't used that often
and are often problematic - it's more common to just use
SQL Server native backups and backup to disk. Enterprise
backup software or tape backups can then just backup the SQL
Server backup file that you create with the SQL Server
native backup. As others have pointed out, you just use
T-SQL backup commands, you can schedule these using SQL
Server Agent.
-Sue
On Tue, 7 Nov 2006 15:16:17 +0800, "William Cheung"
<chun_hei@.msn.com> wrote:
>Dear All,
>does sql2005(std.) have any tools for backup whole database to file for
>other Backup software backup?
>thx,
>William
>

backup again

Dear All,
does sql2005(std.) have any tools for backup whole database to file for
other Backup software backup?
thx,
WilliamWilliam
Can you elaborate a little bit? What are yopu trying to achive?
"William Cheung" <chun_hei@.msn.com> wrote in message
news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
> Dear All,
> does sql2005(std.) have any tools for backup whole database to file for
> other Backup software backup?
> thx,
> William
>|||Dear,
i want to backup the SQL Server while without advance backup software(CA,
symantec...).
reg,
William
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
> William
> Can you elaborate a little bit? What are yopu trying to achive?
> "William Cheung" <chun_hei@.msn.com> wrote in message
> news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>|||Hi
See ,BACKUP DATABASE command in the BOL
"William Cheung" <chun_hei@.msn.com> wrote in message
news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> Dear,
> i want to backup the SQL Server while without advance backup software(CA,
> symantec...).
> reg,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23x8xo3jAHHA.204@.TK2MSFTNGP04.phx.gbl...
>|||With SQL 2005 Standard Edition, you can create a "Maintenance Job" to backup
on schedule, or you can create a SQL Agent Job to execute a SQL Backup
command.
SQL Backup is quite robust, and you may be OK without any other software.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"William Cheung" <chun_hei@.msn.com> wrote in message
news:Ox1xeyjAHHA.3396@.TK2MSFTNGP02.phx.gbl...
> Dear All,
> does sql2005(std.) have any tools for backup whole database to file for
> other Backup software backup?
> thx,
> William
>|||Hi,
can i be schedule run? how to restore? can you pls send me some reference
for futher study.
thx,
William
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
> Hi
>
> See ,BACKUP DATABASE command in the BOL
>
>
> "William Cheung" <chun_hei@.msn.com> wrote in message
> news:uC8T4DkAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>|||Hi
> can i be schedule run?
Sure, create a job with BACKUP DATABASE... command and schedule it
? how to restore?
Take a look at RESTORE DATABASE ...command in the BOL
?can you pls send me some reference
> for futher study.
Start with BOL (Books On Line ) which ships with SQL Server
"William Cheung" <chun_hei@.msn.com> wrote in message
news:OEfjMPkAHHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> can i be schedule run? how to restore? can you pls send me some reference
> for futher study.
> thx,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
>|||Hi
you have to create backup script first and schedule it through sql job.
Schedule depends on your requirements and how much data you can afford to
lose
Here is the very basic script
full backup
BACKUP DATABASE [myDB] TO DISK=N'c:\myDBbackup.bak' WITH
NOINIT,Name='myDBbackup'
differential
BACKUP DATABASE [myDB] TO DISK=N'c:\myDBbackup.bak' WITH
NOINIT,DIFFERENTIAL,Name='myDBbackup'
log
BACKUP LOG [myDB] TO DISK = N'c:\myDBbackup.bak' WITH NOINIT , NOUNLOA
D ,
NAME = 'myDBbackup', NOSKIP , STATS = 10, NOFORMAT
You need to understand how restore works... BOL is the best tool..
You will get some custom script from the following site
www.sqlservercentral.com
http://www.microsoft.com/technet/pr...cle.php/3389831
vinu
"William Cheung" <chun_hei@.msn.com> wrote in message
news:OEfjMPkAHHA.2316@.TK2MSFTNGP04.phx.gbl...
> Hi,
> can i be schedule run? how to restore? can you pls send me some reference
> for futher study.
> thx,
> William
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23SGqKKkAHHA.4808@.TK2MSFTNGP03.phx.gbl...
>|||William,
You don't need to purchase the third party open file, SQL
agents to do SQL Server backups. They aren't used that often
and are often problematic - it's more common to just use
SQL Server native backups and backup to disk. Enterprise
backup software or tape backups can then just backup the SQL
Server backup file that you create with the SQL Server
native backup. As others have pointed out, you just use
T-SQL backup commands, you can schedule these using SQL
Server Agent.
-Sue
On Tue, 7 Nov 2006 15:16:17 +0800, "William Cheung"
<chun_hei@.msn.com> wrote:

>Dear All,
>does sql2005(std.) have any tools for backup whole database to file for
>other Backup software backup?
>thx,
>William
>

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
>

backup

Hai,

I have taken back of sql2005 beta 2 version. But now i am unable to restore it sql2005 original version.

it is displaying the error incompatable version.

Please let me know how i can restore the backup from sql2005 beta 2 version to sql2005 original version.

regards

Suresh S

I think Microsoft made some breaking changes between Beta2 and RTM. I think you will have to script the schema from the Beta2 and create a new blank database in SQL Server 2005 RTM (or later), then use Integration Services to copy your data. Visual Studio 2005 for Database Professionals (Datadude), can automate much of this.