Basic question about replication

Hi,
I am a newbie to replication. I recently joined a company where I have a SQL
Server running 2000 SP3 and I see a distribution database and Replication
Monitor in the Enterprise Manager.
On going to Tools->Replication ->Configure Publishing, Subscribers and
Distribution a pop-up window comes up. In this window under the Publishers
tab, the name of the Server comes up as selected. In the subscribers tab an
ODBC data source is selected. However, in the Publications database tab, NO
database name is checked.
I want to delete the distribution database. Can I safely assume that no
replication is taking place, since no database has been selected for
replication? Or is there a way to find out if any replication is taking
place? Thanks in advance.Hi,
I am not sure but you can check this by executing
sp_helpreplicationoption
Shows the types of replication options enabled for a server. This stored
procedure is executed at the Publisher on the publication database. Take help
of books onile also.
Manu
"sharman" wrote:
> Hi,
> I am a newbie to replication. I recently joined a company where I have a SQL
> Server running 2000 SP3 and I see a distribution database and Replication
> Monitor in the Enterprise Manager.
> On going to Tools->Replication ->Configure Publishing, Subscribers and
> Distribution a pop-up window comes up. In this window under the Publishers
> tab, the name of the Server comes up as selected. In the subscribers tab an
> ODBC data source is selected. However, in the Publications database tab, NO
> database name is checked.
> I want to delete the distribution database. Can I safely assume that no
> replication is taking place, since no database has been selected for
> replication? Or is there a way to find out if any replication is taking
> place? Thanks in advance.
>

Bacon in the fire

I have installed MSDE sp3 with the SAPWD and DISABLENETWORKPROTOCOLS=0
switches set. The server works fine on the local machine but attempts to
access it respond with a 'server does not exist or access denied' message.
How do I verify (via osql) that the NetLib is installed correctly for
TCP/IP? If it is not how do I configure it?
Semper Fi,
Red
Please post to newsgroup only
hi Stephen,
"Stephen J Bement" <spam@.null.net> ha scritto nel messaggio
news:%23RtoNK$nEHA.708@.TK2MSFTNGP09.phx.gbl
> I have installed MSDE sp3 with the SAPWD and DISABLENETWORKPROTOCOLS=0
> switches set. The server works fine on the local machine but attempts
> to access it respond with a 'server does not exist or access denied'
> message.
> How do I verify (via osql) that the NetLib is installed correctly for
> TCP/IP? If it is not how do I configure it?
if you are running Windows XP service pack 2, perhaps
http://support.microsoft.com/default.aspx?kbid=841249 is worth reading
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||the system is running XP sp1 not sp2. It does not have any firewall software
running. It can connect to the Server system (i.e. connect drives copy files
and PCAnywhere) but cannot communicate with the SQL Server.
TIA
Semper Fi,
Red
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:2rb1i1F17ftttU1@.uni-berlin.de...
> hi Stephen,
> "Stephen J Bement" <spam@.null.net> ha scritto nel messaggio
> news:%23RtoNK$nEHA.708@.TK2MSFTNGP09.phx.gbl
> if you are running Windows XP service pack 2, perhaps
> http://support.microsoft.com/default.aspx?kbid=841249 is worth reading
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||You might want to check out following article.
BUG: You may not be able to connect to an instance of Microsoft SQL Server
2000 Desktop Engine SP3a or to an instance of Microsoft SQL Server 2000
Desktop Engine (Windows) after you enable the server network libraries
http://support.microsoft.com/?id=827204
To checkout if network libraries are enabled, you can just see errorlog and
see if SQL Server is listening on named pipe and tcp/ip libraries.

Backups to network share fail

I am having an odd failure when backing up my SQL 2000 SP3 server to a
network share. I receive the following error:
BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
failed to create. Operating system error = 5(Access is denied.).
However, the following T-SQL command does run and create the appropriate
file in the same network folder:
EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
Both the SQL Server and SQL Server Agent is running under a domain user
account which has the required permissions to this share. Other SQL Servers
(SP3 and SP4) which also run under the same domain user account are backing
up to the same share without any errors.
Any insight into resolving this issue would be appreciated.HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>I am having an odd failure when backing up my SQL 2000 SP3 server to a
> network share. I receive the following error:
> BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> failed to create. Operating system error = 5(Access is denied.).
> However, the following T-SQL command does run and create the appropriate
> file in the same network folder:
> EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> Both the SQL Server and SQL Server Agent is running under a domain user
> account which has the required permissions to this share. Other SQL
> Servers
> (SP3 and SP4) which also run under the same domain user account are
> backing
> up to the same share without any errors.
> Any insight into resolving this issue would be appreciated.
>|||The maintenance plan is properly configured to backup to the UNC name - in
the exact same manner as all the other servers that are working properly.
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > network share. I receive the following error:
> >
> > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > failed to create. Operating system error = 5(Access is denied.).
> >
> > However, the following T-SQL command does run and create the appropriate
> > file in the same network folder:
> >
> > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> >
> > Both the SQL Server and SQL Server Agent is running under a domain user
> > account which has the required permissions to this share. Other SQL
> > Servers
> > (SP3 and SP4) which also run under the same domain user account are
> > backing
> > up to the same share without any errors.
> >
> > Any insight into resolving this issue would be appreciated.
> >
>|||I have found in those cases that it is usually either a name resolution
issue or a permissions issue.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:53C105CD-2656-4A02-A708-4E6C43F4077F@.microsoft.com...
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
> "Geoff N. Hiten" wrote:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
>> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>> >I am having an odd failure when backing up my SQL 2000 SP3 server to a
>> > network share. I receive the following error:
>> >
>> > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
>> > failed to create. Operating system error = 5(Access is denied.).
>> >
>> > However, the following T-SQL command does run and create the
>> > appropriate
>> > file in the same network folder:
>> >
>> > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
>> >
>> > Both the SQL Server and SQL Server Agent is running under a domain user
>> > account which has the required permissions to this share. Other SQL
>> > Servers
>> > (SP3 and SP4) which also run under the same domain user account are
>> > backing
>> > up to the same share without any errors.
>> >
>> > Any insight into resolving this issue would be appreciated.
>> >
>>|||Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
\\server\share > \\server\share\test.txt' "
On Apr 10, 4:16 pm, Richard Cote
<RichardC...@.discussions.microsoft.com> wrote:
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
>
> "Geoff N. Hiten" wrote:
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> >http://support.microsoft.com/?kbid=555128
> > --
> > Geoff N. Hiten
> > Senior Database Administrator
> > Microsoft SQL Server MVP
> > "Richard Cote" <RichardC...@.discussions.microsoft.com> wrote in message
> >news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> > >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > > network share. I receive the following error:
> > > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > > failed to create. Operating system error = 5(Access is denied.).
> > > However, the following T-SQL command does run and create the appropriate
> > > file in the same network folder:
> > > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> > > Both the SQL Server and SQL Server Agent is running under a domain user
> > > account which has the required permissions to this share. Other SQL
> > > Servers
> > > (SP3 and SP4) which also run under the same domain user account are
> > > backing
> > > up to the same share without any errors.
> > > Any insight into resolving this issue would be appreciated.- Hide quoted text -
> - Show quoted text -|||The xp_cmdshell command works for any sysadmin user and the SQL Server and
SQL Server Agent logins. However, backups still fail to the same network
share.
This problem seems to be isolated to this one server, and is very
perplexing. Turning on auditing on the file share does not reveal any audit
failures. Choosing a different share with full file and share permissions
granted to Everyone also results in the same access denied error.
"schal" wrote:
> Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
> \\server\share > \\server\share\test.txt' "
>
> On Apr 10, 4:16 pm, Richard Cote
> <RichardC...@.discussions.microsoft.com> wrote:
> > The maintenance plan is properly configured to backup to the UNC name - in
> > the exact same manner as all the other servers that are working properly.
> >
> >
> >
> > "Geoff N. Hiten" wrote:
> > > HowTo: Backup to UNC name using Database Maintenance Wizard
> > >http://support.microsoft.com/?kbid=555128
> >
> > > --
> > > Geoff N. Hiten
> > > Senior Database Administrator
> > > Microsoft SQL Server MVP
> >
> > > "Richard Cote" <RichardC...@.discussions.microsoft.com> wrote in message
> > >news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
> > > >I am having an odd failure when backing up my SQL 2000 SP3 server to a
> > > > network share. I receive the following error:
> >
> > > > BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> > > > failed to create. Operating system error = 5(Access is denied.).
> >
> > > > However, the following T-SQL command does run and create the appropriate
> > > > file in the same network folder:
> >
> > > > EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> >
> > > > Both the SQL Server and SQL Server Agent is running under a domain user
> > > > account which has the required permissions to this share. Other SQL
> > > > Servers
> > > > (SP3 and SP4) which also run under the same domain user account are
> > > > backing
> > > > up to the same share without any errors.
> >
> > > > Any insight into resolving this issue would be appreciated.- Hide quoted text -
> >
> > - Show quoted text -
>
>

Backups to network share fail

I am having an odd failure when backing up my SQL 2000 SP3 server to a
network share. I receive the following error:
BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
failed to create. Operating system error = 5(Access is denied.).
However, the following T-SQL command does run and create the appropriate
file in the same network folder:
EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
Both the SQL Server and SQL Server Agent is running under a domain user
account which has the required permissions to this share. Other SQL Servers
(SP3 and SP4) which also run under the same domain user account are backing
up to the same share without any errors.
Any insight into resolving this issue would be appreciated.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>I am having an odd failure when backing up my SQL 2000 SP3 server to a
> network share. I receive the following error:
> BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> failed to create. Operating system error = 5(Access is denied.).
> However, the following T-SQL command does run and create the appropriate
> file in the same network folder:
> EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> Both the SQL Server and SQL Server Agent is running under a domain user
> account which has the required permissions to this share. Other SQL
> Servers
> (SP3 and SP4) which also run under the same domain user account are
> backing
> up to the same share without any errors.
> Any insight into resolving this issue would be appreciated.
>
|||The maintenance plan is properly configured to backup to the UNC name - in
the exact same manner as all the other servers that are working properly.
"Geoff N. Hiten" wrote:

> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>
|||I have found in those cases that it is usually either a name resolution
issue or a permissions issue.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:53C105CD-2656-4A02-A708-4E6C43F4077F@.microsoft.com...[vbcol=seagreen]
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
> "Geoff N. Hiten" wrote:
|||Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
\\server\share > \\server\share\test.txt' "
On Apr 10, 4:16 pm, Richard Cote
<RichardC...@.discussions.microsoft.com> wrote:
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
>
> "Geoff N. Hiten" wrote:
>
>
>
>
> - Show quoted text -
|||The xp_cmdshell command works for any sysadmin user and the SQL Server and
SQL Server Agent logins. However, backups still fail to the same network
share.
This problem seems to be isolated to this one server, and is very
perplexing. Turning on auditing on the file share does not reveal any audit
failures. Choosing a different share with full file and share permissions
granted to Everyone also results in the same access denied error.
"schal" wrote:

> Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
> \\server\share > \\server\share\test.txt' "
>
> On Apr 10, 4:16 pm, Richard Cote
> <RichardC...@.discussions.microsoft.com> wrote:
>
>

Backups to network share fail

I am having an odd failure when backing up my SQL 2000 SP3 server to a
network share. I receive the following error:
BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
failed to create. Operating system error = 5(Access is denied.).
However, the following T-SQL command does run and create the appropriate
file in the same network folder:
EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
Both the SQL Server and SQL Server Agent is running under a domain user
account which has the required permissions to this share. Other SQL Servers
(SP3 and SP4) which also run under the same domain user account are backing
up to the same share without any errors.
Any insight into resolving this issue would be appreciated.HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>I am having an odd failure when backing up my SQL 2000 SP3 server to a
> network share. I receive the following error:
> BackupDiskFile::CreateMedia: Backup device '\\server\share\master.BAK'
> failed to create. Operating system error = 5(Access is denied.).
> However, the following T-SQL command does run and create the appropriate
> file in the same network folder:
> EXEC xp_cmdshell 'dir \\server\share > \\server\share\test.txt'
> Both the SQL Server and SQL Server Agent is running under a domain user
> account which has the required permissions to this share. Other SQL
> Servers
> (SP3 and SP4) which also run under the same domain user account are
> backing
> up to the same share without any errors.
> Any insight into resolving this issue would be appreciated.
>|||The maintenance plan is properly configured to backup to the UNC name - in
the exact same manner as all the other servers that are working properly.
"Geoff N. Hiten" wrote:

> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
> news:3BA4F72D-E6E3-434A-BDE1-95BC990A3B66@.microsoft.com...
>|||I have found in those cases that it is usually either a name resolution
issue or a permissions issue.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Richard Cote" <RichardCote@.discussions.microsoft.com> wrote in message
news:53C105CD-2656-4A02-A708-4E6C43F4077F@.microsoft.com...[vbcol=seagreen]
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
> "Geoff N. Hiten" wrote:
>|||Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
\\server\share > \\server\share\test.txt' "
On Apr 10, 4:16 pm, Richard Cote
<RichardC...@.discussions.microsoft.com> wrote:
> The maintenance plan is properly configured to backup to the UNC name - in
> the exact same manner as all the other servers that are working properly.
>
> "Geoff N. Hiten" wrote:
>
>
>
>
>
>
>
> - Show quoted text -|||The xp_cmdshell command works for any sysadmin user and the SQL Server and
SQL Server Agent logins. However, backups still fail to the same network
share.
This problem seems to be isolated to this one server, and is very
perplexing. Turning on auditing on the file share does not reveal any audit
failures. Choosing a different share with full file and share permissions
granted to Everyone also results in the same access denied error.
"schal" wrote:

> Login using the SQLAgent Login and Try the same "EXEC xp_cmdshell 'dir
> \\server\share > \\server\share\test.txt' "
>
> On Apr 10, 4:16 pm, Richard Cote
> <RichardC...@.discussions.microsoft.com> wrote:
>
>

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?
How do you run these jobs, and what types are they of. Maint Wiz? SQL Server Agent jobs, TSQL or
CmdExec? Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transaction
> logs. 2 out of the 3 databases are being backed up and the transaction logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?
|||Tibor Karaszi wrote:

> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.
|||You may need to look at both the job history (make sure to view step
details) and the MP history.
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?
|||In addition to Andrew's answer, I suggest you specify a report file in Main Wiz and look for error
messages in there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>
> They were created using the Maintenance Wizard and scheduled to run nightly.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?How do you run these jobs, and what types are they of. Maint Wiz? SQL Server Agent jobs, TSQL or
CmdExec? Etc...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transaction
> logs. 2 out of the 3 databases are being backed up and the transaction logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||Tibor Karaszi wrote:
> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.|||You may need to look at both the job history (make sure to view step
details) and the MP history.
--
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||In addition to Andrew's answer, I suggest you specify a report file in Main Wiz and look for error
messages in there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How do you run these jobs, and what types are they of. Maint Wiz? SQL
>> Server Agent jobs, TSQL or CmdExec? Etc...
>
> They were created using the Maintenance Wizard and scheduled to run nightly.

Backups failing

I am running SQL-2000 Standard (SP3) and have created a backup job a while
back to back up several databases, and a seperate job to backup transaction
logs. 2 out of the 3 databases are being backed up and the transaction logs
are backing up, but our largest database (almost 5GB) is failing on the
backup. Where can I look to see why it is failing?How do you run these jobs, and what types are they of. Maint Wiz? SQL Server
Agent jobs, TSQL or
CmdExec? Etc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup transactio
n
> logs. 2 out of the 3 databases are being backed up and the transaction log
s
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||Tibor Karaszi wrote:

> How do you run these jobs, and what types are they of. Maint Wiz? SQL
> Server Agent jobs, TSQL or CmdExec? Etc...
>
They were created using the Maintenance Wizard and scheduled to run nightly.|||You may need to look at both the job history (make sure to view step
details) and the MP history.
Andrew J. Kelly SQL MVP
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:%23v8hVlDvEHA.3976@.TK2MSFTNGP09.phx.gbl...
>I am running SQL-2000 Standard (SP3) and have created a backup job a while
> back to back up several databases, and a seperate job to backup
> transaction
> logs. 2 out of the 3 databases are being backed up and the transaction
> logs
> are backing up, but our largest database (almost 5GB) is failing on the
> backup. Where can I look to see why it is failing?|||In addition to Andrew's answer, I suggest you specify a report file in Main
Wiz and look for error
messages in there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Humphrey" <richard@.multicam.com> wrote in message
news:estbZJEvEHA.3424@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Tibor Karaszi wrote:
>
>
> They were created using the Maintenance Wizard and scheduled to run nightly.[/vbco
l]

Backup/Restore vs Detach/Attach 2000 Databases to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach. I
backup and detach a database from SQL Server 2000 SP3 and then attach and
restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is
same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is
showing owned by the database owner of the database restored over but
syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
PeterMy best guess on #1 would be that the database size was increased because
the database was upgraded to 2005 and so the file expanded to accomidate the
necessary operations. Or because additional objects were added (system sp's,
tables or views) which are part of the 2005 functionality.
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> I'm trying to see the differences between Backup/Restore and
> Attach/Detach. I
> backup and detach a database from SQL Server 2000 SP3 and then attach and
> restore it to SQL Server 2005 SP1.
> The differences I noticed are:
> 1. The restored database has a much larger initial size (database size is
> same) for data and log.
> 2. The attached database has a last backup date
> 3. If the backup is restored over a database, the restored database is
> showing owned by the database owner of the database restored over but
> syslogins and sysusers do not match.
> I don't understand why #1 happens.
> Are there any other differences between Backup/Restore and Attach/Detach?
>
> Thanks,
> Peter
>|||Hi Mark,
If your guess is correct, should it happen to both cases: restored database
and attached database not just restored database.
Peter
"Mark Miller" wrote:
> My best guess on #1 would be that the database size was increased because
> the database was upgraded to 2005 and so the file expanded to accomidate the
> necessary operations. Or because additional objects were added (system sp's,
> tables or views) which are part of the 2005 functionality.
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> > I'm trying to see the differences between Backup/Restore and
> > Attach/Detach. I
> > backup and detach a database from SQL Server 2000 SP3 and then attach and
> > restore it to SQL Server 2005 SP1.
> >
> > The differences I noticed are:
> >
> > 1. The restored database has a much larger initial size (database size is
> > same) for data and log.
> > 2. The attached database has a last backup date
> > 3. If the backup is restored over a database, the restored database is
> > showing owned by the database owner of the database restored over but
> > syslogins and sysusers do not match.
> >
> > I don't understand why #1 happens.
> >
> > Are there any other differences between Backup/Restore and Attach/Detach?
> >
> >
> >
> > Thanks,
> >
> > Peter
> >
>
>

Backup/Restore vs Detach/Attach 2000 Databases to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach.
I
backup and detach a database from SQL Server 2000 SP3 and then attach and
restore it to SQL Server 2005 SP1.
The differences I noticed are:
1. The restored database has a much larger initial size (database size is
same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is
showing owned by the database owner of the database restored over but
syslogins and sysusers do not match.
I don't understand why #1 happens.
Are there any other differences between Backup/Restore and Attach/Detach?
Thanks,
PeterMy best guess on #1 would be that the database size was increased because
the database was upgraded to 2005 and so the file expanded to accomidate the
necessary operations. Or because additional objects were added (system sp's,
tables or views) which are part of the 2005 functionality.
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
> I'm trying to see the differences between Backup/Restore and
> Attach/Detach. I
> backup and detach a database from SQL Server 2000 SP3 and then attach and
> restore it to SQL Server 2005 SP1.
> The differences I noticed are:
> 1. The restored database has a much larger initial size (database size is
> same) for data and log.
> 2. The attached database has a last backup date
> 3. If the backup is restored over a database, the restored database is
> showing owned by the database owner of the database restored over but
> syslogins and sysusers do not match.
> I don't understand why #1 happens.
> Are there any other differences between Backup/Restore and Attach/Detach?
>
> Thanks,
> Peter
>|||Hi Mark,
If your guess is correct, should it happen to both cases: restored database
and attached database not just restored database.
Peter
"Mark Miller" wrote:

> My best guess on #1 would be that the database size was increased because
> the database was upgraded to 2005 and so the file expanded to accomidate t
he
> necessary operations. Or because additional objects were added (system sp'
s,
> tables or views) which are part of the 2005 functionality.
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:BDCA44DC-CAD7-4A14-8E9B-3D492C3D6B20@.microsoft.com...
>
>

Backup/Restore vs Attach/Detach 2000 Database to 2005

I'm trying to see the differences between Backup/Restore and Attach/Detach. I backup and detach a database from SQL Server 2000 SP3 and then attach and restore it to SQL Server 2005 SP1.

The differences I noticed are:

1. The restored database has a much larger initial size (database size is same) for data and log.
2. The attached database has a last backup date
3. If the backup is restored over a database, the restored database is showing owned by the database owner of the database restored over but syslogins and sysusers do not match.

I don't understand why #1 happens.

Are there any other differences between Backup/Restore and Attach/Detach?

Thanks,

Peter

I guess I don't understand what your #1 means. You seem to be saying that (1) the database is much larger if you restore it vs. an attach but (2) that the database size is the same for both. That seems contradictory...

As for other differences, the primary differences for most people relate to speed. If I'm just moving the log file from the F:\ drive to the E:\ drive, a backup/restore operation for a large database would take much, much longer than an detach/attach. I don't mean to be too simplistic as it sounds, from your testing, that you're very comfortable with SQL Server. Outside of that, there's not going to be a difference between RECOVERY, the orphaned users (#3 above), or anything else that I'm aware of. There's probably some niggling things here and there that are minor differences but nothing major that I know of.

/****************************************************

Check out our Sql Server 2005 and 2000 tutorials

****************************************************/

Backup/Restore SP2 to SP3?

Can I take a Backup created on a server running SQL Server
2000 (Service Pack 2) and Restore it on a server running
SQL Server 2000 (Service Pack 3)? (Without problems, of
course).
I want to not only restore User DBs, but also System DBs
sush as Master, Model, and msdb.
ThanksSystem databases, no. YOu cannot restore a previous
version (i.e. SP2) under SP3.
For user dbs, sure.
>--Original Message--
>Can I take a Backup created on a server running SQL
Server
>2000 (Service Pack 2) and Restore it on a server running
>SQL Server 2000 (Service Pack 3)? (Without problems, of
>course).
>I want to not only restore User DBs, but also System DBs
>sush as Master, Model, and msdb.
>Thanks
>.
>|||Allan
Does this mean that I can restore a user database saved on SP2 into SP3?
Ron Lounsbury
"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
news:561601c3b2c4$5b6ed470$a601280a@.phx.gbl...
> System databases, no. YOu cannot restore a previous
> version (i.e. SP2) under SP3.
> For user dbs, sure.
> >--Original Message--
> >Can I take a Backup created on a server running SQL
> Server
> >2000 (Service Pack 2) and Restore it on a server running
> >SQL Server 2000 (Service Pack 3)? (Without problems, of
> >course).
> >
> >I want to not only restore User DBs, but also System DBs
> >sush as Master, Model, and msdb.
> >
> >Thanks
> >
> >.
> >|||Yes, of course, you can. I do that all the time. Imagine
if you couldn't restore an SP2 user database to an SP3
instance, you would have to upgrade your Dev and prod
environments at the same time if you want to copy any
databases from the prod to the Dev for whatever purposes.
Linchi
>--Original Message--
>Allan
> Does this mean that I can restore a user database
saved on SP2 into SP3?
>Ron Lounsbury
>
>"Allan Hirt" <allanh@.NOSPAMavanade.com> wrote in message
>news:561601c3b2c4$5b6ed470$a601280a@.phx.gbl...
>> System databases, no. YOu cannot restore a previous
>> version (i.e. SP2) under SP3.
>> For user dbs, sure.
>> >--Original Message--
>> >Can I take a Backup created on a server running SQL
>> Server
>> >2000 (Service Pack 2) and Restore it on a server
running
>> >SQL Server 2000 (Service Pack 3)? (Without problems, of
>> >course).
>> >
>> >I want to not only restore User DBs, but also System
DBs
>> >sush as Master, Model, and msdb.
>> >
>> >Thanks
>> >
>> >.
>> >
>
>.
>