MSDE 2000 Timeout when restoring a database

Hello,

I have been trying to restore a database to the msde but I keep getting a timeout when it happens. Does anyone know how to increase the time before a timeout is called?

I have restored a different, and smaller, database to the MSDE 2000 and it is Ok so I know everything else is OK.

Regards,

Ian.

Hello,

I should mention that i need to be able to change the Timeout from the command line and not using the supplied application. This is to be installed on the users PC and it needs to be automatic.

Regards,

Ian.

|||

How is the RESTORE statement being requested?

Control over the timeout is handled by the requesting application.

As far as I know, there is no way to set a default timeout for all applications.

As far as I know, the default timeout is infinite. So perhaps your application is explicitly requesting a timeout from the client access interface it uses.

Hope that helps.

sql

MSDE - moving a copy of a database and reloading onto another PC

I need to the best way to make a copy(Backup/export) of the data base put in on a CD or e-mail it. Have instructions to import or restore the database onto another PC.
Also in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn>
What and how does dtsrun.exe work?
Thanks
These articles might help ..
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||These articles might help ..
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

MSDE - moving a copy of a database and reloading onto another PC

I need to the best way to make a copy(Backup/export) of the data base put in on a CD or e-mail it. Have instructions to import or restore the database onto another PC.
Also in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn>
What and how does dtsrun.exe work?
Thanks
Please refer to these articles that might help -
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Please refer to these articles that might help -
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

msdb restore?

A terrible event has occurred. Our SQL Server 2000 database was attacked an
d
the master, msdb, etc was deleted. We used a utility to undelete those file
s
and have saved them. We had to reinstall Windows and SQL Server, but am
unable to replace the msdb. We have no backup so that isn't an option. Is
there a way to restore our DTS packages from the original msdb? Thanks.Did you try attaching it with a different name?
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> A terrible event has occurred. Our SQL Server 2000 database was attacked
and
> the master, msdb, etc was deleted. We used a utility to undelete those
files
> and have saved them. We had to reinstall Windows and SQL Server, but am
> unable to replace the msdb. We have no backup so that isn't an option.
Is
> there a way to restore our DTS packages from the original msdb? Thanks.|||I tried but I get the following error:
I/O error 38(Reached the end of the file.) detected during read at offset
0000000000000000 in file 'C:\Mymsdbdata.mdf'.
Connection Broken
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching it with a different name?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> and
> files
> Is
>
>|||> I tried but I get the following error:
What *exactly* did you try? I see that you renamed the MDF file, I meant to
attach as a different database name.
If the file is corrupt, and you may be S.O.L. (this is what backups are
for).
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...[vbcol=seagreen]
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0000000000000000 in file 'C:\Mymsdbdata.mdf'.
> Connection Broken
> "Aaron [SQL Server MVP]" wrote:
>
attacked[vbcol=seagreen]
those[vbcol=seagreen]
am[vbcol=seagreen]
option.[vbcol=seagreen]
Thanks.[vbcol=seagreen]|||I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
ran the following in Query Analyser:
exec sp_attach_db @.dbname =
N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
"Aaron [SQL Server MVP]" wrote:

> What *exactly* did you try? I see that you renamed the MDF file, I meant
to
> attach as a different database name.
> If the file is corrupt, and you may be S.O.L. (this is what backups are
> for).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...
> attacked
> those
> am
> option.
> Thanks.
>
>|||The data file is 0k, but the log file has data in it. I'm hoping that since
msdb was never backed up that it can be rebuilt using the log file. Can thi
s
be done?
"Phill" wrote:
[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
>|||I think you may have to open a case with PSS, or cut your losses. In the
future: backup is a very important word.
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filename2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
>
meant to[vbcol=seagreen]
offset[vbcol=seagreen]
but[vbcol=seagreen]|||Thanks Aaron. I knida knew that answer but didn't want to hear it.
"Aaron [SQL Server MVP]" wrote:

> I think you may have to open a case with PSS, or cut your losses. In the
> future: backup is a very important word.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...
> meant to
> offset
> but
>
>

msdb restore?

A terrible event has occurred. Our SQL Server 2000 database was attacked and
the master, msdb, etc was deleted. We used a utility to undelete those files
and have saved them. We had to reinstall Windows and SQL Server, but am
unable to replace the msdb. We have no backup so that isn't an option. Is
there a way to restore our DTS packages from the original msdb? Thanks.
Did you try attaching it with a different name?
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> A terrible event has occurred. Our SQL Server 2000 database was attacked
and
> the master, msdb, etc was deleted. We used a utility to undelete those
files
> and have saved them. We had to reinstall Windows and SQL Server, but am
> unable to replace the msdb. We have no backup so that isn't an option.
Is
> there a way to restore our DTS packages from the original msdb? Thanks.
|||I tried but I get the following error:
I/O error 38(Reached the end of the file.) detected during read at offset
0000000000000000 in file 'C:\Mymsdbdata.mdf'.
Connection Broken
"Aaron [SQL Server MVP]" wrote:

> Did you try attaching it with a different name?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:104A0726-0C71-4A29-B3A8-DF21C9158F38@.microsoft.com...
> and
> files
> Is
>
>
|||> I tried but I get the following error:
What *exactly* did you try? I see that you renamed the MDF file, I meant to
attach as a different database name.
If the file is corrupt, and you may be S.O.L. (this is what backups are
for).
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...[vbcol=seagreen]
> I/O error 38(Reached the end of the file.) detected during read at offset
> 0000000000000000 in file 'C:\Mymsdbdata.mdf'.
> Connection Broken
> "Aaron [SQL Server MVP]" wrote:
attacked[vbcol=seagreen]
those[vbcol=seagreen]
am[vbcol=seagreen]
option.[vbcol=seagreen]
Thanks.[vbcol=seagreen]
|||I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
ran the following in Query Analyser:
exec sp_attach_db @.dbname =
N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
"Aaron [SQL Server MVP]" wrote:

> What *exactly* did you try? I see that you renamed the MDF file, I meant to
> attach as a different database name.
> If the file is corrupt, and you may be S.O.L. (this is what backups are
> for).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:F9540632-EF19-41D8-A3FD-572722B13190@.microsoft.com...
> attacked
> those
> am
> option.
> Thanks.
>
>
|||The data file is 0k, but the log file has data in it. I'm hoping that since
msdb was never backed up that it can be rebuilt using the log file. Can this
be done?
"Phill" wrote:
[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
|||I think you may have to open a case with PSS, or cut your losses. In the
future: backup is a very important word.
http://www.aspfaq.com/
(Reverse address to reply.)
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...[vbcol=seagreen]
> I copied the mdf and ldf files to Mymsdbdata.mdf and Mymsdblog.ldf. The I
> ran the following in Query Analyser:
> exec sp_attach_db @.dbname =
> N'Mymsdb',@.filename1=N'C:\Mymsdbdata.mdf',@.filenam e2=N'C:\Mymsdblog.ldf'
> "Aaron [SQL Server MVP]" wrote:
meant to[vbcol=seagreen]
offset[vbcol=seagreen]
but[vbcol=seagreen]
|||Thanks Aaron. I knida knew that answer but didn't want to hear it.
"Aaron [SQL Server MVP]" wrote:

> I think you may have to open a case with PSS, or cut your losses. In the
> future: backup is a very important word.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:6B1824AD-016E-41CE-A851-889622057227@.microsoft.com...
> meant to
> offset
> but
>
>

msdb Restore......

I have restored the production msdb database on a test box. I can see my
entire DTS package and the scheduled jobs.
When I try to modify the job I am getting the following error.
Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
that originated from an MSZSERVER. The job was not saved. OK Button
How to delete/update then?
SQL 2K.
Thank you,
Benjamin
Dear Benjamin,
Is the server was renamed from one name to another, if so see the
logins were in sync, another work around is
The best way to handle this problem after the rename process is to follow
these steps: 1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.
Or
1.Script the jobs in the server from where u backed up the msdb database.
2.Run the scripts in the new server.
for more details go through the following kb site
http://support.microsoft.com/default...b;en-us;281642
"Benjamin Nethan" wrote:

> I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>
>
|||You need to update the originating_server column in sysjobs to reflect the
name of the instance you restored the database to. You can use the following
code to do that http://sqldev.net/download/sqlagent/...ent_rename.sql
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Benjamin Nethan" <BNethan@.hotmail.com> wrote in message
news:e59AAE5pFHA.820@.TK2MSFTNGP09.phx.gbl...
>I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>

msdb Restore......

I have restored the production msdb database on a test box. I can see my
entire DTS package and the scheduled jobs.
When I try to modify the job I am getting the following error.
Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
that originated from an MSZSERVER. The job was not saved. OK Button
How to delete/update then?
SQL 2K.
Thank you,
BenjaminDear Benjamin,
Is the server was renamed from one name to another, if so see the
logins were in sync, another work around is
The best way to handle this problem after the rename process is to follow
these steps: 1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.
Or
1.Script the jobs in the server from where u backed up the msdb database.
2.Run the scripts in the new server.
for more details go through the following kb site
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642
"Benjamin Nethan" wrote:
> I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>
>|||You need to update the originating_server column in sysjobs to reflect the
name of the instance you restored the database to. You can use the following
code to do that http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Benjamin Nethan" <BNethan@.hotmail.com> wrote in message
news:e59AAE5pFHA.820@.TK2MSFTNGP09.phx.gbl...
>I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>

msdb Restore......

I have restored the production msdb database on a test box. I can see my
entire DTS package and the scheduled jobs.
When I try to modify the job I am getting the following error.
Error 14274: Cannot add, update, or delete a job (or its steps or schedules)
that originated from an MSZSERVER. The job was not saved. OK Button
How to delete/update then?
SQL 2K.
Thank you,
BenjaminDear Benjamin,
Is the server was renamed from one name to another, if so see the
logins were in sync, another work around is
The best way to handle this problem after the rename process is to follow
these steps: 1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.
Or
1.Script the jobs in the server from where u backed up the msdb database.
2.Run the scripts in the new server.
for more details go through the following kb site
http://support.microsoft.com/defaul...kb;en-us;281642
"Benjamin Nethan" wrote:

> I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or schedule
s)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>
>|||You need to update the originating_server column in sysjobs to reflect the
name of the instance you restored the database to. You can use the following
code to do that http://sqldev.net/download/sqlagent...gent_rename.sql
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Benjamin Nethan" <BNethan@.hotmail.com> wrote in message
news:e59AAE5pFHA.820@.TK2MSFTNGP09.phx.gbl...
>I have restored the production msdb database on a test box. I can see my
> entire DTS package and the scheduled jobs.
> When I try to modify the job I am getting the following error.
> Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules)
> that originated from an MSZSERVER. The job was not saved. OK Button
> How to delete/update then?
> SQL 2K.
> Thank you,
> Benjamin
>
>

msdb restore to standby server

Can the msdb database safely be moved to a new server by taking a backup fro
m
the source server and restoring to the target? I am aware that
sysjobs.originating_server must be updated after the restore and that
Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc
.SQL Server 2000 sp3
"Brandon" wrote:

> Can the msdb database safely be moved to a new server by taking a backup f
rom
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, e
tc.
>|||Ive never had good luck doing that and just would up going the MS route.
--
TIA,
ChrisR
"Brandon" wrote:

> Can the msdb database safely be moved to a new server by taking a backup f
rom
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, e
tc.
>

msdb restore to standby server

Can the msdb database safely be moved to a new server by taking a backup from
the source server and restoring to the target? I am aware that
sysjobs.originating_server must be updated after the restore and that
Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
SQL Server 2000 sp3
"Brandon" wrote:

> Can the msdb database safely be moved to a new server by taking a backup from
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
>
|||Ive never had good luck doing that and just would up going the MS route.
TIA,
ChrisR
"Brandon" wrote:

> Can the msdb database safely be moved to a new server by taking a backup from
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
>

msdb restore to standby server

Can the msdb database safely be moved to a new server by taking a backup from
the source server and restoring to the target? I am aware that
sysjobs.originating_server must be updated after the restore and that
Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.SQL Server 2000 sp3
"Brandon" wrote:
> Can the msdb database safely be moved to a new server by taking a backup from
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
>|||Ive never had good luck doing that and just would up going the MS route.
--
TIA,
ChrisR
"Brandon" wrote:
> Can the msdb database safely be moved to a new server by taking a backup from
> the source server and restoring to the target? I am aware that
> sysjobs.originating_server must be updated after the restore and that
> Microsoft suggests transfering the msdb by scripting jobs, dts packages, etc.
>

msdb restore

Hello --
Using SQL 2000. When performing a trans log restore to our backup SQL box
the server locked up. After I got the server restarted the msdb was marked
'suspect'. I don't have a backup of the msdb. I can still perform
restores, but I get error message about the msdb. Do I need to re-install
SQL on this box? I'm not sure what to do (besides doing msdb backups from
now on). Any help would be appreciated. Thank you.
Hi,
No need to install SQL Server.
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
Drop or detach the MSDB database and rebuild the MSDB using the installation
script(INSTMSDB.SQL) in the SLQ Server install folder. But after this
you need to create all your sql agent jobs manually. Please ensure that you
do a backup of MSDB database hereafter
One more solution-
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
From query analyzer Detach MSDB database, copy the files from the CD (in
x86\Data) and then attach this files.
Thanks
Hari
SQL Server MVP
"FRR" <FRR@.discussions.microsoft.com> wrote in message
news:51EACBD9-BB21-4FB6-9C2F-2A5C5B74D083@.microsoft.com...
> Hello --
> Using SQL 2000. When performing a trans log restore to our backup SQL box
> the server locked up. After I got the server restarted the msdb was
> marked
> 'suspect'. I don't have a backup of the msdb. I can still perform
> restores, but I get error message about the msdb. Do I need to re-install
> SQL on this box? I'm not sure what to do (besides doing msdb backups from
> now on). Any help would be appreciated. Thank you.

msdb restore

I have a client who is trying to restore an old backup of
the msdb database. Of course, their new build of SQL is
different, so they can't do the restore. We get an error
similar to:
"Server: Msg 3168, Level 16, State 1, Line 1
The backup of the system database on device
d:\temp\master.bak cannot be restored because it was
created by a different version of the server (134217904)
than this server (134217920).
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally. "
what I need to know is how to interpret the version number
in the error message - i.e. "134217904" so I can figure
out what version they need to have running in order to
restore. Anyone have any ideas?
Thanks!Take a look at Q264474. That will help you :-)
/Peter
"Lia" <lkeston@.patriotsoftware.biz> wrote in message
news:4cdc01c3d5fb$f1edd8b0$7d02280a@.phx.gbl...
> I have a client who is trying to restore an old backup of
> the msdb database. Of course, their new build of SQL is
> different, so they can't do the restore. We get an error
> similar to:
> "Server: Msg 3168, Level 16, State 1, Line 1
> The backup of the system database on device
> d:\temp\master.bak cannot be restored because it was
> created by a different version of the server (134217904)
> than this server (134217920).
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally. "
> what I need to know is how to interpret the version number
> in the error message - i.e. "134217904" so I can figure
> out what version they need to have running in order to
> restore. Anyone have any ideas?
> Thanks!
>|||The following command should tell you what build of SQL Server the backup
was taken from:
RESTORE HEADERONLY
FROM DISK = 'd:\temp\master.bak '
You can look at http://support.microsoft.com/?id=264474 for further
information.
Rand
This posting is provided "as is" with no warranties and confers no rights.

msdb restore

Hello --
Using SQL 2000. When performing a trans log restore to our backup SQL box
the server locked up. After I got the server restarted the msdb was marked
'suspect'. I don't have a backup of the msdb. I can still perform
restores, but I get error message about the msdb. Do I need to re-install
SQL on this box? I'm not sure what to do (besides doing msdb backups from
now on). Any help would be appreciated. Thank you.Hi,
No need to install SQL Server.
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
Drop or detach the MSDB database and rebuild the MSDB using the installation
script(INSTMSDB.SQL) in the SLQ Server install folder. But after this
you need to create all your sql agent jobs manually. Please ensure that you
do a backup of MSDB database hereafter :)
One more solution-
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
From query analyzer Detach MSDB database, copy the files from the CD (in
x86\Data) and then attach this files.
Thanks
Hari
SQL Server MVP
"FRR" <FRR@.discussions.microsoft.com> wrote in message
news:51EACBD9-BB21-4FB6-9C2F-2A5C5B74D083@.microsoft.com...
> Hello --
> Using SQL 2000. When performing a trans log restore to our backup SQL box
> the server locked up. After I got the server restarted the msdb was
> marked
> 'suspect'. I don't have a backup of the msdb. I can still perform
> restores, but I get error message about the msdb. Do I need to re-install
> SQL on this box? I'm not sure what to do (besides doing msdb backups from
> now on). Any help would be appreciated. Thank you.

msdb restore

Hello --
Using SQL 2000. When performing a trans log restore to our backup SQL box
the server locked up. After I got the server restarted the msdb was marked
'suspect'. I don't have a backup of the msdb. I can still perform
restores, but I get error message about the msdb. Do I need to re-install
SQL on this box? I'm not sure what to do (besides doing msdb backups from
now on). Any help would be appreciated. Thank you.Hi,
No need to install SQL Server.
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
Drop or detach the MSDB database and rebuild the MSDB using the installation
script(INSTMSDB.SQL) in the SLQ Server install folder. But after this
you need to create all your sql agent jobs manually. Please ensure that you
do a backup of MSDB database hereafter
One more solution-
Start SQL Server with Trace 3608 from command prompt
SQLSERVER -T3608 -m
From query analyzer Detach MSDB database, copy the files from the CD (in
x86\Data) and then attach this files.
Thanks
Hari
SQL Server MVP
"FRR" <FRR@.discussions.microsoft.com> wrote in message
news:51EACBD9-BB21-4FB6-9C2F-2A5C5B74D083@.microsoft.com...
> Hello --
> Using SQL 2000. When performing a trans log restore to our backup SQL box
> the server locked up. After I got the server restarted the msdb was
> marked
> 'suspect'. I don't have a backup of the msdb. I can still perform
> restores, but I get error message about the msdb. Do I need to re-install
> SQL on this box? I'm not sure what to do (besides doing msdb backups from
> now on). Any help would be appreciated. Thank you.

msdb recovery on sql 2005

Hi

I have a backup of msdb on sql 2005 version 9.00.2047

I need to use this backup to restore msdb on the same machine.

But the version has been upgraded to 9.00.2153

All of my attempts end up with the same messages

"...cannot be restored because it was created by a different version of the server..."

Thanks.

You are going to have to find an install of version 9.00.2047, restore the msdb database, upgrade the instance to 9.00.2153, backup the msdb database again, and then use that backup to restore to the instance that you want.

There is a reason that every hotfix and service pack has an explicit warning message that you must backup system databases immediately after applying the service pack or hotfix. You can not restore a system database across hotfix or service pack versions.

|||Thanks it worked as you said.

MSDB question

WHat would be the danger of adding a couple of indexes to the msdb system
tables (particularly the backup/restore jobs tables)? I know that a hotfix
or sp update may drop the tables, but if we are staying on sp3 or sp4 for
now, would it cause a sytem wide issue?
This is for SQL2000 on a Win2003 server.
Thankscw
Why? MS strongly recommended not direct updating system tables.
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>|||I do it all the time. One side effect of constant build testing is that you
don't run something for a long time and see some of the life cycle issues
that we see in real life. Just don't add keys or constraints, only indexes
and you should be fine.
Here is the set I use:
/ ****************************************
********************************
* *
* Title: msdb index creation *
* Author: Geoff N. Hiten *
* Purpose: Index msdb database *
* Date: 12/12/2005 *
* Modifications: *
* *
* 06-22-2001 *
* Sample Entry *
* *
* *
****************************************
********************************/
use
msdb
go
--backupset
Create
index IX_backupset_backup_set_id on backupset (backup_set_id)
go
Create
index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
go
Create
index IX_backupset_media_set_id on backupset (media_set_id)
go
Create
index IX_backupset_backup_finish_date on backupset (backup_finish_date)
go
Create
index IX_backupset_backup_start_date on backupset (backup_start_date)
go
--backupmediaset
Create
index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
go
--backupfile
Create
index IX_backupfile_backup_set_id on backupfile (backup_set_id)
go
--backupmediafamily
Create
index IX_backupmediafamily_media_set_id on backupmediafamily (media_set_id)
go
--restorehistory
Create
index IX_restorehistory_restore_history_id on restorehistory
(restore_history_id)
go
Create
index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
go
--restorefile
Create
index IX_restorefile_restore_history_id on restorefile (restore_history_id)
go
--restorefilegroup
Create
index IX_restorefilegroup_restore_history_id on restorefilegroup
(restore_history_id)
go
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>|||Thanks - several of these indexes you have listed below are the same ones I
added. I had dropped an old database from a SQL2000 server (using EM). The
DB had daily backups and several transaction log backups during the day.
This has been going on for the last 2-3 years and the number of records in
the msdb backup system tables for historical purposes was huge. When EM
drops a DB and begins to delete the historical information about the
backups, it called the sp_delete_backup_history procedure. If there was
ever a procedure that needed indexes, this is it. The procedure had been
running all night and a trace showed it doing several table scans using
"lots" of cursors.
After I added the indexes the job finished w/i 10 minutes. We dropped
another database that would have had the same backup history and the
procedure finished w/i 15 minutes.
Thanks for the confirmation -- cw
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>I do it all the time. One side effect of constant build testing is that
>you don't run something for a long time and see some of the life cycle
>issues that we see in real life. Just don't add keys or constraints, only
>indexes and you should be fine.
> Here is the set I use:
> / ****************************************
********************************
> * *
> * Title: msdb index creation *
> * Author: Geoff N. Hiten *
> * Purpose: Index msdb database *
> * Date: 12/12/2005 *
> * Modifications: *
> * *
> * 06-22-2001 *
> * Sample Entry *
> * *
> * *
> ****************************************
********************************/
> use
> msdb
> go
> --backupset
> Create
> index IX_backupset_backup_set_id on backupset (backup_set_id)
> go
> Create
> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
> go
> Create
> index IX_backupset_media_set_id on backupset (media_set_id)
> go
> Create
> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
> go
> Create
> index IX_backupset_backup_start_date on backupset (backup_start_date)
> go
> --backupmediaset
> Create
> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
> go
> --backupfile
> Create
> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
> go
> --backupmediafamily
> Create
> index IX_backupmediafamily_media_set_id on backupmediafamily
> (media_set_id)
> go
> --restorehistory
> Create
> index IX_restorehistory_restore_history_id on restorehistory
> (restore_history_id)
> go
> Create
> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
> go
> --restorefile
> Create
> index IX_restorefile_restore_history_id on restorefile
> (restore_history_id)
> go
> --restorefilegroup
> Create
> index IX_restorefilegroup_restore_history_id on restorefilegroup
> (restore_history_id)
> go
>
> "cw" <cw@.3mc.com> wrote in message
> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>|||I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
It deletes one day at a time until it gets to the minimum retention time I
set. And no, I don't think I will post that.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"cw" <cw@.3mc.com> wrote in message
news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Thanks - several of these indexes you have listed below are the same ones
> I added. I had dropped an old database from a SQL2000 server (using EM).
> The DB had daily backups and several transaction log backups during the
> day. This has been going on for the last 2-3 years and the number of
> records in the msdb backup system tables for historical purposes was huge.
> When EM drops a DB and begins to delete the historical information about
> the backups, it called the sp_delete_backup_history procedure. If there
> was ever a procedure that needed indexes, this is it. The procedure had
> been running all night and a trace showed it doing several table scans
> using "lots" of cursors.
> After I added the indexes the job finished w/i 10 minutes. We dropped
> another database that would have had the same backup history and the
> procedure finished w/i 15 minutes.
> Thanks for the confirmation -- cw
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>|||Several months ago I noticed that these backup tables in msdb were
growing out of control. Attempts to delete rows using the delete backup
history sp took *forever*. Since it is my understanding that these
tables are really only needed if you use the interfaces, such as EM or
Mgmt Studio, to do restores, I decided to just truncate the tables then
start running the sp every night to only keep 3 days history. This
method was much faster needless to say and nothing was the worse for
it.
Geoff N. Hiten wrote:[vbcol=seagreen]
> I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
> It deletes one day at a time until it gets to the minimum retention time I
> set. And no, I don't think I will post that.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "cw" <cw@.3mc.com> wrote in message
> news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...

MSDB question

WHat would be the danger of adding a couple of indexes to the msdb system
tables (particularly the backup/restore jobs tables)? I know that a hotfix
or sp update may drop the tables, but if we are staying on sp3 or sp4 for
now, would it cause a sytem wide issue?
This is for SQL2000 on a Win2003 server.
Thanks
cw
Why? MS strongly recommended not direct updating system tables.
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>
|||I do it all the time. One side effect of constant build testing is that you
don't run something for a long time and see some of the life cycle issues
that we see in real life. Just don't add keys or constraints, only indexes
and you should be fine.
Here is the set I use:
/************************************************** **********************
* *
* Title: msdb index creation *
* Author: Geoff N. Hiten *
* Purpose: Index msdb database *
* Date: 12/12/2005 *
* Modifications: *
* *
* 06-22-2001 *
* Sample Entry *
* *
* *
************************************************** **********************/
use
msdb
go
--backupset
Create
index IX_backupset_backup_set_id on backupset (backup_set_id)
go
Create
index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
go
Create
index IX_backupset_media_set_id on backupset (media_set_id)
go
Create
index IX_backupset_backup_finish_date on backupset (backup_finish_date)
go
Create
index IX_backupset_backup_start_date on backupset (backup_start_date)
go
--backupmediaset
Create
index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
go
--backupfile
Create
index IX_backupfile_backup_set_id on backupfile (backup_set_id)
go
--backupmediafamily
Create
index IX_backupmediafamily_media_set_id on backupmediafamily (media_set_id)
go
--restorehistory
Create
index IX_restorehistory_restore_history_id on restorehistory
(restore_history_id)
go
Create
index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
go
--restorefile
Create
index IX_restorefile_restore_history_id on restorefile (restore_history_id)
go
--restorefilegroup
Create
index IX_restorefilegroup_restore_history_id on restorefilegroup
(restore_history_id)
go
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>
|||Thanks - several of these indexes you have listed below are the same ones I
added. I had dropped an old database from a SQL2000 server (using EM). The
DB had daily backups and several transaction log backups during the day.
This has been going on for the last 2-3 years and the number of records in
the msdb backup system tables for historical purposes was huge. When EM
drops a DB and begins to delete the historical information about the
backups, it called the sp_delete_backup_history procedure. If there was
ever a procedure that needed indexes, this is it. The procedure had been
running all night and a trace showed it doing several table scans using
"lots" of cursors.
After I added the indexes the job finished w/i 10 minutes. We dropped
another database that would have had the same backup history and the
procedure finished w/i 15 minutes.
Thanks for the confirmation -- cw
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>I do it all the time. One side effect of constant build testing is that
>you don't run something for a long time and see some of the life cycle
>issues that we see in real life. Just don't add keys or constraints, only
>indexes and you should be fine.
> Here is the set I use:
> /************************************************** **********************
> * *
> * Title: msdb index creation *
> * Author: Geoff N. Hiten *
> * Purpose: Index msdb database *
> * Date: 12/12/2005 *
> * Modifications: *
> * *
> * 06-22-2001 *
> * Sample Entry *
> * *
> * *
> ************************************************** **********************/
> use
> msdb
> go
> --backupset
> Create
> index IX_backupset_backup_set_id on backupset (backup_set_id)
> go
> Create
> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
> go
> Create
> index IX_backupset_media_set_id on backupset (media_set_id)
> go
> Create
> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
> go
> Create
> index IX_backupset_backup_start_date on backupset (backup_start_date)
> go
> --backupmediaset
> Create
> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
> go
> --backupfile
> Create
> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
> go
> --backupmediafamily
> Create
> index IX_backupmediafamily_media_set_id on backupmediafamily
> (media_set_id)
> go
> --restorehistory
> Create
> index IX_restorehistory_restore_history_id on restorehistory
> (restore_history_id)
> go
> Create
> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
> go
> --restorefile
> Create
> index IX_restorefile_restore_history_id on restorefile
> (restore_history_id)
> go
> --restorefilegroup
> Create
> index IX_restorefilegroup_restore_history_id on restorefilegroup
> (restore_history_id)
> go
>
> "cw" <cw@.3mc.com> wrote in message
> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>
|||I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
It deletes one day at a time until it gets to the minimum retention time I
set. And no, I don't think I will post that.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"cw" <cw@.3mc.com> wrote in message
news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Thanks - several of these indexes you have listed below are the same ones
> I added. I had dropped an old database from a SQL2000 server (using EM).
> The DB had daily backups and several transaction log backups during the
> day. This has been going on for the last 2-3 years and the number of
> records in the msdb backup system tables for historical purposes was huge.
> When EM drops a DB and begins to delete the historical information about
> the backups, it called the sp_delete_backup_history procedure. If there
> was ever a procedure that needed indexes, this is it. The procedure had
> been running all night and a trace showed it doing several table scans
> using "lots" of cursors.
> After I added the indexes the job finished w/i 10 minutes. We dropped
> another database that would have had the same backup history and the
> procedure finished w/i 15 minutes.
> Thanks for the confirmation -- cw
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>
|||Several months ago I noticed that these backup tables in msdb were
growing out of control. Attempts to delete rows using the delete backup
history sp took *forever*. Since it is my understanding that these
tables are really only needed if you use the interfaces, such as EM or
Mgmt Studio, to do restores, I decided to just truncate the tables then
start running the sp every night to only keep 3 days history. This
method was much faster needless to say and nothing was the worse for
it.
Geoff N. Hiten wrote:[vbcol=seagreen]
> I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
> It deletes one day at a time until it gets to the minimum retention time I
> set. And no, I don't think I will post that.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "cw" <cw@.3mc.com> wrote in message
> news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...

MSDB question

WHat would be the danger of adding a couple of indexes to the msdb system
tables (particularly the backup/restore jobs tables)? I know that a hotfix
or sp update may drop the tables, but if we are staying on sp3 or sp4 for
now, would it cause a sytem wide issue?
This is for SQL2000 on a Win2003 server.
Thankscw
Why? MS strongly recommended not direct updating system tables.
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>|||I do it all the time. One side effect of constant build testing is that you
don't run something for a long time and see some of the life cycle issues
that we see in real life. Just don't add keys or constraints, only indexes
and you should be fine.
Here is the set I use:
/************************************************************************
* *
* Title: msdb index creation *
* Author: Geoff N. Hiten *
* Purpose: Index msdb database *
* Date: 12/12/2005 *
* Modifications: *
* *
* 06-22-2001 *
* Sample Entry *
* *
* *
************************************************************************/
use
msdb
go
--backupset
Create
index IX_backupset_backup_set_id on backupset (backup_set_id)
go
Create
index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
go
Create
index IX_backupset_media_set_id on backupset (media_set_id)
go
Create
index IX_backupset_backup_finish_date on backupset (backup_finish_date)
go
Create
index IX_backupset_backup_start_date on backupset (backup_start_date)
go
--backupmediaset
Create
index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
go
--backupfile
Create
index IX_backupfile_backup_set_id on backupfile (backup_set_id)
go
--backupmediafamily
Create
index IX_backupmediafamily_media_set_id on backupmediafamily (media_set_id)
go
--restorehistory
Create
index IX_restorehistory_restore_history_id on restorehistory
(restore_history_id)
go
Create
index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
go
--restorefile
Create
index IX_restorefile_restore_history_id on restorefile (restore_history_id)
go
--restorefilegroup
Create
index IX_restorefilegroup_restore_history_id on restorefilegroup
(restore_history_id)
go
"cw" <cw@.3mc.com> wrote in message
news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> WHat would be the danger of adding a couple of indexes to the msdb system
> tables (particularly the backup/restore jobs tables)? I know that a
> hotfix or sp update may drop the tables, but if we are staying on sp3 or
> sp4 for now, would it cause a sytem wide issue?
> This is for SQL2000 on a Win2003 server.
> Thanks
>|||Thanks - several of these indexes you have listed below are the same ones I
added. I had dropped an old database from a SQL2000 server (using EM). The
DB had daily backups and several transaction log backups during the day.
This has been going on for the last 2-3 years and the number of records in
the msdb backup system tables for historical purposes was huge. When EM
drops a DB and begins to delete the historical information about the
backups, it called the sp_delete_backup_history procedure. If there was
ever a procedure that needed indexes, this is it. The procedure had been
running all night and a trace showed it doing several table scans using
"lots" of cursors.
After I added the indexes the job finished w/i 10 minutes. We dropped
another database that would have had the same backup history and the
procedure finished w/i 15 minutes.
Thanks for the confirmation -- cw
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>I do it all the time. One side effect of constant build testing is that
>you don't run something for a long time and see some of the life cycle
>issues that we see in real life. Just don't add keys or constraints, only
>indexes and you should be fine.
> Here is the set I use:
> /************************************************************************
> * *
> * Title: msdb index creation *
> * Author: Geoff N. Hiten *
> * Purpose: Index msdb database *
> * Date: 12/12/2005 *
> * Modifications: *
> * *
> * 06-22-2001 *
> * Sample Entry *
> * *
> * *
> ************************************************************************/
> use
> msdb
> go
> --backupset
> Create
> index IX_backupset_backup_set_id on backupset (backup_set_id)
> go
> Create
> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
> go
> Create
> index IX_backupset_media_set_id on backupset (media_set_id)
> go
> Create
> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
> go
> Create
> index IX_backupset_backup_start_date on backupset (backup_start_date)
> go
> --backupmediaset
> Create
> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
> go
> --backupfile
> Create
> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
> go
> --backupmediafamily
> Create
> index IX_backupmediafamily_media_set_id on backupmediafamily
> (media_set_id)
> go
> --restorehistory
> Create
> index IX_restorehistory_restore_history_id on restorehistory
> (restore_history_id)
> go
> Create
> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
> go
> --restorefile
> Create
> index IX_restorefile_restore_history_id on restorefile
> (restore_history_id)
> go
> --restorefilegroup
> Create
> index IX_restorefilegroup_restore_history_id on restorefilegroup
> (restore_history_id)
> go
>
> "cw" <cw@.3mc.com> wrote in message
> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>> WHat would be the danger of adding a couple of indexes to the msdb system
>> tables (particularly the backup/restore jobs tables)? I know that a
>> hotfix or sp update may drop the tables, but if we are staying on sp3 or
>> sp4 for now, would it cause a sytem wide issue?
>> This is for SQL2000 on a Win2003 server.
>> Thanks
>|||I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
It deletes one day at a time until it gets to the minimum retention time I
set. And no, I don't think I will post that.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"cw" <cw@.3mc.com> wrote in message
news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> Thanks - several of these indexes you have listed below are the same ones
> I added. I had dropped an old database from a SQL2000 server (using EM).
> The DB had daily backups and several transaction log backups during the
> day. This has been going on for the last 2-3 years and the number of
> records in the msdb backup system tables for historical purposes was huge.
> When EM drops a DB and begins to delete the historical information about
> the backups, it called the sp_delete_backup_history procedure. If there
> was ever a procedure that needed indexes, this is it. The procedure had
> been running all night and a trace showed it doing several table scans
> using "lots" of cursors.
> After I added the indexes the job finished w/i 10 minutes. We dropped
> another database that would have had the same backup history and the
> procedure finished w/i 15 minutes.
> Thanks for the confirmation -- cw
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
>>I do it all the time. One side effect of constant build testing is that
>>you don't run something for a long time and see some of the life cycle
>>issues that we see in real life. Just don't add keys or constraints, only
>>indexes and you should be fine.
>> Here is the set I use:
>> /************************************************************************
>> * *
>> * Title: msdb index creation *
>> * Author: Geoff N. Hiten *
>> * Purpose: Index msdb database *
>> * Date: 12/12/2005 *
>> * Modifications: *
>> * *
>> * 06-22-2001 *
>> * Sample Entry *
>> * *
>> * *
>> ************************************************************************/
>> use
>> msdb
>> go
>> --backupset
>> Create
>> index IX_backupset_backup_set_id on backupset (backup_set_id)
>> go
>> Create
>> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
>> go
>> Create
>> index IX_backupset_media_set_id on backupset (media_set_id)
>> go
>> Create
>> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
>> go
>> Create
>> index IX_backupset_backup_start_date on backupset (backup_start_date)
>> go
>> --backupmediaset
>> Create
>> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
>> go
>> --backupfile
>> Create
>> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
>> go
>> --backupmediafamily
>> Create
>> index IX_backupmediafamily_media_set_id on backupmediafamily
>> (media_set_id)
>> go
>> --restorehistory
>> Create
>> index IX_restorehistory_restore_history_id on restorehistory
>> (restore_history_id)
>> go
>> Create
>> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
>> go
>> --restorefile
>> Create
>> index IX_restorefile_restore_history_id on restorefile
>> (restore_history_id)
>> go
>> --restorefilegroup
>> Create
>> index IX_restorefilegroup_restore_history_id on restorefilegroup
>> (restore_history_id)
>> go
>>
>> "cw" <cw@.3mc.com> wrote in message
>> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
>> WHat would be the danger of adding a couple of indexes to the msdb
>> system tables (particularly the backup/restore jobs tables)? I know
>> that a hotfix or sp update may drop the tables, but if we are staying on
>> sp3 or sp4 for now, would it cause a sytem wide issue?
>> This is for SQL2000 on a Win2003 server.
>> Thanks
>>
>|||Several months ago I noticed that these backup tables in msdb were
growing out of control. Attempts to delete rows using the delete backup
history sp took *forever*. Since it is my understanding that these
tables are really only needed if you use the interfaces, such as EM or
Mgmt Studio, to do restores, I decided to just truncate the tables then
start running the sp every night to only keep 3 days history. This
method was much faster needless to say and nothing was the worse for
it.
Geoff N. Hiten wrote:
> I also use a "nibbler" to drive the s_delete_backup_history cleanuot proc.
> It deletes one day at a time until it gets to the minimum retention time I
> set. And no, I don't think I will post that.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "cw" <cw@.3mc.com> wrote in message
> news:OHXUGKrAHHA.3540@.TK2MSFTNGP03.phx.gbl...
> > Thanks - several of these indexes you have listed below are the same ones
> > I added. I had dropped an old database from a SQL2000 server (using EM).
> > The DB had daily backups and several transaction log backups during the
> > day. This has been going on for the last 2-3 years and the number of
> > records in the msdb backup system tables for historical purposes was huge.
> > When EM drops a DB and begins to delete the historical information about
> > the backups, it called the sp_delete_backup_history procedure. If there
> > was ever a procedure that needed indexes, this is it. The procedure had
> > been running all night and a trace showed it doing several table scans
> > using "lots" of cursors.
> > After I added the indexes the job finished w/i 10 minutes. We dropped
> > another database that would have had the same backup history and the
> > procedure finished w/i 15 minutes.
> >
> > Thanks for the confirmation -- cw
> >
> >
> > "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> > news:%235GjJwqAHHA.996@.TK2MSFTNGP02.phx.gbl...
> >>I do it all the time. One side effect of constant build testing is that
> >>you don't run something for a long time and see some of the life cycle
> >>issues that we see in real life. Just don't add keys or constraints, only
> >>indexes and you should be fine.
> >>
> >> Here is the set I use:
> >>
> >> /************************************************************************
> >>
> >> * *
> >>
> >> * Title: msdb index creation *
> >>
> >> * Author: Geoff N. Hiten *
> >>
> >> * Purpose: Index msdb database *
> >>
> >> * Date: 12/12/2005 *
> >>
> >> * Modifications: *
> >>
> >> * *
> >>
> >> * 06-22-2001 *
> >>
> >> * Sample Entry *
> >>
> >> * *
> >>
> >> * *
> >>
> >> ************************************************************************/
> >>
> >> use
> >>
> >> msdb
> >> go
> >>
> >> --backupset
> >>
> >> Create
> >>
> >> index IX_backupset_backup_set_id on backupset (backup_set_id)
> >> go
> >>
> >> Create
> >>
> >> index IX_backupset_backup_set_uuid on backupset (backup_set_uuid)
> >> go
> >>
> >> Create
> >>
> >> index IX_backupset_media_set_id on backupset (media_set_id)
> >> go
> >>
> >> Create
> >>
> >> index IX_backupset_backup_finish_date on backupset (backup_finish_date)
> >> go
> >>
> >> Create
> >>
> >> index IX_backupset_backup_start_date on backupset (backup_start_date)
> >> go
> >>
> >> --backupmediaset
> >>
> >> Create
> >>
> >> index IX_backupmediaset_media_set_id on backupmediaset (media_set_id)
> >> go
> >>
> >> --backupfile
> >>
> >> Create
> >>
> >> index IX_backupfile_backup_set_id on backupfile (backup_set_id)
> >> go
> >>
> >> --backupmediafamily
> >>
> >> Create
> >>
> >> index IX_backupmediafamily_media_set_id on backupmediafamily
> >> (media_set_id)
> >> go
> >>
> >> --restorehistory
> >>
> >> Create
> >>
> >> index IX_restorehistory_restore_history_id on restorehistory
> >> (restore_history_id)
> >> go
> >>
> >> Create
> >>
> >> index IX_restorehistory_backup_set_id on restorehistory (backup_set_id)
> >> go
> >>
> >> --restorefile
> >>
> >> Create
> >>
> >> index IX_restorefile_restore_history_id on restorefile
> >> (restore_history_id)
> >> go
> >>
> >> --restorefilegroup
> >>
> >> Create
> >>
> >> index IX_restorefilegroup_restore_history_id on restorefilegroup
> >> (restore_history_id)
> >> go
> >>
> >>
> >>
> >> "cw" <cw@.3mc.com> wrote in message
> >> news:e2rWiunAHHA.3380@.TK2MSFTNGP04.phx.gbl...
> >> WHat would be the danger of adding a couple of indexes to the msdb
> >> system tables (particularly the backup/restore jobs tables)? I know
> >> that a hotfix or sp update may drop the tables, but if we are staying on
> >> sp3 or sp4 for now, would it cause a sytem wide issue?
> >>
> >> This is for SQL2000 on a Win2003 server.
> >>
> >> Thanks
> >>
> >>
> >>
> >
> >

MSDB database restore

I am trying to restore the msdb to another server. When I do, I get the
following message:
V-79-57344-33938 -
An error occurred on a query to database msdb.
V-79-57344-33938 - The backup of the system database on device
msdb_00__60f69b04_bfad_4b17_a284_4047d59b9e28_ cannot be restored because it
was created by a different version of the server (134218407) than this server
(134218262).
I understand what the message is saying but how do I find out what SPs and
hotfixes I need to install in order to get the target server to the correct
version? Is there a table or a document that will tell me what needs to be
installed on the target server?
Thanks
You can restore to a new version so as long as your up to the latest version
you should be OK. This generally isn't an issue when moving between service
pack and hot fixes so my guess is that you are trying to restore to a
pre-released version of SQL Server. Looking at the SELECT @.@.version output
should help determine what you are running.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Oren" <PaulOren@.discussions.microsoft.com> wrote in message
news:0C01112B-319F-4B14-8A49-F1EBAB72BF6A@.microsoft.com...
>I am trying to restore the msdb to another server. When I do, I get the
> following message:
> V-79-57344-33938 -
> An error occurred on a query to database msdb.
> V-79-57344-33938 - The backup of the system database on device
> msdb_00__60f69b04_bfad_4b17_a284_4047d59b9e28_ cannot be restored because
> it
> was created by a different version of the server (134218407) than this
> server
> (134218262).
> I understand what the message is saying but how do I find out what SPs and
> hotfixes I need to install in order to get the target server to the
> correct
> version? Is there a table or a document that will tell me what needs to
> be
> installed on the target server?
> Thanks
>
|||The backup I am restoring from is on tape so I can not get it restored in
order to run the the select statement. All my databases were loaded from MS
CDs so a pre-released version of SQL server should not be an issue.
"Roger Wolter[MSFT]" wrote:

> You can restore to a new version so as long as your up to the latest version
> you should be OK. This generally isn't an issue when moving between service
> pack and hot fixes so my guess is that you are trying to restore to a
> pre-released version of SQL Server. Looking at the SELECT @.@.version output
> should help determine what you are running.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Paul Oren" <PaulOren@.discussions.microsoft.com> wrote in message
> news:0C01112B-319F-4B14-8A49-F1EBAB72BF6A@.microsoft.com...
>
|||So apply SQL 2005 SP2 and you will assuredly be more recent that the tape
backup.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Oren" <PaulOren@.discussions.microsoft.com> wrote in message
news:339C5DF0-6C3A-4FDA-B0AF-F65D59CB7349@.microsoft.com...[vbcol=seagreen]
> The backup I am restoring from is on tape so I can not get it restored in
> order to run the the select statement. All my databases were loaded from
> MS
> CDs so a pre-released version of SQL server should not be an issue.
> "Roger Wolter[MSFT]" wrote:
|||We are running SQL 2000 not SQL 2005.
"Roger Wolter[MSFT]" wrote:

> So apply SQL 2005 SP2 and you will assuredly be more recent that the tape
> backup.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Paul Oren" <PaulOren@.discussions.microsoft.com> wrote in message
> news:339C5DF0-6C3A-4FDA-B0AF-F65D59CB7349@.microsoft.com...
>