MSDE and Enterprise Manager?

I am a complete sql newbie! For the life of me I can't figure out how to get Enterprise Manager to connect with MSDE. Something about mixed mode or tcp/ip settings? But no one's ever really clear on how to do these things. I've uninstalled MSDE until I can get more info.

I just need a quick walk-through on how to install msde to work with EM. This is what I used when MSDE requested a strong password:

setup INSTANCENAME="InstanceName" SECURITYMODE=SQL SAPWD="AStrongSAPwd"

Is this correct? Any help would be appreciated.

Thanks.

You access it by address or servername + \ + Instance name:

Server\MSDEInstanceName

|||

What part are you having a problem with?

Tell EM to connect to the server\instancename using sql authentication login sa, password AStrongPWD.

|||If you have the full SQL Server installed then you install the MSDE as a named instance and right click at the top of the Enterprise manager of the full version to register the MSDE and you can manage it directly. If you have the MSDE installed then you install the full version as a named instance and repeat the above. I have registered 68 SQL Server 7/2000 in one XP box so if you have more questions post again. Hope this helps.|||

Thanks to everyone for their help.

I had the eval version of 2000 installed but it's expired. I would now like to use EM with MSDE to connect to the database on my website. I re-installed MSDE but it's only showing the local database.

When I try to register my online domain (ip address) it fails. Says sql may not be running. But it is running. I know it's something basic and silly I'm not doing, but I'm still very new to sql. Is there any other very basic step I may be missing?

Again, thanks for everyone's help...sorry for all the newbie questions.

|||

Which is probably correct because your SQL Server service maybe off, the developer edition is $40 in the link below but you could get it for less because when you are using hosting company you need Enterprise manager. Hope this helps.

http://www.provantage.com/buy-22053391-microsoft-backoffice-sql-server-2000-developer-edition-shopping.htm

MSDE 2000 backups and restores

Hi All
I am new to MSDE (from access)
I have chosen simple recovery mode for MSDE Database and do a full backup
each day then subsequent backups within the same day using differential
backups
The problem is I don't really understand how the differential backup should
work
1. Do you use a different file name for the Full and Differential backups?
e.g
BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcars.bak' WITH INIT,
NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT, NAME = 'Tramcars Diff Backup'
GO
BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcarsdiff.bak' WITH INIT,
differential,
NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT,
NAME = 'Tramcars Diff Backup'
GO
2 Do you use different file names for each differential backups during the
day or append each diff backup to the previous?
Because the literature shows TSQL code for Restoring includes reference to a
File = 1 (or 2 etc)
Regards
Steve
hi Steve,
> The problem is I don't really understand how the differential backup
> should work
> 1. Do you use a different file name for the Full and Differential
> backups? e.g
> BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcars.bak' WITH
> INIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT, NAME = 'Tramcars Diff
> Backup'
> GO
> BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcarsdiff.bak'
> WITH INIT, differential,
> NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT,
> NAME = 'Tramcars Diff Backup'
> GO
correct... use 2 different files OR "init" the backup set with the full
backup and append to it the successive differential..
if you choos to have only 1 backup file, when restoring you have to specify
the
RESTORE DATABASE [xxx] FROM DISK = N'C:\xxxx.bak'
WITH FILE = n ,
......
where n is 1 for the very first (complete full backup) and then apply the
differential
WITH FILE = n , -- where n=2

> 2 Do you use different file names for each differential backups
> during the day or append each diff backup to the previous?
> Because the literature shows TSQL code for Restoring includes
> reference to a File = 1 (or 2 etc)
this is up to you... on how you "store" backup sets, how long you have to
store them, your internal policies, the actual media hosting the backups...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
Again thanks for your reliable input
Steve
Regards
Steve
"Andrea Montanari" wrote:

> hi Steve,
> correct... use 2 different files OR "init" the backup set with the full
> backup and append to it the successive differential..
> if you choos to have only 1 backup file, when restoring you have to specify
> the
> RESTORE DATABASE [xxx] FROM DISK = N'C:\xxxx.bak'
> WITH FILE = n ,
> ......
> where n is 1 for the very first (complete full backup) and then apply the
> differential
> WITH FILE = n , -- where n=2
>
> this is up to you... on how you "store" backup sets, how long you have to
> store them, your internal policies, the actual media hosting the backups...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
sql

MSDE - Not associated with a trusted SQL Server connection

I have just installed MSDE on my PC running WinXP Pro. I set it up to use
mixed mode for security mode. However, when I try to log in to the database
using a user account I created I get the following error:
Login Failed for user 'username'. Reason: Not associated with a trusted SQL
server connection.
I am using DbaMgr 2k to view the database settings. I have provided all
admin level access to this user. However, I just cannot login using a
username. I have tried using the sa account to login and I get the same
error. I can only login using a Trusted NT Connection option. Any ideas what
I should try? When I searched it on the web, the only suggestion I can see
is to make sure the security mode is set to mixed mode, which seems to be
correct in this case.
Any help will be appreciated.
DeepaliHi
You don't say how you are trying to connect to the server? Try the following
from a command prompt
osql -S <Servername> -U <username> -P <Password>
John
"Deepali Garg" wrote:

> I have just installed MSDE on my PC running WinXP Pro. I set it up to use
> mixed mode for security mode. However, when I try to log in to the databas
e
> using a user account I created I get the following error:
> Login Failed for user 'username'. Reason: Not associated with a trusted SQ
L
> server connection.
> I am using DbaMgr 2k to view the database settings. I have provided all
> admin level access to this user. However, I just cannot login using a
> username. I have tried using the sa account to login and I get the same
> error. I can only login using a Trusted NT Connection option. Any ideas wh
at
> I should try? When I searched it on the web, the only suggestion I can see
> is to make sure the security mode is set to mixed mode, which seems to be
> correct in this case.
> Any help will be appreciated.
> Deepali
>
>|||SQL will not use the changes until it has been re-started... The error
seems to be saying it will only use trusted connections... perhaps you
simply need to re-start SQL...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Deepali Garg" <dgarg@.bigpond.net.au> wrote in message
news:mycTd.173155$K7.61651@.news-server.bigpond.net.au...
>I have just installed MSDE on my PC running WinXP Pro. I set it up to use
> mixed mode for security mode. However, when I try to log in to the
> database
> using a user account I created I get the following error:
> Login Failed for user 'username'. Reason: Not associated with a trusted
> SQL
> server connection.
> I am using DbaMgr 2k to view the database settings. I have provided all
> admin level access to this user. However, I just cannot login using a
> username. I have tried using the sa account to login and I get the same
> error. I can only login using a Trusted NT Connection option. Any ideas
> what
> I should try? When I searched it on the web, the only suggestion I can see
> is to make sure the security mode is set to mixed mode, which seems to be
> correct in this case.
> Any help will be appreciated.
> Deepali
>|||Wayne,
Thanks for your help. This is exactly what the problem was. As soon as I
restarted the SQL Server everything worked ok!
Deepali
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eW1ECQnGFHA.3628@.TK2MSFTNGP15.phx.gbl...
> SQL will not use the changes until it has been re-started... The error
> seems to be saying it will only use trusted connections... perhaps you
> simply need to re-start SQL...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Deepali Garg" <dgarg@.bigpond.net.au> wrote in message
> news:mycTd.173155$K7.61651@.news-server.bigpond.net.au...
see[vbcol=seagreen]
be[vbcol=seagreen]
>

MSDE - Not associated with a trusted SQL Server connection

I have just installed MSDE on my PC running WinXP Pro. I set it up to use
mixed mode for security mode. However, when I try to log in to the database
using a user account I created I get the following error:
Login Failed for user 'username'. Reason: Not associated with a trusted SQL
server connection.
I am using DbaMgr 2k to view the database settings. I have provided all
admin level access to this user. However, I just cannot login using a
username. I have tried using the sa account to login and I get the same
error. I can only login using a Trusted NT Connection option. Any ideas what
I should try? When I searched it on the web, the only suggestion I can see
is to make sure the security mode is set to mixed mode, which seems to be
correct in this case.
Any help will be appreciated.
Deepali
Hi
You don't say how you are trying to connect to the server? Try the following
from a command prompt
osql -S <Servername> -U <username> -P <Password>
John
"Deepali Garg" wrote:

> I have just installed MSDE on my PC running WinXP Pro. I set it up to use
> mixed mode for security mode. However, when I try to log in to the database
> using a user account I created I get the following error:
> Login Failed for user 'username'. Reason: Not associated with a trusted SQL
> server connection.
> I am using DbaMgr 2k to view the database settings. I have provided all
> admin level access to this user. However, I just cannot login using a
> username. I have tried using the sa account to login and I get the same
> error. I can only login using a Trusted NT Connection option. Any ideas what
> I should try? When I searched it on the web, the only suggestion I can see
> is to make sure the security mode is set to mixed mode, which seems to be
> correct in this case.
> Any help will be appreciated.
> Deepali
>
>
|||SQL will not use the changes until it has been re-started... The error
seems to be saying it will only use trusted connections... perhaps you
simply need to re-start SQL...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Deepali Garg" <dgarg@.bigpond.net.au> wrote in message
news:mycTd.173155$K7.61651@.news-server.bigpond.net.au...
>I have just installed MSDE on my PC running WinXP Pro. I set it up to use
> mixed mode for security mode. However, when I try to log in to the
> database
> using a user account I created I get the following error:
> Login Failed for user 'username'. Reason: Not associated with a trusted
> SQL
> server connection.
> I am using DbaMgr 2k to view the database settings. I have provided all
> admin level access to this user. However, I just cannot login using a
> username. I have tried using the sa account to login and I get the same
> error. I can only login using a Trusted NT Connection option. Any ideas
> what
> I should try? When I searched it on the web, the only suggestion I can see
> is to make sure the security mode is set to mixed mode, which seems to be
> correct in this case.
> Any help will be appreciated.
> Deepali
>
|||Wayne,
Thanks for your help. This is exactly what the problem was. As soon as I
restarted the SQL Server everything worked ok!
Deepali
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eW1ECQnGFHA.3628@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> SQL will not use the changes until it has been re-started... The error
> seems to be saying it will only use trusted connections... perhaps you
> simply need to re-start SQL...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Deepali Garg" <dgarg@.bigpond.net.au> wrote in message
> news:mycTd.173155$K7.61651@.news-server.bigpond.net.au...
see[vbcol=seagreen]
be
>

MSDE - Not associated with a trusted SQL Server connection

I have just installed MSDE on my PC running WinXP Pro. I set it up to use
mixed mode for security mode. However, when I try to log in to the database
using a user account I created I get the following error:
Login Failed for user 'username'. Reason: Not associated with a trusted SQL
server connection.
I am using DbaMgr 2k to view the database settings. I have provided all
admin level access to this user. However, I just cannot login using a
username. I have tried using the sa account to login and I get the same
error. I can only login using a Trusted NT Connection option. Any ideas what
I should try? When I searched it on the web, the only suggestion I can see
is to make sure the security mode is set to mixed mode, which seems to be
correct in this case.
Any help will be appreciated.
DeepaliHi
You don't say how you are trying to connect to the server? Try the following
from a command prompt
osql -S <Servername> -U <username> -P <Password>
John
"Deepali Garg" wrote:
> I have just installed MSDE on my PC running WinXP Pro. I set it up to use
> mixed mode for security mode. However, when I try to log in to the database
> using a user account I created I get the following error:
> Login Failed for user 'username'. Reason: Not associated with a trusted SQL
> server connection.
> I am using DbaMgr 2k to view the database settings. I have provided all
> admin level access to this user. However, I just cannot login using a
> username. I have tried using the sa account to login and I get the same
> error. I can only login using a Trusted NT Connection option. Any ideas what
> I should try? When I searched it on the web, the only suggestion I can see
> is to make sure the security mode is set to mixed mode, which seems to be
> correct in this case.
> Any help will be appreciated.
> Deepali
>
>|||SQL will not use the changes until it has been re-started... The error
seems to be saying it will only use trusted connections... perhaps you
simply need to re-start SQL...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Deepali Garg" <dgarg@.bigpond.net.au> wrote in message
news:mycTd.173155$K7.61651@.news-server.bigpond.net.au...
>I have just installed MSDE on my PC running WinXP Pro. I set it up to use
> mixed mode for security mode. However, when I try to log in to the
> database
> using a user account I created I get the following error:
> Login Failed for user 'username'. Reason: Not associated with a trusted
> SQL
> server connection.
> I am using DbaMgr 2k to view the database settings. I have provided all
> admin level access to this user. However, I just cannot login using a
> username. I have tried using the sa account to login and I get the same
> error. I can only login using a Trusted NT Connection option. Any ideas
> what
> I should try? When I searched it on the web, the only suggestion I can see
> is to make sure the security mode is set to mixed mode, which seems to be
> correct in this case.
> Any help will be appreciated.
> Deepali
>|||Wayne,
Thanks for your help. This is exactly what the problem was. As soon as I
restarted the SQL Server everything worked ok!
Deepali
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eW1ECQnGFHA.3628@.TK2MSFTNGP15.phx.gbl...
> SQL will not use the changes until it has been re-started... The error
> seems to be saying it will only use trusted connections... perhaps you
> simply need to re-start SQL...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Deepali Garg" <dgarg@.bigpond.net.au> wrote in message
> news:mycTd.173155$K7.61651@.news-server.bigpond.net.au...
> >I have just installed MSDE on my PC running WinXP Pro. I set it up to use
> > mixed mode for security mode. However, when I try to log in to the
> > database
> > using a user account I created I get the following error:
> >
> > Login Failed for user 'username'. Reason: Not associated with a trusted
> > SQL
> > server connection.
> >
> > I am using DbaMgr 2k to view the database settings. I have provided all
> > admin level access to this user. However, I just cannot login using a
> > username. I have tried using the sa account to login and I get the same
> > error. I can only login using a Trusted NT Connection option. Any ideas
> > what
> > I should try? When I searched it on the web, the only suggestion I can
see
> > is to make sure the security mode is set to mixed mode, which seems to
be
> > correct in this case.
> >
> > Any help will be appreciated.
> >
> > Deepali
> >
> >
>

msdb trascation logging mode

Hi,
I have a production SQL 2000 database with transaction logging turned on
('Full' recovery mode). I backup the transaction log every 15 minutes. In
order to use the point-in-time recovery option, do I also need to have the
'Full' recovery model set on the msdb? I ask this because I assume the msdb
holds information about the transaction log backup taken on the production
database. Without this information, I suspect point-in-time recover will not
be possible. Is my assumption correct?
Regards,
James G.Hi
MSDB is not needed to do point in time recovery. MSDB is just used to keep
the backup information so that Enterprise Manager UI can show you what
backups are available. It is a nice to have.
Point in time recovery needs the database dump and all the transaction log
dumps after that to the time you want to restore to.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"James Goodwill" wrote:

> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the msd
b
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will n
ot
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>|||Hi James
The MSDB doesn't have to be in full recovery mode. In fact the MSDB
has no role when you run the restore. If the MSDB would have any role
in the restore then it would be impossible to restore the database on a
different server. All the data that is needed for the point in time
restore is stored in the log backup it self.
Adi|||Hi,
'Full' recovery model set on the msdb?
Ans:- NO NEED
I suspect point-in-time recover will not be possible. Is my assumption
correct?
No. You can do it with out a MSDB tranasction log backup.
MSDB database holds the backup history information. FOR each backup SQL
Server logs an entry into BACKUPSET table in MSDB database.
This is just an information only and will be useful if you use Enterprise
manager to do point in time recovery. Even if you
do not have tis information you can do a recovery based on Transaction log
file date and time. For a time based recovery:-
1. Restore the Full database backup first with Norecovery
2. Restore the logbackup file in sequence based on date and time with
Norecovery till last file
3. Restore the last transaction log backup with RECOVERY and STOPAT option.
Note:-
I am taking the MSDB full database backup daily once only.
Thanks
Hari
SQL Server MVP
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:DmmHe.10993$SO4.8738@.newsfe4-win.ntli.net...
> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the
> msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will
> not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>

msdb trascation logging mode

Hi,
I have a production SQL 2000 database with transaction logging turned on
('Full' recovery mode). I backup the transaction log every 15 minutes. In
order to use the point-in-time recovery option, do I also need to have the
'Full' recovery model set on the msdb? I ask this because I assume the msdb
holds information about the transaction log backup taken on the production
database. Without this information, I suspect point-in-time recover will not
be possible. Is my assumption correct?
Regards,
James G.
Hi
MSDB is not needed to do point in time recovery. MSDB is just used to keep
the backup information so that Enterprise Manager UI can show you what
backups are available. It is a nice to have.
Point in time recovery needs the database dump and all the transaction log
dumps after that to the time you want to restore to.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"James Goodwill" wrote:

> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>
|||Hi James
The MSDB doesn't have to be in full recovery mode. In fact the MSDB
has no role when you run the restore. If the MSDB would have any role
in the restore then it would be impossible to restore the database on a
different server. All the data that is needed for the point in time
restore is stored in the log backup it self.
Adi
|||Hi,
'Full' recovery model set on the msdb?
Ans:- NO NEED
I suspect point-in-time recover will not be possible. Is my assumption
correct?
No. You can do it with out a MSDB tranasction log backup.
MSDB database holds the backup history information. FOR each backup SQL
Server logs an entry into BACKUPSET table in MSDB database.
This is just an information only and will be useful if you use Enterprise
manager to do point in time recovery. Even if you
do not have tis information you can do a recovery based on Transaction log
file date and time. For a time based recovery:-
1. Restore the Full database backup first with Norecovery
2. Restore the logbackup file in sequence based on date and time with
Norecovery till last file
3. Restore the last transaction log backup with RECOVERY and STOPAT option.
Note:-
I am taking the MSDB full database backup daily once only.
Thanks
Hari
SQL Server MVP
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:DmmHe.10993$SO4.8738@.newsfe4-win.ntli.net...
> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the
> msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will
> not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>

msdb trascation logging mode

Hi,
I have a production SQL 2000 database with transaction logging turned on
('Full' recovery mode). I backup the transaction log every 15 minutes. In
order to use the point-in-time recovery option, do I also need to have the
'Full' recovery model set on the msdb? I ask this because I assume the msdb
holds information about the transaction log backup taken on the production
database. Without this information, I suspect point-in-time recover will not
be possible. Is my assumption correct?
Regards,
James G.Hi
MSDB is not needed to do point in time recovery. MSDB is just used to keep
the backup information so that Enterprise Manager UI can show you what
backups are available. It is a nice to have.
Point in time recovery needs the database dump and all the transaction log
dumps after that to the time you want to restore to.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"James Goodwill" wrote:
> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>|||Hi James
The MSDB doesn't have to be in full recovery mode. In fact the MSDB
has no role when you run the restore. If the MSDB would have any role
in the restore then it would be impossible to restore the database on a
different server. All the data that is needed for the point in time
restore is stored in the log backup it self.
Adi|||Hi,
'Full' recovery model set on the msdb?
Ans:- NO NEED
I suspect point-in-time recover will not be possible. Is my assumption
correct?
No. You can do it with out a MSDB tranasction log backup.
--
MSDB database holds the backup history information. FOR each backup SQL
Server logs an entry into BACKUPSET table in MSDB database.
This is just an information only and will be useful if you use Enterprise
manager to do point in time recovery. Even if you
do not have tis information you can do a recovery based on Transaction log
file date and time. For a time based recovery:-
1. Restore the Full database backup first with Norecovery
2. Restore the logbackup file in sequence based on date and time with
Norecovery till last file
3. Restore the last transaction log backup with RECOVERY and STOPAT option.
Note:-
I am taking the MSDB full database backup daily once only.
Thanks
Hari
SQL Server MVP
"James Goodwill" <james.goodwill@.uk.fujitsu.com> wrote in message
news:DmmHe.10993$SO4.8738@.newsfe4-win.ntli.net...
> Hi,
> I have a production SQL 2000 database with transaction logging turned on
> ('Full' recovery mode). I backup the transaction log every 15 minutes. In
> order to use the point-in-time recovery option, do I also need to have the
> 'Full' recovery model set on the msdb? I ask this because I assume the
> msdb
> holds information about the transaction log backup taken on the production
> database. Without this information, I suspect point-in-time recover will
> not
> be possible. Is my assumption correct?
> Regards,
> James G.
>
>

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with this
What put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:

>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this
|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:

> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with thisWhat put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:
>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:
> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
> >my msdb has been put into single user mode and i cant get it back to
> >multi_user can anyone help me with this
>

msdb Single user

my msdb has been put into single user mode and i cant get it back to
multi_user can anyone help me with thisWhat put it in single user mode? Did you check the logs?
If it was a user action, did you try executing the
following:
ALTER DATABASE msdb
SET MULTI_USER
-Sue
On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:

>my msdb has been put into single user mode and i cant get it back to
>multi_user can anyone help me with this|||I tried that but it said there was already a connection and could not alter
the database. I had to shutdown the SQL Server Agent in order to stop the
processes that are running in the background on that table. Then I could
execute the alter database statement.
"Sue Hoegemeier" wrote:

> What put it in single user mode? Did you check the logs?
> If it was a user action, did you try executing the
> following:
> ALTER DATABASE msdb
> SET MULTI_USER
> -Sue
> On Tue, 30 Aug 2005 09:17:40 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

MSDB in suspect mode.

Hi,

I have mssql 2005 installed and i see that MSDB database is marked as suspect. I almost tried all the steps which are submitted in the forums.

1) I shut down the server, navigating to the directory 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608
2) renamed the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
3) Run the instmsdb.sql script in the 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory

--x
When I executed third step it returned me below error:
Msg 926, Level 14, State 1, Line 63
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

Please advice me.

It sounds like you didn't detach msdb - see my post at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx for the complete sequence of operations.

Let us know how it goes.

|||Hi,

As the DB was in the suspect mode it didn't gave me scope to detach the DB. Now the DB is out of suspect mode after succesfully completing the 3rd step of your blog.

However the another issue I found while going through the script (in your blog), instmsdb.sql while going to create msdb database uses the same path of master db. while actual path in the sysdatabase for msdb database is different than master db.

Also 3rd steps is completed succesfully but it doesn't create database anywhere due to this msdb is out of suspect mode but as it doesn't see physical file as per the path specified in sysdatabase it doesn't work and gives below error while accessing properties of any database:

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space|||

I'm confused - you say msdb is out of suspect mode and then later you say that a new msdb wasn't created. Do you have a working msdb at this point?

[Edit] One thing I didn't ask is how did msdb get suspect in the first place? I take it you don't have a backup of msdb given that you're trying to rebuild it?

|||

Hi,

The database came out of suspect mode after replacing the good one msdb. However when I fire the query select * from sysdatabases the path of master db and msdb differs. My concern was when we run the query for instmsdb.sql which recreates msdb. My concern is it doesn't create the msdb file. So wanted to know whether it creates file or just repairs the existing one.

This was actually one of client's system so we do not know how it went to suspect mode. Unfortunately we do not have backups for the same.

Now when I right click on DB it gives me following error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Danish_Norwegian_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)

MSDB in suspect mode.

Hi,

I have mssql 2005 installed and i see that MSDB database is marked as suspect. I almost tried all the steps which are submitted in the forums.

1) I shut down the server, navigating to the directory 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608
2) renamed the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
3) Run the instmsdb.sql script in the 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory

--x
When I executed third step it returned me below error:
Msg 926, Level 14, State 1, Line 63
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.

Please advice me.

It sounds like you didn't detach msdb - see my post at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx for the complete sequence of operations.

Let us know how it goes.

|||Hi,

As the DB was in the suspect mode it didn't gave me scope to detach the DB. Now the DB is out of suspect mode after succesfully completing the 3rd step of your blog.

However the another issue I found while going through the script (in your blog), instmsdb.sql while going to create msdb database uses the same path of master db. while actual path in the sysdatabase for msdb database is different than master db.

Also 3rd steps is completed succesfully but it doesn't create database anywhere due to this msdb is out of suspect mode but as it doesn't see physical file as per the path specified in sysdatabase it doesn't work and gives below error while accessing properties of any database:

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space|||

I'm confused - you say msdb is out of suspect mode and then later you say that a new msdb wasn't created. Do you have a working msdb at this point?

[Edit] One thing I didn't ask is how did msdb get suspect in the first place? I take it you don't have a backup of msdb given that you're trying to rebuild it?

|||

Hi,

The database came out of suspect mode after replacing the good one msdb. However when I fire the query select * from sysdatabases the path of master db and msdb differs. My concern was when we run the query for instmsdb.sql which recreates msdb. My concern is it doesn't create the msdb file. So wanted to know whether it creates file or just repairs the existing one.

This was actually one of client's system so we do not know how it went to suspect mode. Unfortunately we do not have backups for the same.

Now when I right click on DB it gives me following error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Danish_Norwegian_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)