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 & SQL 7 running, backups fail

I have a W2K Server with SQL Server 7 and an instance of MSDE 2000 installed.
SQL Server 7 and MSDE 2000 both run OK together (I can make ODBC connections
to their databases from other clients, Enterprise Manager and dbamgr2k work)
and the MSDE 2000 backup jobs run successfully.
However, the SQL Server 7 maintenance jobs do not run successfully. SQL
Agent is running.
The same jobs DO run if MSDE 2000 is not running.
SQL Server connection is NT authentication.
SQL Agent Service startup account is system account.
SQL Agent SQLServer connection is Windows NT authentication.
MSDE connection is trusted NT authentication.
The Event log shows:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan Test'' (0xA15AFA82EC43BD4FBA057E1ECA0E2E61) - Status: Failed
- Invoked on: 29/07/2005 15:22:23 - Message: The job failed. Unable to
determine if the owner (sa) of job DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan Test' has server access (reason: Unable to connect to server
- check SQL Server and SQL Server Agent errorlogs).
SQLAgent.OUT:
29/07/2005 15:21:32 - ! [298] SQLServer Error: 14, Invalid connection.
[SQLSTATE 08001]
29/07/2005 15:21:32 - ! [298] SQLServer Error: 14, ConnectionOpen (Invalid
Instance()). [SQLSTATE 01000]
29/07/2005 15:21:32 - ! [382] Logon to server '(local)' failed
(ConnCachePerfCounterValues)
29/07/2005 15:22:23 - ! [298] SQLServer Error: 14, Invalid connection.
[SQLSTATE 08001]
29/07/2005 15:22:23 - ! [298] SQLServer Error: 14, ConnectionOpen (Invalid
Instance()). [SQLSTATE 01000]
29/07/2005 15:22:23 - ! [382] Logon to server '(local)' failed
(ConnCachePerfCounterValues)
Interestingly, I have to start the services in a particular order - SQL
Server and SQL Server Agent start automatically with Windows. I then have to
manually start MSDE and MSDE Agent otherwise SQL Server Agent cannot start.
I have recreated the same problem on a second server.
Any ideas why my SQL Server 7 jobs will not run?
Has anyone else achieved this?
hi,
Swaffs wrote:
> I have a W2K Server with SQL Server 7 and an instance of MSDE 2000
> installed. SQL Server 7 and MSDE 2000 both run OK together (I can
> make ODBC connections to their databases from other clients,
> Enterprise Manager and dbamgr2k work) and the MSDE 2000 backup jobs
> run successfully.
> However, the SQL Server 7 maintenance jobs do not run successfully.
> SQL Agent is running.
> The same jobs DO run if MSDE 2000 is not running.
I've tried reproducing the problem as indicated, but it seems I'm more lucky
then you
on a virtual machine, where both SQL Server 7.0 Dev Edition sp4 and MSDE Rel
A (sp3a) and relative Agent are running under LocalSystem account...
searching fo your reported exceptions I only found problems related to the
account running the service, but this should not be the case as SQL Server
7.0 Agent runs ok when MSDE is not running...
but I'll keep searching..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea - after reading that you tried it with SP3a without any issues,
I installed the latest Service Pack for MSDE, rebooted and started the MSDE
services before the SQL Services and the SQL Agent jobs work fine now.
Amazing what a difference a weekend makes!

msdb database marked as "suspect"

In my SQL server installation the msdb database has been marked "suspect"
and also the SQL agent cannot be started. Also I can not define any backups
in database management plans (there was on but now it is not working).
The rest database seems to work fine for more than 2 weeks now.
Any ideas?
Thanks"Trifon Triantafillidis" <trifont@.otenet.gr> wrote in message
news:cl61o2$be0$1@.usenet.otenet.gr...
> In my SQL server installation the msdb database has been marked "suspect"
> and also the SQL agent cannot be started. Also I can not define any
backups
> in database management plans (there was on but now it is not working).
> The rest database seems to work fine for more than 2 weeks now.
> Any ideas?
> Thanks
>
Find out what is wrong with your msdb database. The SQL Server Agent uses
the msdb database to store jobs, maintenance plans, replication information
as well as alerts and operators.
Have you tried to restore the msdb from a valid backup?
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||I'd restore the last clean backup of msdb, quite simply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Trifon Triantafillidis" <trifont@.otenet.gr> wrote in message news:cl61o2$be0$1@.usenet.otenet.gr...
> In my SQL server installation the msdb database has been marked "suspect"
> and also the SQL agent cannot be started. Also I can not define any backups
> in database management plans (there was on but now it is not working).
> The rest database seems to work fine for more than 2 weeks now.
> Any ideas?
> Thanks
>|||I don't have a backup of msdb.
I had only one maintenance plan that I can easily recreate it.
Thank you.
Ï "Rick Sawtell" <r_sawtell@.hotmail.com> Ýãñáøå óôï ìÞíõìá
news:e3r4qBstEHA.2808@.TK2MSFTNGP14.phx.gbl...
> "Trifon Triantafillidis" <trifont@.otenet.gr> wrote in message
> news:cl61o2$be0$1@.usenet.otenet.gr...
> > In my SQL server installation the msdb database has been marked
"suspect"
> > and also the SQL agent cannot be started. Also I can not define any
> backups
> > in database management plans (there was on but now it is not working).
> >
> > The rest database seems to work fine for more than 2 weeks now.
> >
> > Any ideas?
> >
> > Thanks
> >
> >
> Find out what is wrong with your msdb database. The SQL Server Agent uses
> the msdb database to store jobs, maintenance plans, replication
information
> as well as alerts and operators.
> Have you tried to restore the msdb from a valid backup?
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||I don't have a backup of msdb.
I had only one maintenance plan that I can easily recreate it.
Thank you.
Ï "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> Ýãñáøå
óôï ìÞíõìá news:OklHrDstEHA.1292@.TK2MSFTNGP10.phx.gbl...
> I'd restore the last clean backup of msdb, quite simply.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Trifon Triantafillidis" <trifont@.otenet.gr> wrote in message
news:cl61o2$be0$1@.usenet.otenet.gr...
> > In my SQL server installation the msdb database has been marked
"suspect"
> > and also the SQL agent cannot be started. Also I can not define any
backups
> > in database management plans (there was on but now it is not working).
> >
> > The rest database seems to work fine for more than 2 weeks now.
> >
> > Any ideas?
> >
> > Thanks
> >
> >
>|||Search Google and KB for suitable phrases such as "rebuild" and "msdb", and you should find articles
covering this. In general, you run instmsdb.sql to get a new fresh msdb database. But SQL Server
might not be so keen to let you do this without setting some trace flag etc, hence my recommendation
to do a search first...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Trifon Triantafillidis" <trifont@.otenet.gr> wrote in message news:cl64mf$nuh$1@.usenet.otenet.gr...
>I don't have a backup of msdb.
> I had only one maintenance plan that I can easily recreate it.
> Thank you.
> Ï "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> Ýãñáøå
> óôï ìÞíõìá news:OklHrDstEHA.1292@.TK2MSFTNGP10.phx.gbl...
>> I'd restore the last clean backup of msdb, quite simply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Trifon Triantafillidis" <trifont@.otenet.gr> wrote in message
> news:cl61o2$be0$1@.usenet.otenet.gr...
>> > In my SQL server installation the msdb database has been marked
> "suspect"
>> > and also the SQL agent cannot be started. Also I can not define any
> backups
>> > in database management plans (there was on but now it is not working).
>> >
>> > The rest database seems to work fine for more than 2 weeks now.
>> >
>> > Any ideas?
>> >
>> > Thanks
>> >
>> >
>>
>|||Thanks a lot
Ï "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> Ýãñáøå
óôï ìÞíõìá news:e0JjtTstEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Search Google and KB for suitable phrases such as "rebuild" and "msdb",
and you should find articles
> covering this. In general, you run instmsdb.sql to get a new fresh msdb
database. But SQL Server
> might not be so keen to let you do this without setting some trace flag
etc, hence my recommendation
> to do a search first...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Trifon Triantafillidis" <trifont@.otenet.gr> wrote in message
news:cl64mf$nuh$1@.usenet.otenet.gr...
> >I don't have a backup of msdb.
> >
> > I had only one maintenance plan that I can easily recreate it.
> >
> > Thank you.
> >
> > Ï "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
Ýãñáøå
> > óôï ìÞíõìá news:OklHrDstEHA.1292@.TK2MSFTNGP10.phx.gbl...
> >> I'd restore the last clean backup of msdb, quite simply.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Trifon Triantafillidis" <trifont@.otenet.gr> wrote in message
> > news:cl61o2$be0$1@.usenet.otenet.gr...
> >> > In my SQL server installation the msdb database has been marked
> > "suspect"
> >> > and also the SQL agent cannot be started. Also I can not define any
> > backups
> >> > in database management plans (there was on but now it is not
working).
> >> >
> >> > The rest database seems to work fine for more than 2 weeks now.
> >> >
> >> > Any ideas?
> >> >
> >> > Thanks
> >> >
> >> >
> >>
> >>
> >
> >
>

MSDB database grows too much even though its empty.

In my SQL Server 2000 msdb database I have several DTS and a few jobs. I also do backups daily. All this should make the msdb grow but I've removed most of the logs and histories and, still, the database is almost 1Gb big. The log file is almost at 0. It is the data file that ocupies the whole size.

I'm thinking it must be some kind of corruption of a table so I ran dbcc checkdb, checktable, cleantable, checkalloc, dbreindex and indexdefrag.

Nothing seems to work in order to make it smaller. Any ideas? Thanks

MSDB can grow by many reasons...

(a) Backup/restore history table not cleared over a period of time - To clear this sp_delete_backuphistory

(b) Job history pilled up

(d) DTS Versioning

(e) Log shipping

Madhu

|||Madhu, I checked the first three before and I don't use log shipping. Any other idea? Thanks|||

Hi Marcos,

After you deleted all the history.

You will need to shrink the data file if you want to make your database smaller in size.

You can use DBCC Shrinkfile command for the above.

regards

Jag

|||Jag, I did that, but anyway, there's no available space to shrink the database. Although there is almost no data in the database it reports to be full and it won't shrink any smaller than the size it thinks it requires to keep the data it believes to have. Thanks|||

BTW , what command u used to get the size of your database ?

Madhu

|||I'm using the Enterprise Manager and double checked it with the sp_helpdb command.|||to confirm it once more just run

EXEC sp_spaceused @.updateusage = N'TRUE';

if still u gets the same error, why don't u run profiler and see the database activities on MSDB. Use column filter database name like MSDB and run the profiler... see the activities... you will get a clear picture

Madhu

|||

Hi Marcos,

Could you please post the output of the following commands:

use msdb

go

sp_spaceused @.updateusage='true'

go

dbcc showfilestats

go

sp_helpdb msdb

go

cheers

Jag

|||

Here are the results of the script.

database_name

database_size

unallocated space

msdb

925.06 MB

0.20 MB

reserved

data

index_size

unused

939896 KB

935056 KB

2616 KB

2224 KB

Fileid

FileGroup

TotalExtents

UsedExtents

Name

FileName

1

1

14689

14687

MSDBData

C:\Archivos de programa\Microsoft SQL Server\MSSQL\data\msdbdata.mdf

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

name

db_size

owner

dbid

created

status

compatibility_level

msdb

925.06 MB

sa

4

Aug6 2000

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=Modern_Spanish_CI_AS, SQLSortOrder=0, IsAutoShrink, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics

80

name

fileid

filename

filegroup

size

maxsize

growth

usage

MSDBData

1

C:\Archivos de programa\Microsoft SQL Server\MSSQL\data\msdbdata.mdf

PRIMARY

940096 KB

Unlimited

256 KB

data only

MSDBLog

2

C:\Archivos de programa\Microsoft SQL Server\MSSQL\data\msdblog.ldf

7168 KB

Unlimited

256 KB

log only

|||

sp_spaceused returned the same as previous results.

The profiler returned normal job work.

|||

Hi,

now u find out the table which consumes the space

Use MSDB

select 'exec sp_spaceused '+name From sysobjects where xtype in ('u')

this query will give u the script for each usre table. Copy paste to QA and run ... post the result

You have not answered the other question .... Why don't u try SQL Profiler ? ... Its very helpful in such scenario

Madhu

|||

Madhu,

I had tried a similar script that brought back the number of rows. I never thought that sysdtspackages rows where so heavy. In fact that table had all the weight in the database.

You were right from the begining, I hadn't cleaned old DTS versioning. I thought I had, but I had only removed the execution logs of the DTS.

Here's the script I used to clean all DTS versions other than the last one. I had to many to do it by hand. I hope you find it usefull for yourself.

declare @.name sysname,
@.cmdSQL varchar(8000)
@.CreateDate datetime

select @.name = min(name)
from sysdtspackages

while @.name is not null
begin
print '@.name: ' + @.name

select @.CreateDate = min(CreateDate)
from sysdtspackages sp
where name = @.name and
createdate < (select max(createdate)
from sysdtspackages sp2
where sp2.id = sp.id)
while @.CreateDate is not null
begin
select @.cmdSQL = 'exec sp_drop_dtspackage NULL, NULL, "' + cast(VersionID as varchar(8000))+ '"'
from sysdtspackages
where name = @.name and
createdate = @.createdate

print @.cmdSQL

exec (@.cmdSQL)

select @.CreateDate = min(CreateDate)
from sysdtspackages sp
where name = @.name and
createdate < (select max(createdate)
from sysdtspackages sp2
where sp2.id = sp.id) and
CreateDate > @.CreateDate

end

select @.name = min(name)
from sysdtspackages
where name > @.name
end

Thanks a lot