database problem: msdb

Showing posts with label msdb. Show all posts

Tuesday, March 20, 2012

restoring msdb**

Hi
I restored msdb database successfully,
because of changing
the server and needed to restore all jobs
have been schaduled for periodical backup all
user batabases.
but when I want to change the text of step1
of every job to new drive in a new server
following error happened:
""
why? what's wrong?
any help would be greatly appreciated.RM,
Do you get an error number?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RM wrote:
> Hi
> I restored msdb database successfully,
> because of changing
> the server and needed to restore all jobs
> have been schaduled for periodical backup all
> user batabases.
> but when I want to change the text of step1
> of every job to new drive in a new server
> following error happened:
> ""
> why? what's wrong?
> any help would be greatly appreciated.|||Also check SQL server error log and SQLAgent log for complete information on this behaviour.
--
--
Satya SKJ
"Mark Allison" wrote:
> RM,
> Do you get an error number?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> RM wrote:
> > Hi
> >
> > I restored msdb database successfully,
> > because of changing
> > the server and needed to restore all jobs
> > have been schaduled for periodical backup all
> > user batabases.
> > but when I want to change the text of step1
> > of every job to new drive in a new server
> > following error happened:
> > ""
> >
> > why? what's wrong?
> > any help would be greatly appreciated.
>|||yeah,
error 14274
On Mon, 12 Jul 2004 11:32:16 +0100, Mark Allison
<marka@.no.tinned.meat.mvps.org> wrote:
> RM,
> Do you get an error number?
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> RM wrote:
>> Hi
>> I restored msdb database successfully,
>> because of changing
>> the server and needed to restore all jobs
>> have been schaduled for periodical backup all
>> user batabases.
>> but when I want to change the text of step1
>> of every job to new drive in a new server
>> following error happened:
>> ""
>> why? what's wrong?
>> any help would be greatly appreciated.|||RM, I don't know why your error message text is not showing but it
should be this:
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
This means that the server has been renamed and the originating_server
column in the sysjobs table is referencing the old server name. Update
sysjobs and it should be fine.
Use this proc to do the rename:
http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
This proc assumes that @.@.SERVERNAME is correct, you might want to check
that first, and correct it if it's wrong using sp_dropserver, sp_addserver
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RM wrote:
> yeah,
> error 14274
> On Mon, 12 Jul 2004 11:32:16 +0100, Mark Allison
> <marka@.no.tinned.meat.mvps.org> wrote:
>> RM,
>> Do you get an error number?
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>>
>> RM wrote:
>> Hi
>> I restored msdb database successfully,
>> because of changing
>> the server and needed to restore all jobs
>> have been schaduled for periodical backup all
>> user batabases.
>> but when I want to change the text of step1
>> of every job to new drive in a new server
>> following error happened:
>> ""
>> why? what's wrong?
>> any help would be greatly appreciated.
>|||thanks alot,
I was suprised ,very useful help**
On Tue, 13 Jul 2004 09:24:57 +0100, Mark Allison
<marka@.no.tinned.meat.mvps.org> wrote:
> RM, I don't know why your error message text is not showing but it
> should be this:
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> This means that the server has been renamed and the originating_server
> column in the sysjobs table is referencing the old server name. Update
> sysjobs and it should be fine.
> Use this proc to do the rename:
> http://sqldev.net/download/sqlagent/sp_sqlagent_rename.sql
> This proc assumes that @.@.SERVERNAME is correct, you might want to check
> that first, and correct it if it's wrong using sp_dropserver,
> sp_addserver
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> RM wrote:
>> yeah,
>> error 14274
>> On Mon, 12 Jul 2004 11:32:16 +0100, Mark Allison
>> <marka@.no.tinned.meat.mvps.org> wrote:
>> RM,
>> Do you get an error number?
>> -- Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>>
>> RM wrote:
>> Hi
>> I restored msdb database successfully,
>> because of changing
>> the server and needed to restore all jobs
>> have been schaduled for periodical backup all
>> user batabases.
>> but when I want to change the text of step1
>> of every job to new drive in a new server
>> following error happened:
>> ""
>> why? what's wrong?
>> any help would be greatly appreciated.
>>

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, HowardIf you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connect
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see the
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to connect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
> > Hi,
> >
> > I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> > backup database msdb to disk ='c:\msdb.dat'
> >
> > then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> > norecovery
> >
> > The restore seem to succeed. But I get the following messages:
> >
> > Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> > Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> > Server: Msg 927, Level 14, State 2, Line 1
> > Database 'msdb' cannot be opened. It is in the middle of a restore.
> > Server: Msg 3009, Level 16, State 3, Line 1
> > Could not insert a backup or restore history/detail record in the msdb
> > database. This may indicate a problem with the msdb database. The
> > backup/restore operation was still successful.
> > RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> > MB/sec).
> >
> > These messages aren't generated with SQL2005, nor do you see the messages if
> > you specify 'with recovery' on the restore statement.
> >
> > I would be appreciative if someone could explain these messages. If I ignore
> > them, it is still possible to recover the database (restore database msdb
> > with recovery).
> >
> > Thanks, Howard
> >
> >
> >
> >
> >
> >|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode.
So you may still need to restore the transaction log to a PIT after restoring
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and this
type of message makes them nervous.
Thanks again, Howard|||This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
possible. One could argue that SQL server would be smart enough to produce only a warning or similar
in these situations, of course. Perhaps you want to file an entry at
http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
enough to warrant any major effort... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> Hi Ben,
> Thanks for your quick reply. I did consider using 'with recovery' on the
> restore statement. The trouble is that msdb does permit 'full' recovery mode.
> So you may still need to restore the transaction log to a PIT after restoring
> the database. Also, I understand that the msdb was indeed restored despite
> the error message. What I really want to know is what to make of the error
> message as I work with a program that deals with other people's data and this
> type of message makes them nervous.
> Thanks again, Howard
>|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:
> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
> possible. One could argue that SQL server would be smart enough to produce only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> > Hi Ben,
> >
> > Thanks for your quick reply. I did consider using 'with recovery' on the
> > restore statement. The trouble is that msdb does permit 'full' recovery mode.
> > So you may still need to restore the transaction log to a PIT after restoring
> > the database. Also, I understand that the msdb was indeed restored despite
> > the error message. What I really want to know is what to make of the error
> > message as I work with a program that deals with other people's data and this
> > type of message makes them nervous.
> >
> > Thanks again, Howard
> >
> >
>
>|||> As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005.
Cool. I didn't know that. So MS did spend some time on this. Thanks for the update. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2FA4EC15-34D7-41FA-867F-8DC825A67414@.microsoft.com...
> Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
> to get an official response from Microsoft because at least on of my
> customers may be expecting it.
> "Tibor Karaszi" wrote:
>> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
>> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing
>> isn't
>> possible. One could argue that SQL server would be smart enough to produce only a warning or
>> similar
>> in these situations, of course. Perhaps you want to file an entry at
>> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
>> enough to warrant any major effort... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Howard" <Howard@.discussions.microsoft.com> wrote in message
>> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>> > Hi Ben,
>> >
>> > Thanks for your quick reply. I did consider using 'with recovery' on the
>> > restore statement. The trouble is that msdb does permit 'full' recovery mode.
>> > So you may still need to restore the transaction log to a PIT after restoring
>> > the database. Also, I understand that the msdb was indeed restored despite
>> > the error message. What I really want to know is what to make of the error
>> > message as I work with a program that deals with other people's data and this
>> > type of message makes them nervous.
>> >
>> > Thanks again, Howard
>> >
>> >
>>

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, HowardIf you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connec
t
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:

> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I igno
re
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see t
he
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to conn
ect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
>|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode
.
So you may still need to restore the transaction log to a PIT after restorin
g
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and thi
s
type of message makes them nervous.
Thanks again, Howard|||This is a kind of catch-22 situation. Each RESTORE want to write to the rest
ore history tables in
msdb. Since these aren't available (yet) when you restore msdb, that restore
history writing isn't
possible. One could argue that SQL server would be smart enough to produce o
nly a warning or similar
in these situations, of course. Perhaps you want to file an entry at
http://connect.microsoft.com/sqlserver for this... Probably MS didn't find t
his happening often
enough to warrant any major effort... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> Hi Ben,
> Thanks for your quick reply. I did consider using 'with recovery' on the
> restore statement. The trouble is that msdb does permit 'full' recovery mo
de.
> So you may still need to restore the transaction log to a PIT after restor
ing
> the database. Also, I understand that the msdb was indeed restored despit
e
> the error message. What I really want to know is what to make of the error
> message as I work with a program that deals with other people's data and t
his
> type of message makes them nervous.
> Thanks again, Howard
>|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and tr
y
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:

> This is a kind of catch-22 situation. Each RESTORE want to write to the re
store history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that resto
re history writing isn't
> possible. One could argue that SQL server would be smart enough to produce
only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find
this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>
>|||> As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005.
Cool. I didn't know that. So MS did spend some time on this. Thanks for the
update. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2FA4EC15-34D7-41FA-867F-8DC825A67414@.microsoft.com...[vbcol=seagreen]
> Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005. I think I'll take your suggestion and
try
> to get an official response from Microsoft because at least on of my
> customers may be expecting it.
> "Tibor Karaszi" wrote:
>

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.
Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.
|||We had already changed the name of the new server to what the server
name was. So this shouldn't apply to my problem. However, if I
restored msdb while SQL Server Agent service was still running, would
this have caused the problem?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.|||We had already changed the name of the new server to what the server
name was. So this shouldn't apply to my problem. However, if I
restored msdb while SQL Server Agent service was still running, would
this have caused the problem?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restoring MSDB to another server

We moved a server from one box to another. I restored
MSDB to the new server and some of the jobs didn't run as
scheduled. What should I have done to avoid this? It
appears the timing was thrown off. Any experience with
this shared would be appreciated.
Thanks.Hi,
1. Stop the SQL Agent service
2. Since you restored the MSDB from a differnt server you need to update
the SYSJOBS table wth current sql server name,
Because the servername in the table will be source server and all the jobs
will fail.
How to update:-
UPDATE MSDB..SYSJOBS
SET Originating Server = @.@.Servername
WHERE Originating Server <> @.@.Servername
3. start sql agent service
Thanks
Hari
MCDBA
"metoonyc" <metoonyc
"Sandi" <sandra_richardson@.harvardpilgrim.org> wrote in message
news:bc8f01c4895a$65be0370$a401280a@.phx.gbl...
> We moved a server from one box to another. I restored
> MSDB to the new server and some of the jobs didn't run as
> scheduled. What should I have done to avoid this? It
> appears the timing was thrown off. Any experience with
> this shared would be appreciated.
> Thanks.

restoring msdb of different server

Hi
Evironment: SQL Server 2000 SP3a
I want to restore master & msdb (which transfers login,
settings, jobs etc.) of old server to the new server. What
are the possible issues with this approach, if any? Is
UPDATE msdb.dbo.sysjobs
SET originating_server = 'NewServerName'
WHERE originating_server = 'OldServerName'
enough in case of msdb?
The old server is Windows 2000 Advanced Server SP4, HP
Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
with Ultra SCSI 320 15k RPM disks attached. The new server
is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
app/OS RAM, the same RAID setup as in the old server.
I haven't changed any SQL Server settings on the old
server, except CPU affinity mask to use just one virtual
CPU on each of the physical CPUs and max. degree of
paralellism to 1.
Is SQL Server Setup aware of more advanced hardware of the
new server to such an extent that it can automatically
produce more optimal SQL server settings (stored in master)
than they were produced for the old server?
I.e. will the auto-generated settings in master database
from old server hurt performance on the new server?
--
Many, many thanks,
OskSee if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"Osk" wrote:
> Hi
> Evironment: SQL Server 2000 SP3a
> I want to restore master & msdb (which transfers login,
> settings, jobs etc.) of old server to the new server. What
> are the possible issues with this approach, if any? Is
> UPDATE msdb.dbo.sysjobs
> SET originating_server = 'NewServerName'
> WHERE originating_server = 'OldServerName'
> enough in case of msdb?
> The old server is Windows 2000 Advanced Server SP4, HP
> Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
> Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
> and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
> with Ultra SCSI 320 15k RPM disks attached. The new server
> is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
> with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
> app/OS RAM, the same RAID setup as in the old server.
> I haven't changed any SQL Server settings on the old
> server, except CPU affinity mask to use just one virtual
> CPU on each of the physical CPUs and max. degree of
> paralellism to 1.
> Is SQL Server Setup aware of more advanced hardware of the
> new server to such an extent that it can automatically
> produce more optimal SQL server settings (stored in master)
> than they were produced for the old server?
> I.e. will the auto-generated settings in master database
> from old server hurt performance on the new server?
>
> --
> Many, many thanks,
> Osk
>

restoring msdb of different server

Hi
Evironment: SQL Server 2000 SP3a
I want to restore master & msdb (which transfers login,
settings, jobs etc.) of old server to the new server. What
are the possible issues with this approach, if any? Is
UPDATE msdb.dbo.sysjobs
SET originating_server = 'NewServerName'
WHERE originating_server = 'OldServerName'
enough in case of msdb?
The old server is Windows 2000 Advanced Server SP4, HP
Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
with Ultra SCSI 320 15k RPM disks attached. The new server
is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
app/OS RAM, the same RAID setup as in the old server.
I haven't changed any SQL Server settings on the old
server, except CPU affinity mask to use just one virtual
CPU on each of the physical CPUs and max. degree of
paralellism to 1.
Is SQL Server Setup aware of more advanced hardware of the
new server to such an extent that it can automatically
produce more optimal SQL server settings (stored in master)
than they were produced for the old server?
I.e. will the auto-generated settings in master database
from old server hurt performance on the new server?
Many, many thanks,
Osk
See if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"Osk" wrote:

> Hi
> Evironment: SQL Server 2000 SP3a
> I want to restore master & msdb (which transfers login,
> settings, jobs etc.) of old server to the new server. What
> are the possible issues with this approach, if any? Is
> UPDATE msdb.dbo.sysjobs
> SET originating_server = 'NewServerName'
> WHERE originating_server = 'OldServerName'
> enough in case of msdb?
> The old server is Windows 2000 Advanced Server SP4, HP
> Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
> Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
> and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
> with Ultra SCSI 320 15k RPM disks attached. The new server
> is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
> with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
> app/OS RAM, the same RAID setup as in the old server.
> I haven't changed any SQL Server settings on the old
> server, except CPU affinity mask to use just one virtual
> CPU on each of the physical CPUs and max. degree of
> paralellism to 1.
> Is SQL Server Setup aware of more advanced hardware of the
> new server to such an extent that it can automatically
> produce more optimal SQL server settings (stored in master)
> than they were produced for the old server?
> I.e. will the auto-generated settings in master database
> from old server hurt performance on the new server?
>
> --
> Many, many thanks,
> Osk
>

restoring msdb of different server

Hi
Evironment: SQL Server 2000 SP3a
I want to restore master & msdb (which transfers login,
settings, jobs etc.) of old server to the new server. What
are the possible issues with this approach, if any? Is
UPDATE msdb.dbo.sysjobs
SET originating_server = 'NewServerName'
WHERE originating_server = 'OldServerName'
enough in case of msdb?
The old server is Windows 2000 Advanced Server SP4, HP
Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
with Ultra SCSI 320 15k RPM disks attached. The new server
is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
app/OS RAM, the same RAID setup as in the old server.
I haven't changed any SQL Server settings on the old
server, except CPU affinity mask to use just one virtual
CPU on each of the physical CPUs and max. degree of
paralellism to 1.
Is SQL Server Setup aware of more advanced hardware of the
new server to such an extent that it can automatically
produce more optimal SQL server settings (stored in master)
than they were produced for the old server?
I.e. will the auto-generated settings in master database
from old server hurt performance on the new server?
Many, many thanks,
OskSee if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Osk" wrote:

> Hi
> Evironment: SQL Server 2000 SP3a
> I want to restore master & msdb (which transfers login,
> settings, jobs etc.) of old server to the new server. What
> are the possible issues with this approach, if any? Is
> UPDATE msdb.dbo.sysjobs
> SET originating_server = 'NewServerName'
> WHERE originating_server = 'OldServerName'
> enough in case of msdb?
> The old server is Windows 2000 Advanced Server SP4, HP
> Proliant DL 380 G3 with 2 x 2.4 GHz Intel Xeon
> Hyper-Threading CPUs, 4GB RAM, of which 2GB is for apps,
> and HP SmartArray 5300/256 Ultra SCSI 320 RAID controller
> with Ultra SCSI 320 15k RPM disks attached. The new server
> is Windows 2003 Server Standart SP1, HP ProLiant DL 380 G4
> with 2 x 3.6 GHz Intel Xeon Hyper-Threading CPUs, 3.5 GB of
> app/OS RAM, the same RAID setup as in the old server.
> I haven't changed any SQL Server settings on the old
> server, except CPU affinity mask to use just one virtual
> CPU on each of the physical CPUs and max. degree of
> paralellism to 1.
> Is SQL Server Setup aware of more advanced hardware of the
> new server to such an extent that it can automatically
> produce more optimal SQL server settings (stored in master)
> than they were produced for the old server?
> I.e. will the auto-generated settings in master database
> from old server hurt performance on the new server?
>
> --
> Many, many thanks,
> Osk
>

restoring msdb

I need some data from msdb.dbo.sysjobs and msdb.dbo.sysjobhistory but I need it from a backup file.

I don't need to restore the msdb database, I just need some data from the backup. Everytime I try to restore it under some innocuous name like bills_db it tells me msdb is in use.

Is there a way to restore this db to some other name and retrieve data from it?I never really had to do this but I imagine your sql server is getting pissed because you are trying to restore over the current msdb.

if I was you, I would create a blank database named something other than msdb and look at the WITH REPLACE argument of the RESTORE statement in books online.|||Rather that WITH REPLACE, look at WITH MOVE:

restore database msdb_copy from disk = '<disk location>' WITH
MOVE 'MSDBData' to '<different physical file name>.mdb'
,MOVE 'MSDBLog' to '<different physical file name>.ldf'
,stats

Restoring msdb

Hi,
My system went down and I had to reinstall the O/S and of
course SQL Server and MSDE. Anyhow after that saga, I
finally have SQL Server running again with another
Instance called <computername>\VSDOTNET
I have backups of all dbs from prior to the crash, but I
am having trouble restoring them, especially the msdb
because it contains my DTS packages which I need
desperately!
Problem is when I attempt to restore the msdb it fails
and tells me it can't do it b/c the backup was created
with a different version of the server.
I've tried to Force restore over existing database with
no luck. Any suggestions please?
Thank you.
JasonMake sure that the service pack level for your reinstalled system is the same as the one you had
when the prior install crashed. SQL Server cannot go between even service packs for system
databases.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason" <shogun1972@.hotmail.com> wrote in message news:051401c3b7e2$01590910$a501280a@.phx.gbl...
> Hi,
> My system went down and I had to reinstall the O/S and of
> course SQL Server and MSDE. Anyhow after that saga, I
> finally have SQL Server running again with another
> Instance called <computername>\VSDOTNET
> I have backups of all dbs from prior to the crash, but I
> am having trouble restoring them, especially the msdb
> because it contains my DTS packages which I need
> desperately!
> Problem is when I attempt to restore the msdb it fails
> and tells me it can't do it b/c the backup was created
> with a different version of the server.
> I've tried to Force restore over existing database with
> no luck. Any suggestions please?
> Thank you.
> Jason

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
ChristofferYou can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.
> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
>> You can view the physical files from the backup using
>> restore filelistonly.
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>> If you need to have the files in
>> another location, use the move option in the restore script
>> to specify the physical file locations.
>No, You cannot use the move option with system databases.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
>> Yes you can use with move. You can follow the example in the
>> following knowledge base article:
>> http://support.microsoft.com/?id=304692
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
Christoffer
You can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.

> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.
|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.4ax.com.. .
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>No, You cannot use the move option with system databases.
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.
|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.4ax.com.. .
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.
|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Restoring model and msdb databases goes to wrong file locations

Hi,
I am working on a script which should be able to restore a full sql server
2000 automatically. When I do a full restore of the system databases the
file locations of the mdf and ldf files don't change but when I restore the
differential backup the ldf file places it self in the default directory of
Microsoft SQL Server.
So my script looks like this:
restore database model from disk = 'c:\backup\model.bkf' with norecovery,
replace
go
restore database model from disk ='c:\backup\modeldiff.bkf' with
recovery,replace
go
The model log file should go into "e:\database\data\" but instead it goes
into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
why does it work fine when I do like this:
restore database model from disk = 'c:\backup\model.bkf' with recovery,
replace
but not when I want to do a diff restore?
I know I can detach and attach the model database, but then I also have to
stop and start the database with a trace flag - not very pretty...
Any ideas?
ChristofferYou can view the physical files from the backup using
restore filelistonly. If you need to have the files in
another location, use the move option in the restore script
to specify the physical file locations.
-Sue
On Tue, 17 May 2005 16:49:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>Hi,
>I am working on a script which should be able to restore a full sql server
>2000 automatically. When I do a full restore of the system databases the
>file locations of the mdf and ldf files don't change but when I restore the
>differential backup the ldf file places it self in the default directory of
>Microsoft SQL Server.
>So my script looks like this:
>restore database model from disk = 'c:\backup\model.bkf' with norecovery,
>replace
>go
>restore database model from disk ='c:\backup\modeldiff.bkf' with
>recovery,replace
>go
>The model log file should go into "e:\database\data\" but instead it goes
>into "C:\Program Files\Microsoft SQL Server\MSSQL\Data"
>why does it work fine when I do like this:
>restore database model from disk = 'c:\backup\model.bkf' with recovery,
>replace
>but not when I want to do a diff restore?
>I know I can detach and attach the model database, but then I also have to
>stop and start the database with a trace flag - not very pretty...
>
>Any ideas?
>Christoffer
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.
4ax.com...
> You can view the physical files from the backup using
> restore filelistonly.
Correct, I can view the paths with restore filelistonly and it shows the
correct file locations.

> If you need to have the files in
> another location, use the move option in the restore script
> to specify the physical file locations.
No, You cannot use the move option with system databases.|||Yes you can use with move. You can follow the example in the
following knowledge base article:
http://support.microsoft.com/?id=304692
-Sue
On Wed, 18 May 2005 08:55:37 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:47dl81t4qahcv4l8gjbrb6c8oo934vl09p@.
4ax.com...
>Correct, I can view the paths with restore filelistonly and it shows the
>correct file locations.
>
>No, You cannot use the move option with system databases.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.
4ax.com...
> Yes you can use with move. You can follow the example in the
> following knowledge base article:
> http://support.microsoft.com/?id=304692
>
In the article is mentioned the following:
NOTE: These instructions in this article do not apply to SQL Server 2000.
The response from the server is :
tempdb is skipped. You cannot run a query that requires tempdb.|||One of the trace flags settings is different on 2000 if you
are following all of the steps but you can restore with
move. Follow the trace flags in the following article or
just use attach/detach as the article does:
http://support.microsoft.com/?id=224071
-Sue
On Wed, 18 May 2005 15:35:57 +0200, "Christoffer B."
<StofferB@.online.nospam> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:3s9m811b194jc1qs0v74kugnhqcfg2c4lo@.
4ax.com...
>In the article is mentioned the following:
>NOTE: These instructions in this article do not apply to SQL Server 2000.
>The response from the server is :
>tempdb is skipped. You cannot run a query that requires tempdb.
>|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:otpn81df0cncebavp40flr7v071i8nn22b@.
4ax.com...
> One of the trace flags settings is different on 2000 if you
> are following all of the steps but you can restore with
> move. Follow the trace flags in the following article or
> just use attach/detach as the article does:
> http://support.microsoft.com/?id=224071
I can still not do a restore with a move as described in my first posting.
if I am going to use detach/attach then we are back to my first posting..
which I want to avoid.|||Hi Christoffer,
As Sue said, I am afraid we will have to use detach and attach in this
scenario. I wanted to post a quick note to see if there is anything more I
could help you on this topic.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Monday, March 12, 2012

Restoring Master DB..

Could anyone guide me how to restore Master, msdb to different server from the backup files.
thanks in advance.Why would you want to?

I would imagine it'd be like any other database...|||Infact we are moving all our database server stuff to newly purchased hardware..
i can script all logins and regenerate it in the new server, but i wanted to know the process of restore or detach/attach options for master database to different server..
thanks for your reply.|||If you retain the server name, then using -m from command line with SQLSERVR would alow you to restore master. MSDB is even easier than that, just restore, after you stop SQLAgent.

Restoring Master Database...

Hello All,
I am trying to test my master and MSDB restore actvities.
1. When I try to run SQL Server on single user mode(DOS prompt command
sqlservr.exe -m) it is taking forever... If the SQL Server is started in
single user mode will I get the DOS prompt again? It it normal that
launching SQL Server in single user mode is taking time?
2. After launching the server in single user mode, the next step is
launching EM and restore the master database like any other user database
restore? OR any special sequence is required.
3. To restore the MSDB should the SQL Server be running in single user mode?
SQL 2K.
Thank you very much.
Johnson
1. Try using: sqlservr.exe -c -m
It's not normal to take a long time but it depends on your
definition of a long time. The output to the screen should
give you an idea of what is taking how long. You won't get a
second DOS screen.
2. Yes but SQL Server will shut down after you restore the
master database.
You can find the steps in books online under the topic:
Restoring the master Database from a Current Backup
3. No...but you can't restore a database that is being
accessed so make sure the SQL Server Agent isn't running.
Refer to the article in books online:
Restoring the model, msdb, and distribution Databases
-Sue
On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>Hello All,
>I am trying to test my master and MSDB restore actvities.
>1. When I try to run SQL Server on single user mode(DOS prompt command
>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>single user mode will I get the DOS prompt again? It it normal that
>launching SQL Server in single user mode is taking time?
>2. After launching the server in single user mode, the next step is
>launching EM and restore the master database like any other user database
>restore? OR any special sequence is required.
>3. To restore the MSDB should the SQL Server be running in single user mode?
>SQL 2K.
>Thank you very much.
>Johnson
>
|||Hi,
(1)From command prompt switch to the approprite directory and use this
sqlservr.exe -c -m
It is normal it take long time
if would be better firt stop the services and start services again
(2)Yes sql server has to be restarted so that new setting should take
place.
(3)To restore the master database just stop all the serveices and
detach the database and rename the database mdf file name and copy the
backed up msdb database in the location and attach the database agin.
Hope this help u
from
killer
Sue Hoegemeier wrote:[vbcol=seagreen]
> 1. Try using: sqlservr.exe -c -m
> It's not normal to take a long time but it depends on your
> definition of a long time. The output to the screen should
> give you an idea of what is taking how long. You won't get a
> second DOS screen.
> 2. Yes but SQL Server will shut down after you restore the
> master database.
> You can find the steps in books online under the topic:
> Restoring the master Database from a Current Backup
> 3. No...but you can't restore a database that is being
> accessed so make sure the SQL Server Agent isn't running.
> Refer to the article in books online:
> Restoring the model, msdb, and distribution Databases
> -Sue
> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
|||Thank you very much for the response.
I understand that I would not get the second DOS screen. I think I should
have made my question very clear.
After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
to know that SQL Server agent is started in the single user mode. It did
display some text after executing the above command. How I will come to know
it finished the job of starting SQL Server in single user mode? Will I get
the DOS prompt again in the same DOS shell where I executed the line
sqlservr.exe -c -m?
Thanks,
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com...
> 1. Try using: sqlservr.exe -c -m
> It's not normal to take a long time but it depends on your
> definition of a long time. The output to the screen should
> give you an idea of what is taking how long. You won't get a
> second DOS screen.
> 2. Yes but SQL Server will shut down after you restore the
> master database.
> You can find the steps in books online under the topic:
> Restoring the master Database from a Current Backup
> 3. No...but you can't restore a database that is being
> accessed so make sure the SQL Server Agent isn't running.
> Refer to the article in books online:
> Restoring the model, msdb, and distribution Databases
> -Sue
> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>
|||First, it should be SQL Server not SQ Agent that you are
starting up. If you read the output on the screen, it should
display lines something like:
warning *****
SQL Server started in single user mode
starting up database master
and then other lines for starting up your other databases,
and you will see a line when it is ready for client
connections although other databases may start up after
that. And then it just sits so if you aren't used to doing
this, it may seem like it is hung. You just having to get
used to doing it or practice with a server with few, if any
user databases. Then you can tell by looking at what
databases have started up.
-Sue
On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>Thank you very much for the response.
>I understand that I would not get the second DOS screen. I think I should
>have made my question very clear.
>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
>to know that SQL Server agent is started in the single user mode. It did
>display some text after executing the above command. How I will come to know
>it finished the job of starting SQL Server in single user mode? Will I get
>the DOS prompt again in the same DOS shell where I executed the line
>sqlservr.exe -c -m?
>Thanks,
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com.. .
>
|||Oh and look for the last line of Recovery complete.
-Sue
On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>Thank you very much for the response.
>I understand that I would not get the second DOS screen. I think I should
>have made my question very clear.
>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
>to know that SQL Server agent is started in the single user mode. It did
>display some text after executing the above command. How I will come to know
>it finished the job of starting SQL Server in single user mode? Will I get
>the DOS prompt again in the same DOS shell where I executed the line
>sqlservr.exe -c -m?
>Thanks,
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.4ax.com.. .
>
|||That means If I see the message recovery complete I am ready to connect
through the EM and do my master database restore? SQL Server agent will
never start when I issue the sqlservr.exe -c -m? Even though SQL Server
agent is not running I should not have any problem to connect through the
EM?
Thanks for your quick reply.
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.4ax.com...
> Oh and look for the last line of Recovery complete.
> -Sue
> On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>
|||I am not finding this info in BOL.
As Sue said it has to be by experience. Very strange as how one will guess
like this way!!!
Thanks,
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.4ax.com...
> Oh and look for the last line of Recovery complete.
> -Sue
> On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>
|||Yes.
Just disable the Agent service while you do your restores
and it will make life easier.
-Sue
On Mon, 15 Aug 2005 22:11:26 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>That means If I see the message recovery complete I am ready to connect
>through the EM and do my master database restore? SQL Server agent will
>never start when I issue the sqlservr.exe -c -m? Even though SQL Server
>agent is not running I should not have any problem to connect through the
>EM?
>Thanks for your quick reply.
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.4ax.com.. .
>
|||Every detail of it isn't in BOL. Scroll down in the DOS
screen and look for recovery complete. What you see on the
screen is just what is written to the SQL log - same thing
you see if you look in the errorlog.
-Sue
On Mon, 15 Aug 2005 23:02:30 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>I am not finding this info in BOL.
>As Sue said it has to be by experience. Very strange as how one will guess
>like this way!!!
>Thanks,
>Johnson
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.4ax.com.. .
>

Restoring Master Database...

Hello All,
I am trying to test my master and MSDB restore actvities.
1. When I try to run SQL Server on single user mode(DOS prompt command
sqlservr.exe -m) it is taking forever... If the SQL Server is started in
single user mode will I get the DOS prompt again? It it normal that
launching SQL Server in single user mode is taking time?
2. After launching the server in single user mode, the next step is
launching EM and restore the master database like any other user database
restore? OR any special sequence is required.
3. To restore the MSDB should the SQL Server be running in single user mode?
SQL 2K.
Thank you very much.
Johnson1. Try using: sqlservr.exe -c -m
It's not normal to take a long time but it depends on your
definition of a long time. The output to the screen should
give you an idea of what is taking how long. You won't get a
second DOS screen.
2. Yes but SQL Server will shut down after you restore the
master database.
You can find the steps in books online under the topic:
Restoring the master Database from a Current Backup
3. No...but you can't restore a database that is being
accessed so make sure the SQL Server Agent isn't running.
Refer to the article in books online:
Restoring the model, msdb, and distribution Databases
-Sue
On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>Hello All,
>I am trying to test my master and MSDB restore actvities.
>1. When I try to run SQL Server on single user mode(DOS prompt command
>sqlservr.exe -m) it is taking forever... If the SQL Server is started in
>single user mode will I get the DOS prompt again? It it normal that
>launching SQL Server in single user mode is taking time?
>2. After launching the server in single user mode, the next step is
>launching EM and restore the master database like any other user database
>restore? OR any special sequence is required.
>3. To restore the MSDB should the SQL Server be running in single user mode
?
>SQL 2K.
>Thank you very much.
>Johnson
>|||Hi,
(1)From command prompt switch to the approprite directory and use this
sqlservr.exe -c -m
It is normal it take long time
if would be better firt stop the services and start services again
(2)Yes sql server has to be restarted so that new setting should take
place.
(3)To restore the master database just stop all the serveices and
detach the database and rename the database mdf file name and copy the
backed up msdb database in the location and attach the database agin.
Hope this help u
from
killer
Sue Hoegemeier wrote:[vbcol=seagreen]
> 1. Try using: sqlservr.exe -c -m
> It's not normal to take a long time but it depends on your
> definition of a long time. The output to the screen should
> give you an idea of what is taking how long. You won't get a
> second DOS screen.
> 2. Yes but SQL Server will shut down after you restore the
> master database.
> You can find the steps in books online under the topic:
> Restoring the master Database from a Current Backup
> 3. No...but you can't restore a database that is being
> accessed so make sure the SQL Server Agent isn't running.
> Refer to the article in books online:
> Restoring the model, msdb, and distribution Databases
> -Sue
> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>|||Thank you very much for the response.
I understand that I would not get the second DOS screen. I think I should
have made my question very clear.
After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
to know that SQL Server agent is started in the single user mode. It did
display some text after executing the above command. How I will come to know
it finished the job of starting SQL Server in single user mode? Will I get
the DOS prompt again in the same DOS shell where I executed the line
sqlservr.exe -c -m?
Thanks,
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.
4ax.com...
> 1. Try using: sqlservr.exe -c -m
> It's not normal to take a long time but it depends on your
> definition of a long time. The output to the screen should
> give you an idea of what is taking how long. You won't get a
> second DOS screen.
> 2. Yes but SQL Server will shut down after you restore the
> master database.
> You can find the steps in books online under the topic:
> Restoring the master Database from a Current Backup
> 3. No...but you can't restore a database that is being
> accessed so make sure the SQL Server Agent isn't running.
> Refer to the article in books online:
> Restoring the model, msdb, and distribution Databases
> -Sue
> On Mon, 15 Aug 2005 19:27:50 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>
>|||First, it should be SQL Server not SQ Agent that you are
starting up. If you read the output on the screen, it should
display lines something like:
warning *****
SQL Server started in single user mode
starting up database master
and then other lines for starting up your other databases,
and you will see a line when it is ready for client
connections although other databases may start up after
that. And then it just sits so if you aren't used to doing
this, it may seem like it is hung. You just having to get
used to doing it or practice with a server with few, if any
user databases. Then you can tell by looking at what
databases have started up.
-Sue
On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>Thank you very much for the response.
>I understand that I would not get the second DOS screen. I think I should
>have made my question very clear.
>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
>to know that SQL Server agent is started in the single user mode. It did
>display some text after executing the above command. How I will come to kno
w
>it finished the job of starting SQL Server in single user mode? Will I get
>the DOS prompt again in the same DOS shell where I executed the line
>sqlservr.exe -c -m?
>Thanks,
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.
4ax.com...
>|||Oh and look for the last line of Recovery complete.
-Sue
On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>Thank you very much for the response.
>I understand that I would not get the second DOS screen. I think I should
>have made my question very clear.
>After entering sqlservr.exe -c -m in the DOS prompt how exactly I will come
>to know that SQL Server agent is started in the single user mode. It did
>display some text after executing the above command. How I will come to kno
w
>it finished the job of starting SQL Server in single user mode? Will I get
>the DOS prompt again in the same DOS shell where I executed the line
>sqlservr.exe -c -m?
>Thanks,
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:nkl2g19i0oreq3k21g44hku5vs0n0rnm43@.
4ax.com...
>|||That means If I see the message recovery complete I am ready to connect
through the EM and do my master database restore? SQL Server agent will
never start when I issue the sqlservr.exe -c -m? Even though SQL Server
agent is not running I should not have any problem to connect through the
EM?
Thanks for your quick reply.
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.
4ax.com...
> Oh and look for the last line of Recovery complete.
> -Sue
> On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>
>|||I am not finding this info in BOL.
As Sue said it has to be by experience. Very strange as how one will guess
like this way!!!
Thanks,
Johnson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.
4ax.com...
> Oh and look for the last line of Recovery complete.
> -Sue
> On Mon, 15 Aug 2005 21:30:09 -0700, "Johnson Smith"
> <JJSmith@.hotmail.com> wrote:
>
>|||Yes.
Just disable the Agent service while you do your restores
and it will make life easier.
-Sue
On Mon, 15 Aug 2005 22:11:26 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>That means If I see the message recovery complete I am ready to connect
>through the EM and do my master database restore? SQL Server agent will
>never start when I issue the sqlservr.exe -c -m? Even though SQL Server
>agent is not running I should not have any problem to connect through the
>EM?
>Thanks for your quick reply.
>Johnson
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.
4ax.com...
>|||Every detail of it isn't in BOL. Scroll down in the DOS
screen and look for recovery complete. What you see on the
screen is just what is written to the SQL log - same thing
you see if you look in the errorlog.
-Sue
On Mon, 15 Aug 2005 23:02:30 -0700, "Johnson Smith"
<JJSmith@.hotmail.com> wrote:

>I am not finding this info in BOL.
>As Sue said it has to be by experience. Very strange as how one will guess
>like this way!!!
>Thanks,
>Johnson
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:s5s2g11la0rs694mq27u4qtgavhf192ad8@.
4ax.com...
>

Tuesday, March 20, 2012

restoring msdb**

restoring msdb**

restoring msdb**

restoring msdb with SQL 2000

restoring msdb with SQL 2000

restoring msdb with SQL 2000

Restoring MSDB to another server

Restoring MSDB to another server

Restoring MSDB to another server

restoring msdb of different server

restoring msdb of different server

restoring msdb of different server

restoring msdb

Restoring msdb

Restoring model and msdb databases goes to wrong file locations

Restoring model and msdb databases goes to wrong file locations

Restoring model and msdb databases goes to wrong file locations

Monday, March 12, 2012

Restoring Master DB..

Restoring Master Database...

Restoring Master Database...

database problem

Blog Archive

About Me