backward compatability

Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
--
Paul G
Software engineer.Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
--
Paul G
Software engineer.
"Geoff N. Hiten" wrote:
> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > Hi,
> > I was just wondering if you build a database in sql2005 and then save the
> > file(backup dbase) would it work to perform a restore from this file with
> > sql2000?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||oops, someone asked the same question just a few posts earlier!
--
Paul G
Software engineer.
"Paul" wrote:
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:
> > No.
> >
> > Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> > will restore to 2000 and 2005, but not 2008 (forthcoming).
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > > Hi,
> > > I was just wondering if you build a database in sql2005 and then save the
> > > file(backup dbase) would it work to perform a restore from this file with
> > > sql2000?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >

backward compatability

Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
Paul G
Software engineer.
Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
Paul G
Software engineer.
"Geoff N. Hiten" wrote:

> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
>
|||oops, someone asked the same question just a few posts earlier!
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:

Backups Failed

Dear All,
We recently changed our user for the SQL Agent. On Sunday
nights we backup all our databases and perform checks,
this Sunday they did not work. The error is as follows: -
'
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'INVESTMENTS1' as 'NT AUTHORITY\SYSTEM'
(trusted)
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 229:
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'sysdbmaintplans',
database 'msdb', owner 'dbo'.'
The odd thing however is we have other jobs that run on a
day by day basis that still work, can anyone help?
Thanks
PeterHi,
Check whether local administrators group on the machine still a member of
the
sysadmin server role? Else add it and verify the job.
Incase if you still have issues then,
Can you please add that user (User inwhich u start SQL Agent) to
buildin\administrators group and verify the execution.
or else give select permission on 'sysdbmaintplans' table to the user in
which you have started SQL Agent.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a44e01c40a74$e3ee35e0$a601280a@.phx.gbl...
> Dear All,
> We recently changed our user for the SQL Agent. On Sunday
> nights we backup all our databases and perform checks,
> this Sunday they did not work. The error is as follows: -
> '
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'INVESTMENTS1' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 229:
> [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
> permission denied on object 'sysdbmaintplans',
> database 'msdb', owner 'dbo'.'
> The odd thing however is we have other jobs that run on a
> day by day basis that still work, can anyone help?
> Thanks
> Peter|||Thanks Hari,
Someone took out the Sysadmin access to the BUILTIN\Admin
right.
Peter

>--Original Message--
>Hi,
>Check whether local administrators group on the machine
still a member of
>the
>sysadmin server role? Else add it and verify the job.
>Incase if you still have issues then,
>Can you please add that user (User inwhich u start SQL
Agent) to
>buildin\administrators group and verify the execution.
>or else give select permission on 'sysdbmaintplans' table
to the user in
>which you have started SQL Agent.
>
>
>Thanks
>Hari
>MCDBA
>
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a44e01c40a74$e3ee35e0$a601280a@.phx.gbl...
Sunday
follows: -
a
>
>.
>

Backup/Restore History Question

Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear to be orphaned records in the RestoreHistory table. (eg. The RestoreHistory has a record of the restore as well as a BackupSetID value, however these values cannot be found in the BackupSet and BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA
Too many drinks I think Rick<g>. Seriously though I have not noticed this before. Is there any chance there is a job that tries to clean out the history without using sp_deletebackuphistory?
Andrew J. Kelly SQL MVP
"Rick Sawtell" <quickening@.msn.com> wrote in message news:e7q53A54EHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear to be orphaned records in the RestoreHistory table. (eg. The RestoreHistory has a record of the restore as well as a BackupSetID value, however these values cannot be found in the BackupSet and BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA
|||There is that possibility,
I'm still researching it however. This only happens occasionally, so it is a bit baffling.
I've got 50 or so servers in the hosted environment and only a few of them are experiencing this problem.
All the servers are the same H/W and S/W, service packs etc.
Win 2k Server, SP4, SQL 2k SP3a etc.
I'll keep you posted, meanwhile if anyone else can think of anything...
I've got jobs that run after a restore takes place which ensures that everything in these logs match up (just in case we run into problems like this. ;-))
Rick
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:uT3Lwr54EHA.4008@.TK2MSFTNGP15.phx.gbl...
Too many drinks I think Rick<g>. Seriously though I have not noticed this before. Is there any chance there is a job that tries to clean out the history without using sp_deletebackuphistory?
Andrew J. Kelly SQL MVP
"Rick Sawtell" <quickening@.msn.com> wrote in message news:e7q53A54EHA.1260@.TK2MSFTNGP12.phx.gbl...
Hi all,
Occasionally, when I perform a RESTORE on a database,
The msdb tables are not updated properly.
The RESTORE functions normally, however when I run a query against the
BackupSet, BackupMediaFamily and RestoreHistory tables, there appear to be orphaned records in the RestoreHistory table. (eg. The RestoreHistory has a record of the restore as well as a BackupSetID value, however these values cannot be found in the BackupSet and BackupMediaFamily tables.
This is a recurring problem, however it does not happen all of the time.
Do you have any ideas?
TIA
Rick Sawtell
MCT, MCSD, MCDBA

backup/copy just a table

How can I perform a backup of a single table so that I
can then restore the table to another server with a new
table name?Unfortunatly there is no automatic way of doing this.
Instead I would use the export / import data from
Enterprice Manager.
Sumply right click your database, select all taskes and
there is an option for Export / Import data, from here its
fairly self explanitory.
Peter
"The best argument against democracy is a five-minute
conversation with the average voter."
Winston Churchill
>--Original Message--
>How can I perform a backup of a single table so that I
>can then restore the table to another server with a new
>table name?
>.
>|||Hi,
Backup a single table is not avalable directly from SQL 7, but it was there
in SQL 6.5. But you can perform a filegroup backup.
What you could do is you can put the tables which need frequent backup into
a seperate file group. After that you
can very well restore the file group backup.
Thanks
Hari
MCDBA
"Gio" wrote:
> How can I perform a backup of a single table so that I
> can then restore the table to another server with a new
> table name?
>|||Just be aware that the backup cannot be used alone, it has to be restored into the database from
where the backup was performed.
Also, the backup cannot be used to "go back in time" for that part of the database. All transaction
log backups taken since need to be applied until the database is usable.
Typically, when I see a request for table backup, filegroup backup is *not* the answer... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9FC2795D-79A0-4339-9D80-5BE68C80D675@.microsoft.com...
> Hi,
> Backup a single table is not avalable directly from SQL 7, but it was there
> in SQL 6.5. But you can perform a filegroup backup.
> What you could do is you can put the tables which need frequent backup into
> a seperate file group. After that you
> can very well restore the file group backup.
> Thanks
> Hari
> MCDBA
>
> "Gio" wrote:
> > How can I perform a backup of a single table so that I
> > can then restore the table to another server with a new
> > table name?
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> Just be aware that the backup cannot be used alone, it has to be restored
into the database from
> where the backup was performed.
> Also, the backup cannot be used to "go back in time" for that part of the
database. All transaction
> log backups taken since need to be applied until the database is usable.
> Typically, when I see a request for table backup, filegroup backup is
*not* the answer... :-)
Funny enough, I was just looking at this issue today and came to the same
conclusion. ;-)
While it has its place, for waht I need, it's just not going to work. Which
is unfortunate.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||Greg,
*Maybe* the PARTIAL option of the RESTORE command can be useful for you. You restore into a new
database, but using PARTICAL, you don't have to restore the whole database, only PRIMARY and the one
containing the data you want. Then just copy that data over to your production database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cE45d.83103$Kt5.23453@.twister.nyroc.rr.com...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
>> Just be aware that the backup cannot be used alone, it has to be restored
> into the database from
>> where the backup was performed.
>> Also, the backup cannot be used to "go back in time" for that part of the
> database. All transaction
>> log backups taken since need to be applied until the database is usable.
>> Typically, when I see a request for table backup, filegroup backup is
> *not* the answer... :-)
> Funny enough, I was just looking at this issue today and came to the same
> conclusion. ;-)
> While it has its place, for waht I need, it's just not going to work. Which
> is unfortunate.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23RSexuuoEHA.1712@.tk2msftngp13.phx.gbl...
> Greg,
> *Maybe* the PARTIAL option of the RESTORE command can be useful for you.
You restore into a new
> database, but using PARTICAL, you don't have to restore the whole
database, only PRIMARY and the one
> containing the data you want. Then just copy that data over to your
production database...
As you said "maybe". :-)
There are other areas I may end up using filegroup backups, but this won't
be one of them.
But thanks for remindnig me of the PARTIAL command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>

backup/copy just a table

How can I perform a backup of a single table so that I
can then restore the table to another server with a new
table name?
Hi,
Backup a single table is not avalable directly from SQL 7, but it was there
in SQL 6.5. But you can perform a filegroup backup.
What you could do is you can put the tables which need frequent backup into
a seperate file group. After that you
can very well restore the file group backup.
Thanks
Hari
MCDBA
"Gio" wrote:

> How can I perform a backup of a single table so that I
> can then restore the table to another server with a new
> table name?
>
|||Just be aware that the backup cannot be used alone, it has to be restored into the database from
where the backup was performed.
Also, the backup cannot be used to "go back in time" for that part of the database. All transaction
log backups taken since need to be applied until the database is usable.
Typically, when I see a request for table backup, filegroup backup is *not* the answer... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:9FC2795D-79A0-4339-9D80-5BE68C80D675@.microsoft.com...[vbcol=seagreen]
> Hi,
> Backup a single table is not avalable directly from SQL 7, but it was there
> in SQL 6.5. But you can perform a filegroup backup.
> What you could do is you can put the tables which need frequent backup into
> a seperate file group. After that you
> can very well restore the file group backup.
> Thanks
> Hari
> MCDBA
>
> "Gio" wrote:
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> Just be aware that the backup cannot be used alone, it has to be restored
into the database from
> where the backup was performed.
> Also, the backup cannot be used to "go back in time" for that part of the
database. All transaction
> log backups taken since need to be applied until the database is usable.
> Typically, when I see a request for table backup, filegroup backup is
*not* the answer... :-)
Funny enough, I was just looking at this issue today and came to the same
conclusion. ;-)
While it has its place, for waht I need, it's just not going to work. Which
is unfortunate.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
|||Greg,
*Maybe* the PARTIAL option of the RESTORE command can be useful for you. You restore into a new
database, but using PARTICAL, you don't have to restore the whole database, only PRIMARY and the one
containing the data you want. Then just copy that data over to your production database...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:cE45d.83103$Kt5.23453@.twister.nyroc.rr.com...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:u9TnxigoEHA.1088@.TK2MSFTNGP09.phx.gbl...
> into the database from
> database. All transaction
> *not* the answer... :-)
> Funny enough, I was just looking at this issue today and came to the same
> conclusion. ;-)
> While it has its place, for waht I need, it's just not going to work. Which
> is unfortunate.
>
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23RSexuuoEHA.1712@.tk2msftngp13.phx.gbl...
> Greg,
> *Maybe* the PARTIAL option of the RESTORE command can be useful for you.
You restore into a new
> database, but using PARTICAL, you don't have to restore the whole
database, only PRIMARY and the one
> containing the data you want. Then just copy that data over to your
production database...
As you said "maybe". :-)
There are other areas I may end up using filegroup backups, but this won't
be one of them.
But thanks for remindnig me of the PARTIAL command.

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>