A differential backup that seems to darn big...

I have a db that is currently about 7 gb. It is currently on a test
instance of SQL 2005. It is not being used by anyone but me for the
purpose of learning one or two things about 2005. Here is what I deem
odd:
If i do a full backup of the database, I get a backup of 7 gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
I was started to see a pattern. I can guarantee there are no other
users and that I didn't change the database in between backups.
tia,
SteveHi
From BOL:
"A differential backup is based on the most recent, previous full backup of
the data that is included in the differential backup. A differential backup
captures only the data that has changed since that full backup. This is know
n
as the base of the differential. A differential backup includes only the dat
a
that have changed since the differential base. "
If you don't do another full backup between the differential backups they
will only get bigger if someone changes the data, and will stay the same if
they don't.
You don't say how old your base is, but the size of the differential backups
indicates a significant amount of changes. Have you re-indexes or shrunk the
files since the full backup?
John
"Not the Face" wrote:

> I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>|||"Not the Face" <nottheface@.gmail.com> wrote in message
news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
>I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
>
How large is the transaction log?
I believe it's backing up the entire transaction log PLUS any changes in the
database.

> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>|||Sorry about the lag and I appreciate the responses.
The DB is currently ~7.6 gb.
The Transaction Log is currently 5 mb.
I have tried shrinking the DB and Log files (shrinking the whole DB and
each file individually)
This is on a test system, so I have control over the database changing.
It isn't. I was literally doing the differential immediately after
the full backup.
Thanks for your help.
Steve.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Not the Face" <nottheface@.gmail.com> wrote in message
> news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
> How large is the transaction log?
> I believe it's backing up the entire transaction log PLUS any changes in t
he
> database.
>|||Hi
4.7GB does seem large for a differential backup. If you ran the backups as a
script such as:
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 1
0
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
Then a directory of C:\backups gives
Directory of C:\Backups
17/01/2007 15:18 <DIR> .
17/01/2007 15:18 <DIR> ..
17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
17/01/2007 15:18 171,002,368 AdventureworksFull.bak
4 File(s) 174,401,536 bytes
2 Dir(s) 1,241,235,456 bytes free
This shows what you would expect.
John
"Not the Face" wrote:

> Sorry about the lag and I appreciate the responses.
> The DB is currently ~7.6 gb.
> The Transaction Log is currently 5 mb.
> I have tried shrinking the DB and Log files (shrinking the whole DB and
> each file individually)
> This is on a test system, so I have control over the database changing.
> It isn't. I was literally doing the differential immediately after
> the full backup.
> Thanks for your help.
> Steve.
> Greg D. Moore (Strider) wrote:
>|||Yeah. So here was the problem.
Turns out that if you Shrink the DB and Log files after the backup, it
wants to make a really large differential backup for some reason.
Even though you have *changed* the data at all.
You just *moved* the data around a bit. You know. All of it (most of
it anyway).
Woops. I moved the Shrinks in front of the backup and now my
differential is 1,121 kb. Seems a bit more reasonable.
Thanks for the pointing of the fingers in the right direction.
Steve.
On Jan 17, 10:45 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> 4.7GB does seem large for a differential backup. If you ran the backups as
a
> script such as:
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
> N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS =
10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINI
T,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINI
T,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINI
T,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> Then a directory of C:\backups gives
> Directory of C:\Backups
> 17/01/2007 15:18 <DIR> .
> 17/01/2007 15:18 <DIR> ..
> 17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
> 17/01/2007 15:18 171,002,368 AdventureworksFull.bak
> 4 File(s) 174,401,536 bytes
> 2 Dir(s) 1,241,235,456 bytes free
> This shows what you would expect.
> John
>
> "Not the Face" wrote:
>
>
>
>
>
>
>
>
>
>
>sql

A differential backup that seems to darn big...

I have a db that is currently about 7 gb. It is currently on a test
instance of SQL 2005. It is not being used by anyone but me for the
purpose of learning one or two things about 2005. Here is what I deem
odd:
If i do a full backup of the database, I get a backup of 7 gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
I was started to see a pattern. I can guarantee there are no other
users and that I didn't change the database in between backups.
tia,
Steve
Hi
From BOL:
"A differential backup is based on the most recent, previous full backup of
the data that is included in the differential backup. A differential backup
captures only the data that has changed since that full backup. This is known
as the base of the differential. A differential backup includes only the data
that have changed since the differential base. "
If you don't do another full backup between the differential backups they
will only get bigger if someone changes the data, and will stay the same if
they don't.
You don't say how old your base is, but the size of the differential backups
indicates a significant amount of changes. Have you re-indexes or shrunk the
files since the full backup?
John
"Not the Face" wrote:

> I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>
|||"Not the Face" <nottheface@.gmail.com> wrote in message
news:1166552868.097104.258360@.73g2000cwn.googlegro ups.com...
>I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
>
How large is the transaction log?
I believe it's backing up the entire transaction log PLUS any changes in the
database.

> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>
|||Sorry about the lag and I appreciate the responses.
The DB is currently ~7.6 gb.
The Transaction Log is currently 5 mb.
I have tried shrinking the DB and Log files (shrinking the whole DB and
each file individually)
This is on a test system, so I have control over the database changing.
It isn't. I was literally doing the differential immediately after
the full backup.
Thanks for your help.
Steve.
Greg D. Moore (Strider) wrote:[vbcol=seagreen]
> "Not the Face" <nottheface@.gmail.com> wrote in message
> news:1166552868.097104.258360@.73g2000cwn.googlegro ups.com...
> How large is the transaction log?
> I believe it's backing up the entire transaction log PLUS any changes in the
> database.
>
|||Hi
4.7GB does seem large for a differential backup. If you ran the backups as a
script such as:
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [AdventureWorks] TO DISK =
N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
NOUNLOAD, STATS = 10
GO
Then a directory of C:\backups gives
Directory of C:\Backups
17/01/2007 15:18 <DIR> .
17/01/2007 15:18 <DIR> ..
17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
17/01/2007 15:18 171,002,368 AdventureworksFull.bak
4 File(s) 174,401,536 bytes
2 Dir(s) 1,241,235,456 bytes free
This shows what you would expect.
John
"Not the Face" wrote:

> Sorry about the lag and I appreciate the responses.
> The DB is currently ~7.6 gb.
> The Transaction Log is currently 5 mb.
> I have tried shrinking the DB and Log files (shrinking the whole DB and
> each file individually)
> This is on a test system, so I have control over the database changing.
> It isn't. I was literally doing the differential immediately after
> the full backup.
> Thanks for your help.
> Steve.
> Greg D. Moore (Strider) wrote:
>
|||Yeah. So here was the problem.
Turns out that if you Shrink the DB and Log files after the backup, it
wants to make a really large differential backup for some reason.
Even though you have *changed* the data at all.
You just *moved* the data around a bit. You know. All of it (most of
it anyway).
Woops. I moved the Shrinks in front of the backup and now my
differential is 1,121 kb. Seems a bit more reasonable.
Thanks for the pointing of the fingers in the right direction.
Steve.
On Jan 17, 10:45 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> 4.7GB does seem large for a differential backup. If you ran the backups as a
> script such as:
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME =
> N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK =
> N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> Then a directory of C:\backups gives
> Directory of C:\Backups
> 17/01/2007 15:18 <DIR> .
> 17/01/2007 15:18 <DIR> ..
> 17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
> 17/01/2007 15:18 171,002,368 AdventureworksFull.bak
> 4 File(s) 174,401,536 bytes
> 2 Dir(s) 1,241,235,456 bytes free
> This shows what you would expect.
> John
>
> "Not the Face" wrote:
>
>
>
>
>

A differential backup that seems to darn big...

I have a db that is currently about 7 gb. It is currently on a test
instance of SQL 2005. It is not being used by anyone but me for the
purpose of learning one or two things about 2005. Here is what I deem
odd:
If i do a full backup of the database, I get a backup of 7 gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
gb.
I was started to see a pattern. I can guarantee there are no other
users and that I didn't change the database in between backups.
tia,
Steve"Not the Face" <nottheface@.gmail.com> wrote in message
news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
>I have a db that is currently about 7 gb. It is currently on a test
> instance of SQL 2005. It is not being used by anyone but me for the
> purpose of learning one or two things about 2005. Here is what I deem
> odd:
> If i do a full backup of the database, I get a backup of 7 gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
> If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> gb.
>
How large is the transaction log?
I believe it's backing up the entire transaction log PLUS any changes in the
database.
> I was started to see a pattern. I can guarantee there are no other
> users and that I didn't change the database in between backups.
> tia,
> Steve
>|||Sorry about the lag and I appreciate the responses.
The DB is currently ~7.6 gb.
The Transaction Log is currently 5 mb.
I have tried shrinking the DB and Log files (shrinking the whole DB and
each file individually)
This is on a test system, so I have control over the database changing.
It isn't. I was literally doing the differential immediately after
the full backup.
Thanks for your help.
Steve.
Greg D. Moore (Strider) wrote:
> "Not the Face" <nottheface@.gmail.com> wrote in message
> news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
> >I have a db that is currently about 7 gb. It is currently on a test
> > instance of SQL 2005. It is not being used by anyone but me for the
> > purpose of learning one or two things about 2005. Here is what I deem
> > odd:
> >
> > If i do a full backup of the database, I get a backup of 7 gb.
> > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > gb.
> > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > gb.
> > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > gb.
> >
> How large is the transaction log?
> I believe it's backing up the entire transaction log PLUS any changes in the
> database.
>
> > I was started to see a pattern. I can guarantee there are no other
> > users and that I didn't change the database in between backups.
> >
> > tia,
> > Steve
> >|||Yeah. So here was the problem.
Turns out that if you Shrink the DB and Log files after the backup, it
wants to make a really large differential backup for some reason.
Even though you have *changed* the data at all.
You just *moved* the data around a bit. You know. All of it (most of
it anyway).
Woops. I moved the Shrinks in front of the backup and now my
differential is 1,121 kb. Seems a bit more reasonable.
Thanks for the pointing of the fingers in the right direction.
Steve.
On Jan 17, 10:45 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> 4.7GB does seem large for a differential backup. If you ran the backups as a
> script such as:
> BACKUP DATABASE [AdventureWorks] TO DISK => N'C:\Backups\AdventureworksFull.bak' WITH NOFORMAT, NOINIT, NAME => N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK => N'C:\Backups\AdventureworksDiff1.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK => N'C:\Backups\AdventureworksDiff2.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> BACKUP DATABASE [AdventureWorks] TO DISK => N'C:\Backups\AdventureworksDiff3.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT,
> NAME = N'AdventureWorks-Differential Database Backup', SKIP, NOREWIND,
> NOUNLOAD, STATS = 10
> GO
> Then a directory of C:\backups gives
> Directory of C:\Backups
> 17/01/2007 15:18 <DIR> .
> 17/01/2007 15:18 <DIR> ..
> 17/01/2007 15:18 1,133,056 AdventureworksDiff1.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff2.bak
> 17/01/2007 15:18 1,133,056 AdventureworksDiff3.bak
> 17/01/2007 15:18 171,002,368 AdventureworksFull.bak
> 4 File(s) 174,401,536 bytes
> 2 Dir(s) 1,241,235,456 bytes free
> This shows what you would expect.
> John
>
> "Not the Face" wrote:
> > Sorry about the lag and I appreciate the responses.
> > The DB is currently ~7.6 gb.
> > The Transaction Log is currently 5 mb.
> > I have tried shrinking the DB and Log files (shrinking the whole DB and
> > each file individually)
> > This is on a test system, so I have control over the database changing.
> > It isn't. I was literally doing the differential immediately after
> > the full backup.
> > Thanks for your help.
> > Steve.
> > Greg D. Moore (Strider) wrote:
> > > "Not the Face" <notthef...@.gmail.com> wrote in message
> > >news:1166552868.097104.258360@.73g2000cwn.googlegroups.com...
> > > >I have a db that is currently about 7 gb. It is currently on a test
> > > > instance of SQL 2005. It is not being used by anyone but me for the
> > > > purpose of learning one or two things about 2005. Here is what I deem
> > > > odd:
> > > > If i do a full backup of the database, I get a backup of 7 gb.
> > > > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > > > gb.
> > > > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > > > gb.
> > > > If I then IMMEDIATELY do a differential backup, I get a backup of 4.7
> > > > gb.
> > > How large is the transaction log?
> > > I believe it's backing up the entire transaction log PLUS any changes in the
> > > database.
> > > > I was started to see a pattern. I can guarantee there are no other
> > > > users and that I didn't change the database in between backups.
> > > > tia,
> > > > Steve- Hide quoted text -- Show quoted text -

A database file is demaged, no backup, anyway to fix it?

A database file (*.mdf) is demaged, no backup, anyway to fix it?
--frank, using sql2000
The only tool I know of is http://www.officerecovery.com/mssql/.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:edAwrp6IGHA.208@.tk2msftngp13.phx.gbl...
>A database file (*.mdf) is demaged, no backup, anyway to fix it?
> --frank, using sql2000
>
|||If that doesn't work, you can put the database in emergency mode and then
use DBCC PAGE to manually extract data. Very slow, very time consuming, and
no guarantees. I've only had to do it once and it wasn't very pretty.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CIeUI$IGHA.668@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> The only tool I know of is http://www.officerecovery.com/mssql/.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:edAwrp6IGHA.208@.tk2msftngp13.phx.gbl...
sql

A database file is demaged, no backup, anyway to fix it?

A database file (*.mdf) is demaged, no backup, anyway to fix it?
--frank, using sql2000The only tool I know of is http://www.officerecovery.com/mssql/.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:edAwrp6IGHA.208@.tk2msftngp13.phx.gbl...
>A database file (*.mdf) is demaged, no backup, anyway to fix it?
> --frank, using sql2000
>|||If that doesn't work, you can put the database in emergency mode and then
use DBCC PAGE to manually extract data. Very slow, very time consuming, and
no guarantees. I've only had to do it once and it wasn't very pretty.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CIeUI$IGHA.668@.TK2MSFTNGP11.phx.gbl...
> The only tool I know of is http://www.officerecovery.com/mssql/.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:edAwrp6IGHA.208@.tk2msftngp13.phx.gbl...
>>A database file (*.mdf) is demaged, no backup, anyway to fix it?
>> --frank, using sql2000

A database file is demaged, no backup, anyway to fix it?

A database file (*.mdf) is demaged, no backup, anyway to fix it?
--frank, using sql2000The only tool I know of is http://www.officerecovery.com/mssql/.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:edAwrp6IGHA.208@.tk2msftngp13.phx.gbl.
.
>A database file (*.mdf) is demaged, no backup, anyway to fix it?
> --frank, using sql2000
>|||If that doesn't work, you can put the database in emergency mode and then
use DBCC PAGE to manually extract data. Very slow, very time consuming, and
no guarantees. I've only had to do it once and it wasn't very pretty.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CIeUI$IGHA.668@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> The only tool I know of is http://www.officerecovery.com/mssql/.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Frank Lee" <Reply@.to.newsgroup> wrote in message
> news:edAwrp6IGHA.208@.tk2msftngp13.phx.gbl...

A column nullability inconsistency was detected in the metadata E

Hello, I am trying to restore a backup of a database that was taken in SQL
Server 2000 onto SQL Server 2005. It appears the action completes, but I ge
t
a list of errors. I have ran a checktable on the tables in question on both
our origional 2000 database and 2005, as well as a checkdb on the database i
n
question on our SQL Server 2005 and get no errors. Any ideas?
Database 'MyDatabase' running the upgrade step from version 539 to version
551.
A column nullability inconsistency was detected in the metadata of
indexWarning: A column nullability inconsistency was detected in the metadat
a
of index "_WA_Sys_claim_scrubber_37BBEBC3" (index_id = 34) on object ID
935062467 in database "MyDatabase". The index may be corrupt. Run DBCC
CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_abn_notifier_37BBEBC3" (index_id = 35) on object ID 935062467
in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_cptguidelines_37BBEBC3" (index_id = 36) on object ID 93506246
7
in database "Mydatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_federal_37BBEBC3" (index_id = 37) on object ID 935062467 in
database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_medicarea_37BBEBC3" (index_id = 38) on object ID 935062467 in
database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.Judging by the names of these, they appear to be auto-created statistics (an
d not indexes). I'd drop
those and then re-try the upgrade. See the DROP STATISTICS command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:5DF5A523-75C0-45E1-BE54-6901814D0110@.microsoft.com...
> Hello, I am trying to restore a backup of a database that was taken in SQ
L
> Server 2000 onto SQL Server 2005. It appears the action completes, but I
get
> a list of errors. I have ran a checktable on the tables in question on bo
th
> our origional 2000 database and 2005, as well as a checkdb on the database
in
> question on our SQL Server 2005 and get no errors. Any ideas?
> Database 'MyDatabase' running the upgrade step from version 539 to version
> 551.
> A column nullability inconsistency was detected in the metadata of
> indexWarning: A column nullability inconsistency was detected in the metad
ata
> of index "_WA_Sys_claim_scrubber_37BBEBC3" (index_id = 34) on object ID
> 935062467 in database "MyDatabase". The index may be corrupt. Run DBCC
> CHECKTABLE to verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata o
f
> index "_WA_Sys_abn_notifier_37BBEBC3" (index_id = 35) on object ID 9350624
67
> in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata o
f
> index "_WA_Sys_cptguidelines_37BBEBC3" (index_id = 36) on object ID 935062
467
> in database "Mydatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata o
f
> index "_WA_Sys_federal_37BBEBC3" (index_id = 37) on object ID 935062467 in
> database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata o
f
> index "_WA_Sys_medicarea_37BBEBC3" (index_id = 38) on object ID 935062467
in
> database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
>

A column nullability inconsistency was detected in the metadata E

Hello, I am trying to restore a backup of a database that was taken in SQL
Server 2000 onto SQL Server 2005. It appears the action completes, but I get
a list of errors. I have ran a checktable on the tables in question on both
our origional 2000 database and 2005, as well as a checkdb on the database in
question on our SQL Server 2005 and get no errors. Any ideas?
Database 'MyDatabase' running the upgrade step from version 539 to version
551.
A column nullability inconsistency was detected in the metadata of
indexWarning: A column nullability inconsistency was detected in the metadata
of index "_WA_Sys_claim_scrubber_37BBEBC3" (index_id = 34) on object ID
935062467 in database "MyDatabase". The index may be corrupt. Run DBCC
CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_abn_notifier_37BBEBC3" (index_id = 35) on object ID 935062467
in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_cptguidelines_37BBEBC3" (index_id = 36) on object ID 935062467
in database "Mydatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_federal_37BBEBC3" (index_id = 37) on object ID 935062467 in
database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of
index "_WA_Sys_medicarea_37BBEBC3" (index_id = 38) on object ID 935062467 in
database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
verify consistency.Judging by the names of these, they appear to be auto-created statistics (and not indexes). I'd drop
those and then re-try the upgrade. See the DROP STATISTICS command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:5DF5A523-75C0-45E1-BE54-6901814D0110@.microsoft.com...
> Hello, I am trying to restore a backup of a database that was taken in SQL
> Server 2000 onto SQL Server 2005. It appears the action completes, but I get
> a list of errors. I have ran a checktable on the tables in question on both
> our origional 2000 database and 2005, as well as a checkdb on the database in
> question on our SQL Server 2005 and get no errors. Any ideas?
> Database 'MyDatabase' running the upgrade step from version 539 to version
> 551.
> A column nullability inconsistency was detected in the metadata of
> indexWarning: A column nullability inconsistency was detected in the metadata
> of index "_WA_Sys_claim_scrubber_37BBEBC3" (index_id = 34) on object ID
> 935062467 in database "MyDatabase". The index may be corrupt. Run DBCC
> CHECKTABLE to verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata of
> index "_WA_Sys_abn_notifier_37BBEBC3" (index_id = 35) on object ID 935062467
> in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata of
> index "_WA_Sys_cptguidelines_37BBEBC3" (index_id = 36) on object ID 935062467
> in database "Mydatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata of
> index "_WA_Sys_federal_37BBEBC3" (index_id = 37) on object ID 935062467 in
> database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
> Warning: A column nullability inconsistency was detected in the metadata of
> index "_WA_Sys_medicarea_37BBEBC3" (index_id = 38) on object ID 935062467 in
> database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to
> verify consistency.
>sql

823 Error after the DB recovery

I just backup an ArcSDE geography database in SQL Server 2000, whose size is
about 3.5GB. The backup process was finished and checked properly, but when
it has been recovered, some errors occur:
First, when you click the data table in the enterprise manager window,
choose the design item in the right button menu, an error window come to
show: "823" and nothing. And if you choose to see all lines in the table,
you will find some data of the table has disappeared. In other words, data
recovery was not successful, but why no error in the whole process?
Second, if you click some table occasionally, it alerts: The data base has
not been connected.
Third, you can't get connection to the database in any way.
I wonder if there was something wrong underlying, our goal is to move the
database from their machine to ours, and the software environment (I mean
the SQL Server version) is totally the same. Nonetheless, we can not assure
some login users' consistence, which we are going to solve after the
recovery, but we don't take it as the critical reason for the fault.
Therefore, if some veteran in this field can offer some help, I really
appreciate.
Thanks in advance.
error 823 is I/O error %ls detected during %S_MSG at offset %#016I64x in
file '%ls'.
Do you get an error like this or merely 823? What happens when you do a dbcc
checkalloc in this database?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Alex Dai" <alexdaichn@.hotmail.com> wrote in message
news:%235b1J6WVFHA.4056@.TK2MSFTNGP15.phx.gbl...
> I just backup an ArcSDE geography database in SQL Server 2000, whose size
is
> about 3.5GB. The backup process was finished and checked properly, but
when
> it has been recovered, some errors occur:
> First, when you click the data table in the enterprise manager window,
> choose the design item in the right button menu, an error window come to
> show: "823" and nothing. And if you choose to see all lines in the table,
> you will find some data of the table has disappeared. In other words, data
> recovery was not successful, but why no error in the whole process?
> Second, if you click some table occasionally, it alerts: The data base has
> not been connected.
> Third, you can't get connection to the database in any way.
> I wonder if there was something wrong underlying, our goal is to move the
> database from their machine to ours, and the software environment (I mean
> the SQL Server version) is totally the same. Nonetheless, we can not
assure
> some login users' consistence, which we are going to solve after the
> recovery, but we don't take it as the critical reason for the fault.
> Therefore, if some veteran in this field can offer some help, I really
> appreciate.
> Thanks in advance.
>
|||Here I run DBCC CHECKDB('<mydb>', REPAIR_REBUILD), but it informs me the
database must be in single user mode, what's the problem?
"Hilary Cotter" <hilary.cotter@.gmail.com> д?
news:e$Xs42YVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> error 823 is I/O error %ls detected during %S_MSG at offset %#016I64x in
> file '%ls'.
> Do you get an error like this or merely 823? What happens when you do a
dbcc[vbcol=seagreen]
> checkalloc in this database?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Alex Dai" <alexdaichn@.hotmail.com> wrote in message
> news:%235b1J6WVFHA.4056@.TK2MSFTNGP15.phx.gbl...
size[vbcol=seagreen]
> is
> when
table,[vbcol=seagreen]
data[vbcol=seagreen]
has[vbcol=seagreen]
the[vbcol=seagreen]
mean
> assure
>
|||I have been waiting for this issue, somebody could help me out?
"Alex Dai" <alexdaichn@.hotmail.com> д?
news:ODg9OrdVFHA.3076@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Here I run DBCC CHECKDB('<mydb>', REPAIR_REBUILD), but it informs me the
> database must be in single user mode, what's the problem?
> "Hilary Cotter" <hilary.cotter@.gmail.com> д?
> news:e$Xs42YVFHA.3280@.TK2MSFTNGP09.phx.gbl...
> dbcc
> size
to
> table,
> data
> has
> the
> mean
>

7.0 to 2000 Upgrade

Is this a reasonable way to upgrade a 7.0 server to 2000?
1. Backup the databases.
2. Move the database mdf and ldf files to a neutral
location on the server's drive.
3. Upgrade the server to Win 2k and Sql Server 2000.
4. Add pertinent logins.
5. Reattach the mdf and ldf files and let sql server
upgrade the databases "on the fly".
The reason I'm asking is that I've always been ultra-safe
in times past and used the copy wizard to copy my 7.0
databases to a 2000 server first.
Is an attach/reattach considered a supported way of doing
a 7.0 to 2000 conversion?
I'm a little scared of course because if it doesn't attach
for some reason, then I can try the .BAK files and if that
doesn't work, I'm dead.It is hard to find documentation that provides it one way or another.
However, iny my experience of doing it Attach/reattaching works fine. Just
make sure you have a backup copy of your databases becauase you'll not be
able to reattach them to a SQL 7.0 server after attaching them to SQL 2000.
The only concerns I can think of have to do more with the sort order, which
would show up when people start to connect and use the system. SQL 2000
supports attaching databases with different sort orders, but some stored
procedures that create temp tables may have issues without having the
collation order specified for them. Only thorough testing will reveal a
problem like this.
My *safest method* is to use DTS to transfer the databases to another
server, then copy them back.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"CLM" <anonymous@.discussions.microsoft.com> wrote in message
news:121bf01c3f57c$ec04e740$a501280a@.phx
.gbl...
> Is this a reasonable way to upgrade a 7.0 server to 2000?
> 1. Backup the databases.
> 2. Move the database mdf and ldf files to a neutral
> location on the server's drive.
> 3. Upgrade the server to Win 2k and Sql Server 2000.
> 4. Add pertinent logins.
> 5. Reattach the mdf and ldf files and let sql server
> upgrade the databases "on the fly".
> The reason I'm asking is that I've always been ultra-safe
> in times past and used the copy wizard to copy my 7.0
> databases to a 2000 server first.
> Is an attach/reattach considered a supported way of doing
> a 7.0 to 2000 conversion?
> I'm a little scared of course because if it doesn't attach
> for some reason, then I can try the .BAK files and if that
> doesn't work, I'm dead.
>

7.0 to 2000 Upgrade

Is this a reasonable way to upgrade a 7.0 server to 2000?
1. Backup the databases.
2. Move the database mdf and ldf files to a neutral
location on the server's drive.
3. Upgrade the server to Win 2k and Sql Server 2000.
4. Add pertinent logins.
5. Reattach the mdf and ldf files and let sql server
upgrade the databases "on the fly".
The reason I'm asking is that I've always been ultra-safe
in times past and used the copy wizard to copy my 7.0
databases to a 2000 server first.
Is an attach/reattach considered a supported way of doing
a 7.0 to 2000 conversion?
I'm a little scared of course because if it doesn't attach
for some reason, then I can try the .BAK files and if that
doesn't work, I'm dead.It is hard to find documentation that provides it one way or another.
However, iny my experience of doing it Attach/reattaching works fine. Just
make sure you have a backup copy of your databases becauase you'll not be
able to reattach them to a SQL 7.0 server after attaching them to SQL 2000.
The only concerns I can think of have to do more with the sort order, which
would show up when people start to connect and use the system. SQL 2000
supports attaching databases with different sort orders, but some stored
procedures that create temp tables may have issues without having the
collation order specified for them. Only thorough testing will reveal a
problem like this.
My *safest method* is to use DTS to transfer the databases to another
server, then copy them back.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"CLM" <anonymous@.discussions.microsoft.com> wrote in message
news:121bf01c3f57c$ec04e740$a501280a@.phx.gbl...
> Is this a reasonable way to upgrade a 7.0 server to 2000?
> 1. Backup the databases.
> 2. Move the database mdf and ldf files to a neutral
> location on the server's drive.
> 3. Upgrade the server to Win 2k and Sql Server 2000.
> 4. Add pertinent logins.
> 5. Reattach the mdf and ldf files and let sql server
> upgrade the databases "on the fly".
> The reason I'm asking is that I've always been ultra-safe
> in times past and used the copy wizard to copy my 7.0
> databases to a 2000 server first.
> Is an attach/reattach considered a supported way of doing
> a 7.0 to 2000 conversion?
> I'm a little scared of course because if it doesn't attach
> for some reason, then I can try the .BAK files and if that
> doesn't work, I'm dead.
>|||If you are going to upgrade Windows, I'd recommend
reinstalling everything from scratch. Our experience with
Windows upgrading in place is that it works successfully
30% of the time or less. And if it fails, it can fail
really hard (blue screen on a system that will never boot
again, i.e., total data loss).
SQL Server upgrades (without upgrading the OS) usually
work. One thing you *MUST* do, however, is install the
latest MDAC prior to beginning the SQL Server upgrade.
If you are worried about the integrity of the databases or
their backups, be sure to do a DBCC CHECKDB on all your
databases before you start this. Fix any problems before
taking your backups or detaching.
Also, keep records of all the logical *and* physical file
names (including the disk drive letters/file paths) for
*every* database file! You might need to know these to
get a restore or attach to work. This information can be
recorded from your existing installation only as long as
the databases are still attached, so get it first.
Regarding one of your questions: Both the attach and
restore SQL operations will automatically convert a SQL 7
database to SQL 2000.
P.S. It isn't really necessary to detach the databases to
copy them. You can copy the files safely as long as SQL
Server is stopped.
--Scott
>--Original Message--
>Is this a reasonable way to upgrade a 7.0 server to 2000?
>1. Backup the databases.
>2. Move the database mdf and ldf files to a neutral
>location on the server's drive.
>3. Upgrade the server to Win 2k and Sql Server 2000.
>4. Add pertinent logins.
>5. Reattach the mdf and ldf files and let sql server
>upgrade the databases "on the fly".
>The reason I'm asking is that I've always been ultra-safe
>in times past and used the copy wizard to copy my 7.0
>databases to a 2000 server first.
>Is an attach/reattach considered a supported way of doing
>a 7.0 to 2000 conversion?
>I'm a little scared of course because if it doesn't
attach
>for some reason, then I can try the .BAK files and if
that
>doesn't work, I'm dead.
>.
>

7.0 backup format

is .bak the fileformat for SQL 7.0 backup formats...I apologize if i
sound too naive.
yes if you mean the extension for full backups
|||The extension of a file name (like .bak) doesn't say anything about the contents of a file. I can
have an excel file named "a.doc", for instance. Now, some applications might enforce a special
extension. SQL Server doesn't. There is a convention, however, to name database backup using "bak"
as extension.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132614115.754785.181850@.o13g2000cwo.googlegr oups.com...
> is .bak the fileformat for SQL 7.0 backup formats...I apologize if i
> sound too naive.
>

7.0 backup format

is .bak the fileformat for SQL 7.0 backup formats...I apologize if i
sound too naive.yes if you mean the extension for full backups|||The extension of a file name (like .bak) doesn't say anything about the contents of a file. I can
have an excel file named "a.doc", for instance. Now, some applications might enforce a special
extension. SQL Server doesn't. There is a convention, however, to name database backup using "bak"
as extension.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132614115.754785.181850@.o13g2000cwo.googlegroups.com...
> is .bak the fileformat for SQL 7.0 backup formats...I apologize if i
> sound too naive.
>

7.0 backup format

is .bak the fileformat for SQL 7.0 backup formats...I apologize if i
sound too naive.yes if you mean the extension for full backups|||The extension of a file name (like .bak) doesn't say anything about the cont
ents of a file. I can
have an excel file named "a.doc", for instance. Now, some applications might
enforce a special
extension. SQL Server doesn't. There is a convention, however, to name datab
ase backup using "bak"
as extension.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132614115.754785.181850@.o13g2000cwo.googlegroups.com...
> is .bak the fileformat for SQL 7.0 backup formats...I apologize if i
> sound too naive.
>

6.5 to 2K Restore

Hello:
Is there anyway to restore a SQL 6.5 database backup
directly into a SQL 2000 DB?
Thanks
BrennanNo. You must go thru the upgrade path which means you first need to restore
to a 6.5 machine.
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/deploy/upgrdmigrate/sqlugrd.asp
Andrew J. Kelly
SQL Server MVP
"Brennan" <anonymous@.discussions.microsoft.com> wrote in message
news:0c0d01c39442$9edb9950$a001280a@.phx.gbl...
> Hello:
> Is there anyway to restore a SQL 6.5 database backup
> directly into a SQL 2000 DB?
> Thanks
> Brennan

5 Day Backup Maintenance Plan Help?

I am just getting used to SQL 2005 coming from SQL 2000 and can't figure
something out. In SQL 2000 I can go into the wizard and create a maintenance
plan to do a backup every night keeping 5 day's worth of (.bak) backup files,
each file having it's own naming convention using the date and time stamp as
part of the file name. Can't be any simpler. From there I backup each of
the .bak files to a tape.
When I go into the maintenance planner in the new SQL 2005 manager I can
create a backup plan but I can't seem to get it to create the same type of
thing. All it wants to do is overwrite the previous nights file, thus I only
have 1 file (most recent date) in my backup directory. What am I doing
wrong? Is there a simple Q article talking about this?
Thanks!
-Richard K
In the backup databases task you need to check the option for "Create a
Backup file for every database"
Andrew J. Kelly SQL MVP
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:F62187FE-7677-4408-8062-047B40D5EFC9@.microsoft.com...
>I am just getting used to SQL 2005 coming from SQL 2000 and can't figure
> something out. In SQL 2000 I can go into the wizard and create a
> maintenance
> plan to do a backup every night keeping 5 day's worth of (.bak) backup
> files,
> each file having it's own naming convention using the date and time stamp
> as
> part of the file name. Can't be any simpler. From there I backup each of
> the .bak files to a tape.
> When I go into the maintenance planner in the new SQL 2005 manager I can
> create a backup plan but I can't seem to get it to create the same type of
> thing. All it wants to do is overwrite the previous nights file, thus I
> only
> have 1 file (most recent date) in my backup directory. What am I doing
> wrong? Is there a simple Q article talking about this?
> Thanks!
> -Richard K
>
|||OK, got that part but no where in the wizard can I specify that I want to
hold 5 days worth of backups. That's what is confusing. Other than that I
like the new SQL Server Management Studio. I just have to get used to it.
"Andrew J. Kelly" wrote:

> In the backup databases task you need to check the option for "Create a
> Backup file for every database"
> --
> Andrew J. Kelly SQL MVP
> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
> news:F62187FE-7677-4408-8062-047B40D5EFC9@.microsoft.com...
>
>
|||Richard,
Pre SP2 you have to Modify the maintenance plan to add the Maintenance
Cleanup Task which will only delete down to Days.
SP2 CTP includes this Maintenance Cleanup Task in the wizard and allows the
delete to be in Hours.
Chris
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:2264AE56-C465-4DDD-8FE6-546D2C14C30F@.microsoft.com...[vbcol=seagreen]
> OK, got that part but no where in the wizard can I specify that I want to
> hold 5 days worth of backups. That's what is confusing. Other than that
> I
> like the new SQL Server Management Studio. I just have to get used to it.
> "Andrew J. Kelly" wrote:
|||OK, got it. I needed to add in a "cleanup task" to my routine. Very similar
to DTS package setup so very cool and flexible.
Thanks for the help Chris!!!
-Richard
"Chris Wood" wrote:

> Richard,
> Pre SP2 you have to Modify the maintenance plan to add the Maintenance
> Cleanup Task which will only delete down to Days.
> SP2 CTP includes this Maintenance Cleanup Task in the wizard and allows the
> delete to be in Hours.
> Chris
> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
> news:2264AE56-C465-4DDD-8FE6-546D2C14C30F@.microsoft.com...
>
>

4GB sql file

I have a 4GB SQL db that is used for Backup Exec, but the file has
reached the maximum file size for the file system. I have tried
upgrading from 2000 to 2005 but I do not get any more options, such as
shrink or to do maintenance on the db through the Enterprise manager.
Is my install stuck on the msde license and won't let me install the
full blown sql server until I reboot the server? ThanksHi
If you have 4GB database and thinking to upgrare to SQL Server 2005 , you DO
NOT have MSDE ,MSDE has a limit 2GB
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
<jooooooohn@.gmail.com> wrote in message
news:1191878238.775355.215520@.r29g2000hsg.googlegroups.com...
>I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
>|||jooooooohn@.gmail.com wrote:
> I have a 4GB SQL db that is used for Backup Exec, but the file has
> reached the maximum file size for the file system. I have tried
> upgrading from 2000 to 2005 but I do not get any more options, such as
> shrink or to do maintenance on the db through the Enterprise manager.
> Is my install stuck on the msde license and won't let me install the
> full blown sql server until I reboot the server? Thanks
It's not clear if you are hitting the limit of the FAT32 file system or
the limit of MSDE/SQL Express. If the file system is the problem, then
change it to NTFS. If you already use NTFS, then try to shrink the file
using OSQL.EXE. For example, you can try DBCC SHRINKFILE(1,1). Which is
the big file: the MDF (data file) or the LDF (log file) ?
For more information, see Books Online topics for DBCC SHRINKFILE and
DBCC SHRINKDATABASE.
--
Razvan Socol
SQL Server MVP

3rd party software

Does anyone know any software to backup single tables ? i recall one software that extracts the data into a text file called SQLinsert or something but wondering if they are others around ?Rey SQLLiteSpeed from DBAssociates (I think it has been bought by IMCEDA).

3rd party backup/restore

I'm looking for recommendations for 3rd party backup/restore software for
SQL Server 2000.
The software must be cluster aware. Ease and speed of recoverability are
most important. I am not so concerned with compression ratio but rather the
ease of which junior staff could restore databases at crunch time. Point in
time restore is essential.
thanks to any one who could offer an opinion.
A follow-up if I may. Do any of these products allow you to recovers users
at the server level when a restoring a database to a different server. It's
a hassle scripting and transferring logins.
"Terri" <terri@.cybernets.com> wrote in message
news:f1vj1k$teb$1@.reader2.nmix.net...
> I'm looking for recommendations for 3rd party backup/restore software for
> SQL Server 2000.
> The software must be cluster aware. Ease and speed of recoverability are
> most important. I am not so concerned with compression ratio but rather
the
> ease of which junior staff could restore databases at crunch time. Point
in
> time restore is essential.
> thanks to any one who could offer an opinion.
>
|||"Terri" <terri@.cybernets.com> wrote in message
news:f1vj1k$teb$1@.reader2.nmix.net...
> I'm looking for recommendations for 3rd party backup/restore software for
> SQL Server 2000.
>
Any particular reason you can't use the built in backup tools for SQL Server
2000?
In any case, I'd recommend Litespeed for SQL Server.

> The software must be cluster aware. Ease and speed of recoverability are
> most important. I am not so concerned with compression ratio but rather
> the
> ease of which junior staff could restore databases at crunch time. Point
> in
> time restore is essential.
> thanks to any one who could offer an opinion.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||I am very happy with the price, the speed, and the ease of restores and
backups using RED GATE. Instead of bak sizes of 80+ gig, they are about 10
gig. Its a great piece of software.
Regards,
Jamie
"Terri" wrote:

> A follow-up if I may. Do any of these products allow you to recovers users
> at the server level when a restoring a database to a different server. It's
> a hassle scripting and transferring logins.
>
> "Terri" <terri@.cybernets.com> wrote in message
> news:f1vj1k$teb$1@.reader2.nmix.net...
> the
> in
>
>
|||"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:OURUS2ykHHA.3484@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>
> "Terri" <terri@.cybernets.com> wrote in message
> news:f1vj1k$teb$1@.reader2.nmix.net...
for
> Any particular reason you can't use the built in backup tools for SQL
Server
> 2000?
I'm hoping it would be easier for an administrator to make a timely restore.
The performance boost can't hurt.

> In any case, I'd recommend Litespeed for SQL Server.
Thanks for your response, I'll take a look.
|||"Terri" <terri@.cybernets.com> wrote in message
news:f2070q$2i9$1@.reader2.nmix.net...
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message
> news:OURUS2ykHHA.3484@.TK2MSFTNGP02.phx.gbl...
> for
> Server
> I'm hoping it would be easier for an administrator to make a timely
> restore.
> The performance boost can't hurt.
Easier?
Can't say I ever found
RESTORE DATABASE FOO from file="backup.bak" with NORECOVERY
all that complex :-)
But.. the performance boost is definitely a plus.

>
> Thanks for your response, I'll take a look.
>
|||Are Red Gate and Lightspeed cluster aware? Also, can those products restore
to a server on which the product does not reside?
Terri, if you REALLY want ease/speed of recoverability, get familiar with
the backupset system table and create a restore database script that takes a
parameter of @.DBName and creates all necessary RESTORE statements to
completely recover a database. Holy-Shit time is NOT the time to be
futzing with an interface you aren't familiar with. I used such a mechanism
(albeit a BIT more complex and hand-crafted at crunch time) earlier this
year to restore 719 databases having 6 backup files each to two different
servers in about 4 hours total time. :-O
TheSQLGuru
President
Indicium Resources, Inc.
"Terri" <terri@.cybernets.com> wrote in message
news:f1vj1k$teb$1@.reader2.nmix.net...
> I'm looking for recommendations for 3rd party backup/restore software for
> SQL Server 2000.
> The software must be cluster aware. Ease and speed of recoverability are
> most important. I am not so concerned with compression ratio but rather
> the
> ease of which junior staff could restore databases at crunch time. Point
> in
> time restore is essential.
> thanks to any one who could offer an opinion.
>

3rd party backup/restore

I'm looking for recommendations for 3rd party backup/restore software for
SQL Server 2000.
The software must be cluster aware. Ease and speed of recoverability are
most important. I am not so concerned with compression ratio but rather the
ease of which junior staff could restore databases at crunch time. Point in
time restore is essential.
thanks to any one who could offer an opinion.A follow-up if I may. Do any of these products allow you to recovers users
at the server level when a restoring a database to a different server. It's
a hassle scripting and transferring logins.
"Terri" <terri@.cybernets.com> wrote in message
news:f1vj1k$teb$1@.reader2.nmix.net...
> I'm looking for recommendations for 3rd party backup/restore software for
> SQL Server 2000.
> The software must be cluster aware. Ease and speed of recoverability are
> most important. I am not so concerned with compression ratio but rather
the
> ease of which junior staff could restore databases at crunch time. Point
in
> time restore is essential.
> thanks to any one who could offer an opinion.
>|||"Terri" <terri@.cybernets.com> wrote in message
news:f1vj1k$teb$1@.reader2.nmix.net...
> I'm looking for recommendations for 3rd party backup/restore software for
> SQL Server 2000.
>
Any particular reason you can't use the built in backup tools for SQL Server
2000?
In any case, I'd recommend Litespeed for SQL Server.

> The software must be cluster aware. Ease and speed of recoverability are
> most important. I am not so concerned with compression ratio but rather
> the
> ease of which junior staff could restore databases at crunch time. Point
> in
> time restore is essential.
> thanks to any one who could offer an opinion.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I am very happy with the price, the speed, and the ease of restores and
backups using RED GATE. Instead of bak sizes of 80+ gig, they are about 10
gig. Its a great piece of software.
--
Regards,
Jamie
"Terri" wrote:

> A follow-up if I may. Do any of these products allow you to recovers users
> at the server level when a restoring a database to a different server. It'
s
> a hassle scripting and transferring logins.
>
> "Terri" <terri@.cybernets.com> wrote in message
> news:f1vj1k$teb$1@.reader2.nmix.net...
> the
> in
>
>|||"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:OURUS2ykHHA.3484@.TK2MSFTNGP02.phx.gbl...
>
> "Terri" <terri@.cybernets.com> wrote in message
> news:f1vj1k$teb$1@.reader2.nmix.net...
for[vbcol=seagreen]
> Any particular reason you can't use the built in backup tools for SQL
Server
> 2000?
I'm hoping it would be easier for an administrator to make a timely restore.
The performance boost can't hurt.

> In any case, I'd recommend Litespeed for SQL Server.
Thanks for your response, I'll take a look.|||"Terri" <terri@.cybernets.com> wrote in message
news:f2070q$2i9$1@.reader2.nmix.net...
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
> message
> news:OURUS2ykHHA.3484@.TK2MSFTNGP02.phx.gbl...
> for
> Server
> I'm hoping it would be easier for an administrator to make a timely
> restore.
> The performance boost can't hurt.
Easier?
Can't say I ever found
RESTORE DATABASE FOO from file="backup.bak" with NORECOVERY
all that complex :-)
But.. the performance boost is definitely a plus.

>
> Thanks for your response, I'll take a look.
>|||Are Red Gate and Lightspeed cluster aware? Also, can those products restore
to a server on which the product does not reside?
Terri, if you REALLY want ease/speed of recoverability, get familiar with
the backupset system table and create a restore database script that takes a
parameter of @.DBName and creates all necessary RESTORE statements to
completely recover a database. Holy-Shit time is NOT the time to be
futzing with an interface you aren't familiar with. I used such a mechanism
(albeit a BIT more complex and hand-crafted at crunch time) earlier this
year to restore 719 databases having 6 backup files each to two different
servers in about 4 hours total time. :-O
TheSQLGuru
President
Indicium Resources, Inc.
"Terri" <terri@.cybernets.com> wrote in message
news:f1vj1k$teb$1@.reader2.nmix.net...
> I'm looking for recommendations for 3rd party backup/restore software for
> SQL Server 2000.
> The software must be cluster aware. Ease and speed of recoverability are
> most important. I am not so concerned with compression ratio but rather
> the
> ease of which junior staff could restore databases at crunch time. Point
> in
> time restore is essential.
> thanks to any one who could offer an opinion.
>