Restoring a BAK Do I Need the Schema ?

Hello everyone,
I'm new to SQL Server 2005. I'm using the Express version.
A co-worker sent me a BAK file which I need to restore. My question is, do I
need to have the database structure already in place before I can do the
restore? I was wondering if there's a way to simply restore the BAK file
into an empty database and have underlying tables/sprocs automatically
created as part of the restore process. Is this possible?
Thanks very much.
Yes. You can either just restore the bak file and let it create what it
needs or you can
1. Create empty, new database with the same name
2. Do a backup of the same name as your backup
3. Put the co-worker's backup in place of the backup in 2
4. Restore the backup and tell it to overwrite an existing DB.
Alan Foxmore wrote:
> Hello everyone,
> I'm new to SQL Server 2005. I'm using the Express version.
> A co-worker sent me a BAK file which I need to restore. My question is, do I
> need to have the database structure already in place before I can do the
> restore? I was wondering if there's a way to simply restore the BAK file
> into an empty database and have underlying tables/sprocs automatically
> created as part of the restore process. Is this possible?
> Thanks very much.
>
|||Actually, step 1 to 3 are unnecessary and a waste of time. The restore process will create the
database for you.
You might need to investigate what files were used for the original database (RESTORE HEADERONLY and
RESTORE FILELISTONLY) and then specify new physical filenames when you restore (using the MOVE
option of the RESTORE command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MSDN" <Grunt@.newsgroup.nospam> wrote in message news:eAc3EaxkHHA.492@.TK2MSFTNGP04.phx.gbl...[vbcol=seagreen]
> Yes. You can either just restore the bak file and let it create what it needs or you can
> 1. Create empty, new database with the same name
> 2. Do a backup of the same name as your backup
> 3. Put the co-worker's backup in place of the backup in 2
> 4. Restore the backup and tell it to overwrite an existing DB.
>
> Alan Foxmore wrote:
|||I always wondered about that. I've taken to just restoring the file as I
suggested. However, when I inherited the SQL stuff that was the
technique the current admin used and recommended. It didn't make much
sense to me but at the time he new much more than I did <G>.
Tibor Karaszi wrote:
> Actually, step 1 to 3 are unnecessary and a waste of time. The restore
> process will create the database for you.
> You might need to investigate what files were used for the original
> database (RESTORE HEADERONLY and RESTORE FILELISTONLY) and then specify
> new physical filenames when you restore (using the MOVE option of the
> RESTORE command).
>
|||Perhaps that person had worked with the old architecture (6.5 and older), where you actually *had*
to create the database first?
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MSDN" <Grunt@.newsgroup.nospam> wrote in message news:%23e7CJmykHHA.568@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>I always wondered about that. I've taken to just restoring the file as I suggested. However, when
>I inherited the SQL stuff that was the technique the current admin used and recommended. It didn't
>make much sense to me but at the time he new much more than I did <G>.
>
> Tibor Karaszi wrote:
|||That would explain it. I picked it up when I migrated a SQL 7 to SQL
2000. He did have 6.5 or earlier experience. Thanks.
Tibor Karaszi wrote:
> Perhaps that person had worked with the old architecture (6.5 and
> older), where you actually *had* to create the database first?
> :-)
>
|||Thank you all... It works now. I had to change to the account under which
the service was running to the Local Account.
Thanks again.
"Alan Foxmore" <afoxmore@.yahoo.com> wrote in message
news:upPyLKxkHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Hello everyone,
> I'm new to SQL Server 2005. I'm using the Express version.
> A co-worker sent me a BAK file which I need to restore. My question is, do
> I need to have the database structure already in place before I can do the
> restore? I was wondering if there's a way to simply restore the BAK file
> into an empty database and have underlying tables/sprocs automatically
> created as part of the restore process. Is this possible?
> Thanks very much.
>

.Bak or .mdf..What's the best way to move DB on another Server

Hi all
I can copy my .BAK on my new server and then restore the Database, or
copy my .mdf and .ldf and then reattach my Database...but what is the
best method.
What can I do for my DTS.
Please give me some answers!!!
ThanksI'd prefer copy .bak files and then perfom restore command.
"Guy Tanguay" <guy_tanguay@.hotmail.com> wrote in message
news:3b53412a.0309090505.643c61bc@.posting.google.com...
> Hi all
> I can copy my .BAK on my new server and then restore the Database, or
> copy my .mdf and .ldf and then reattach my Database...but what is the
> best method.
> What can I do for my DTS.
> Please give me some answers!!!
> Thanks|||I agree with Uri, since the bak files are smaller. You can always move the
mdf file with its logs files and attach them as well.
For your DTS packages you can open them and perform a SAVE AS command on the
File Menu to the new Server. For your jobs you can script them out.
Yovan Fernandez
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23jHIjOtdDHA.2640@.TK2MSFTNGP09.phx.gbl...
> I'd prefer copy .bak files and then perfom restore command.
>
> "Guy Tanguay" <guy_tanguay@.hotmail.com> wrote in message
> news:3b53412a.0309090505.643c61bc@.posting.google.com...
> > Hi all
> >
> > I can copy my .BAK on my new server and then restore the Database, or
> > copy my .mdf and .ldf and then reattach my Database...but what is the
> > best method.
> >
> > What can I do for my DTS.
> >
> > Please give me some answers!!!
> > Thanks
>|||Thank U everybody
Long Life to SQL Server

.BAK is growing up

Hello ,Folks
SQL Server 2000 (sp3)
I have posted this question yesterday and still have not got an idea why it
is happening.
I have a database 11GB and every night I do a full backup (WITH INIT) of
the database that .BAK file was always around 11 GB as well
Two days ago it finnished (backup operation) with 16GB .bak file. The
.MDF,.LDF files have not grown since.
The question is WHY is my .BAK file grew so much? What could be a
reason?
ThanksHi Alex
From the sp_spaceused information you figures don't correspond
database_name database_size unallocated space
-- -- --
dbname 28511.69 MB 519.01 MB
reserved data index_size unused
-- -- -- --
23568632 KB 6408816 KB 6066240 KB 11093576 KB
This says your database is 27GB and there is 10GB unused space, so 16GB for
a backup seems reasonable.
John
"Alex" wrote:
> Hello ,Folks
> SQL Server 2000 (sp3)
> I have posted this question yesterday and still have not got an idea why it
> is happening.
> I have a database 11GB and every night I do a full backup (WITH INIT) of
> the database that .BAK file was always around 11 GB as well
> Two days ago it finnished (backup operation) with 16GB .bak file. The
> ..MDF,.LDF files have not grown since.
> The question is WHY is my .BAK file grew so much? What could be a
> reason?
> Thanks
>
>|||John
Yes , you are right, the database contains three files , however , it has
been all time that the .BAK file was 11GB , so what happened at night to
grow to 16GB?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0D7D6906-B6E8-47DD-AED0-21F101C1571C@.microsoft.com...
> Hi Alex
> From the sp_spaceused information you figures don't correspond
> database_name database_size unallocated space
> -- -- --
> dbname 28511.69 MB 519.01 MB
> reserved data index_size unused
> -- -- -- --
> 23568632 KB 6408816 KB 6066240 KB 11093576 KB
>
> This says your database is 27GB and there is 10GB unused space, so 16GB
> for
> a backup seems reasonable.
> John
> "Alex" wrote:
>> Hello ,Folks
>> SQL Server 2000 (sp3)
>> I have posted this question yesterday and still have not got an idea why
>> it
>> is happening.
>> I have a database 11GB and every night I do a full backup (WITH INIT)
>> of
>> the database that .BAK file was always around 11 GB as well
>> Two days ago it finnished (backup operation) with 16GB .bak file. The
>> ..MDF,.LDF files have not grown since.
>> The question is WHY is my .BAK file grew so much? What could be a
>> reason?
>> Thanks
>>|||Hi Alex
The simple answer would be that there is more information in the file!
Unless you are archiving data from your database then you would expect a
steady growth. A 4GB increment would indicate something abnormal has
happened, such as someone copyied a large table. To find out space used by
each table you could run something like:
CREATE TABLE #spaceused ( name nvarchar(128),
rows char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18),
reservedK AS CAST(LEFT(reserved,charindex(' ', reserved)-1) AS BIGINT) )
EXEC sp_msforeachtable 'INSERT INTO #spaceused ( name, rows, reserved, data,
index_size, unused ) EXEC sp_spaceused [?]'
SELECT * FROM #spaceused
ORDER BY reservedK DESC
DROP TABLE #spaceused
John
"Alex" wrote:
> John
> Yes , you are right, the database contains three files , however , it has
> been all time that the .BAK file was 11GB , so what happened at night to
> grow to 16GB?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0D7D6906-B6E8-47DD-AED0-21F101C1571C@.microsoft.com...
> > Hi Alex
> >
> > From the sp_spaceused information you figures don't correspond
> >
> > database_name database_size unallocated space
> > -- -- --
> > dbname 28511.69 MB 519.01 MB
> >
> > reserved data index_size unused
> > -- -- -- --
> > 23568632 KB 6408816 KB 6066240 KB 11093576 KB
> >
> >
> > This says your database is 27GB and there is 10GB unused space, so 16GB
> > for
> > a backup seems reasonable.
> >
> > John
> >
> > "Alex" wrote:
> >
> >> Hello ,Folks
> >>
> >> SQL Server 2000 (sp3)
> >>
> >> I have posted this question yesterday and still have not got an idea why
> >> it
> >> is happening.
> >>
> >> I have a database 11GB and every night I do a full backup (WITH INIT)
> >> of
> >> the database that .BAK file was always around 11 GB as well
> >> Two days ago it finnished (backup operation) with 16GB .bak file. The
> >> ..MDF,.LDF files have not grown since.
> >>
> >> The question is WHY is my .BAK file grew so much? What could be a
> >> reason?
> >>
> >> Thanks
> >>
> >>
> >>
>
>

.BAK is growing up

Hello ,Folks
SQL Server 2000 (sp3)
I have posted this question yesterday and still have not got an idea why it
is happening.
I have a database 11GB and every night I do a full backup (WITH INIT) of
the database that .BAK file was always around 11 GB as well
Two days ago it finnished (backup operation) with 16GB .bak file. The
.MDF,.LDF files have not grown since.
The question is WHY is my .BAK file grew so much? What could be a
reason?
ThanksHi Alex
From the sp_spaceused information you figures don't correspond
database_name database_size unallocated space
-- -- --
dbname 28511.69 MB 519.01 MB
reserved data index_size unused
-- -- -- --
23568632 KB 6408816 KB 6066240 KB 11093576 KB
This says your database is 27GB and there is 10GB unused space, so 16GB for
a backup seems reasonable.
John
"Alex" wrote:

> Hello ,Folks
> SQL Server 2000 (sp3)
> I have posted this question yesterday and still have not got an idea why
it
> is happening.
> I have a database 11GB and every night I do a full backup (WITH INIT) of
> the database that .BAK file was always around 11 GB as well
> Two days ago it finnished (backup operation) with 16GB .bak file. The
> ..MDF,.LDF files have not grown since.
> The question is WHY is my .BAK file grew so much? What could be a
> reason?
> Thanks
>
>|||John
Yes , you are right, the database contains three files , however , it has
been all time that the .BAK file was 11GB , so what happened at night to
grow to 16GB?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0D7D6906-B6E8-47DD-AED0-21F101C1571C@.microsoft.com...[vbcol=seagreen]
> Hi Alex
> From the sp_spaceused information you figures don't correspond
> database_name database_size unallocated space
> -- -- --
> dbname 28511.69 MB 519.01 MB
> reserved data index_size unused
> -- -- -- --
> 23568632 KB 6408816 KB 6066240 KB 11093576 KB
>
> This says your database is 27GB and there is 10GB unused space, so 16GB
> for
> a backup seems reasonable.
> John
> "Alex" wrote:
>|||Hi Alex
The simple answer would be that there is more information in the file!
Unless you are archiving data from your database then you would expect a
steady growth. A 4GB increment would indicate something abnormal has
happened, such as someone copyied a large table. To find out space used by
each table you could run something like:
CREATE TABLE #spaceused ( name nvarchar(128),
rows char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18),
reservedK AS CAST(LEFT(reserved,charindex(' ', reserved)-1) AS BIGINT) )
EXEC sp_msforeachtable 'INSERT INTO #spaceused ( name, rows, reserved, data,
index_size, unused ) EXEC sp_spaceused [?]'
SELECT * FROM #spaceused
ORDER BY reservedK DESC
DROP TABLE #spaceused
John
"Alex" wrote:

> John
> Yes , you are right, the database contains three files , however , it has
> been all time that the .BAK file was 11GB , so what happened at night to
> grow to 16GB?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0D7D6906-B6E8-47DD-AED0-21F101C1571C@.microsoft.com...
>
>

.BAK is growing up

Hello ,Folks
SQL Server 2000 (sp3)
I have posted this question yesterday and still have not got an idea why it
is happening.
I have a database 11GB and every night I do a full backup (WITH INIT) of
the database that .BAK file was always around 11 GB as well
Two days ago it finnished (backup operation) with 16GB .bak file. The
..MDF,.LDF files have not grown since.
The question is WHY is my .BAK file grew so much? What could be a
reason?
Thanks
Hi Alex
From the sp_spaceused information you figures don't correspond
database_name database_size unallocated space
-- --
dbname 28511.69 MB 519.01 MB
reserved data index_size unused
-- -- -- --
23568632 KB 6408816 KB 6066240 KB 11093576 KB
This says your database is 27GB and there is 10GB unused space, so 16GB for
a backup seems reasonable.
John
"Alex" wrote:

> Hello ,Folks
> SQL Server 2000 (sp3)
> I have posted this question yesterday and still have not got an idea why it
> is happening.
> I have a database 11GB and every night I do a full backup (WITH INIT) of
> the database that .BAK file was always around 11 GB as well
> Two days ago it finnished (backup operation) with 16GB .bak file. The
> ..MDF,.LDF files have not grown since.
> The question is WHY is my .BAK file grew so much? What could be a
> reason?
> Thanks
>
>
|||John
Yes , you are right, the database contains three files , however , it has
been all time that the .BAK file was 11GB , so what happened at night to
grow to 16GB?
Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0D7D6906-B6E8-47DD-AED0-21F101C1571C@.microsoft.com...[vbcol=seagreen]
> Hi Alex
> From the sp_spaceused information you figures don't correspond
> database_name database_size unallocated space
> -- -- --
> dbname 28511.69 MB 519.01 MB
> reserved data index_size unused
> -- -- -- --
> 23568632 KB 6408816 KB 6066240 KB 11093576 KB
>
> This says your database is 27GB and there is 10GB unused space, so 16GB
> for
> a backup seems reasonable.
> John
> "Alex" wrote:
|||Hi Alex
The simple answer would be that there is more information in the file!
Unless you are archiving data from your database then you would expect a
steady growth. A 4GB increment would indicate something abnormal has
happened, such as someone copyied a large table. To find out space used by
each table you could run something like:
CREATE TABLE #spaceused ( name nvarchar(128),
rows char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18),
reservedK AS CAST(LEFT(reserved,charindex(' ', reserved)-1) AS BIGINT) )
EXEC sp_msforeachtable 'INSERT INTO #spaceused ( name, rows, reserved, data,
index_size, unused ) EXEC sp_spaceused [?]'
SELECT * FROM #spaceused
ORDER BY reservedK DESC
DROP TABLE #spaceused
John
"Alex" wrote:

> John
> Yes , you are right, the database contains three files , however , it has
> been all time that the .BAK file was 11GB , so what happened at night to
> grow to 16GB?
> Thanks
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:0D7D6906-B6E8-47DD-AED0-21F101C1571C@.microsoft.com...
>
>

.BAK files do not show

When attempting to restore a database, I choose "From Device" and browse to
the directory where my .bak files are nothing shows. However, if I rename
the files in windows without changing the file extension, the files show.
Once this occurs, Enterprise Manager stops responding with the following
error in the event log:
Hanging application mmc.exe, version 5.2.3790.0, hang module ntdll.dll,
version 5.2.3790.0, hang address 0x0000f67d.
Any help would be appreciated tremendously.
Hi Tony,
Try to choose "From Disk"
Regards,
Daniel
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:484BEC53-8F12-4114-8D09-77913CCCC42A@.microsoft.com...
> When attempting to restore a database, I choose "From Device" and browse
to
> the directory where my .bak files are nothing shows. However, if I rename
> the files in windows without changing the file extension, the files show.
> Once this occurs, Enterprise Manager stops responding with the following
> error in the event log:
> Hanging application mmc.exe, version 5.2.3790.0, hang module ntdll.dll,
> version 5.2.3790.0, hang address 0x0000f67d.
> Any help would be appreciated tremendously.

.BAK files do not show

When attempting to restore a database, I choose "From Device" and browse to
the directory where my .bak files are nothing shows. However, if I rename
the files in windows without changing the file extension, the files show.
Once this occurs, Enterprise Manager stops responding with the following
error in the event log:
Hanging application mmc.exe, version 5.2.3790.0, hang module ntdll.dll,
version 5.2.3790.0, hang address 0x0000f67d.
Any help would be appreciated tremendously.Hi Tony,
Try to choose "From Disk"
Regards,
Daniel
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:484BEC53-8F12-4114-8D09-77913CCCC42A@.microsoft.com...
> When attempting to restore a database, I choose "From Device" and browse
to
> the directory where my .bak files are nothing shows. However, if I rename
> the files in windows without changing the file extension, the files show.
> Once this occurs, Enterprise Manager stops responding with the following
> error in the event log:
> Hanging application mmc.exe, version 5.2.3790.0, hang module ntdll.dll,
> version 5.2.3790.0, hang address 0x0000f67d.
> Any help would be appreciated tremendously.

.BAK files do not show

When attempting to restore a database, I choose "From Device" and browse to
the directory where my .bak files are nothing shows. However, if I rename
the files in windows without changing the file extension, the files show.
Once this occurs, Enterprise Manager stops responding with the following
error in the event log:
Hanging application mmc.exe, version 5.2.3790.0, hang module ntdll.dll,
version 5.2.3790.0, hang address 0x0000f67d.
Any help would be appreciated tremendously.Hi Tony,
Try to choose "From Disk"
Regards,
Daniel
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:484BEC53-8F12-4114-8D09-77913CCCC42A@.microsoft.com...
> When attempting to restore a database, I choose "From Device" and browse
to
> the directory where my .bak files are nothing shows. However, if I rename
> the files in windows without changing the file extension, the files show.
> Once this occurs, Enterprise Manager stops responding with the following
> error in the event log:
> Hanging application mmc.exe, version 5.2.3790.0, hang module ntdll.dll,
> version 5.2.3790.0, hang address 0x0000f67d.
> Any help would be appreciated tremendously.

.bak file not fully restored

I restored a .bak file (to a different server) to find missing a lot of the data I thought it contained (and by the size of the file appears to contain).

Is there any other process to acertain the content of the bak file?

You could view the backup header to ensure which backups have been made to this media.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

.bak file is incorrectly formed error

I create a backup file in Enterprise Manager on SQL Server 2000. When I try
to restore this file using SQL Server 2005 Express on another machine, I get
the error "The media family on [my .bak file] is incorrectly formed. SQL
Server cannot process this media family. RESTORE HEADER is terminating
abnormally. (Error 3241)."
This same process has worked successfully several times, the last time in
September 2007. A backup file created in December 2006 will restore. What
has changed to cause this error now and how can it be fixed?
Thanking you in anticipation.I'd re-create the backup file, this should work. And, to be certain that the right steps are taken,
I'd do both the backup and the restore using TSQL commands instead of the GUI (BACKUP DTABASE and
RESTORE DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
news:481F37E4-F944-4F1C-A738-0075F7E2BD05@.microsoft.com...
>I create a backup file in Enterprise Manager on SQL Server 2000. When I try
> to restore this file using SQL Server 2005 Express on another machine, I get
> the error "The media family on [my .bak file] is incorrectly formed. SQL
> Server cannot process this media family. RESTORE HEADER is terminating
> abnormally. (Error 3241)."
> This same process has worked successfully several times, the last time in
> September 2007. A backup file created in December 2006 will restore. What
> has changed to cause this error now and how can it be fixed?
> Thanking you in anticipation.|||Thanks for this, but it didn't fix the problem.
Process is:
1 Use DTS to make a copy of operational database into laptop1 (this has SQL
server 2000). (Use of BACKUP DATABASE is prohibited on the operational
server, which is in a shared environment).
2 Make a backup with TSQL in laptop1 (I can restore from this backup in
laptop1 successfully).
3 Restore with this backup on laptop2 (running SQL Server 2005) using TSQL
gives the same error.
TSQL used is simply BACKUP/RESTORE DATABASE dbname TO/FROM DISK ='C:\...\file.bak
This process has worked successfully in the past. Last accomplished in
September 2007.
Thanking you in anticipation.
Roger
"Tibor Karaszi" wrote:
> I'd re-create the backup file, this should work. And, to be certain that the right steps are taken,
> I'd do both the backup and the restore using TSQL commands instead of the GUI (BACKUP DTABASE and
> RESTORE DATABASE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
> news:481F37E4-F944-4F1C-A738-0075F7E2BD05@.microsoft.com...
> >I create a backup file in Enterprise Manager on SQL Server 2000. When I try
> > to restore this file using SQL Server 2005 Express on another machine, I get
> > the error "The media family on [my .bak file] is incorrectly formed. SQL
> > Server cannot process this media family. RESTORE HEADER is terminating
> > abnormally. (Error 3241)."
> >
> > This same process has worked successfully several times, the last time in
> > September 2007. A backup file created in December 2006 will restore. What
> > has changed to cause this error now and how can it be fixed?
> >
> > Thanking you in anticipation.
>|||I see. So you have a 2000 backup, which restores successfully on 2000. But the same backup file
generates below error if you try to restore on 2005:
"The media family on [my .bak file] is incorrectly formed. SQL Server cannot process this media
family. RESTORE HEADER is terminating abnormally. (Error 3241)."
I would either assume that you don't really restore from the same file, or that some file copy
operation from laptop 1 to laptop 2 was unsuccessful. If you are 100% certain that it really is the
same file, then I'd open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
news:F9BB51AA-2EE6-4C2E-98FB-62A3423BB0A4@.microsoft.com...
> Thanks for this, but it didn't fix the problem.
> Process is:
> 1 Use DTS to make a copy of operational database into laptop1 (this has SQL
> server 2000). (Use of BACKUP DATABASE is prohibited on the operational
> server, which is in a shared environment).
> 2 Make a backup with TSQL in laptop1 (I can restore from this backup in
> laptop1 successfully).
> 3 Restore with this backup on laptop2 (running SQL Server 2005) using TSQL
> gives the same error.
> TSQL used is simply BACKUP/RESTORE DATABASE dbname TO/FROM DISK => 'C:\...\file.bak
> This process has worked successfully in the past. Last accomplished in
> September 2007.
> Thanking you in anticipation.
> Roger
> "Tibor Karaszi" wrote:
>> I'd re-create the backup file, this should work. And, to be certain that the right steps are
>> taken,
>> I'd do both the backup and the restore using TSQL commands instead of the GUI (BACKUP DTABASE and
>> RESTORE DATABASE).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
>> news:481F37E4-F944-4F1C-A738-0075F7E2BD05@.microsoft.com...
>> >I create a backup file in Enterprise Manager on SQL Server 2000. When I try
>> > to restore this file using SQL Server 2005 Express on another machine, I get
>> > the error "The media family on [my .bak file] is incorrectly formed. SQL
>> > Server cannot process this media family. RESTORE HEADER is terminating
>> > abnormally. (Error 3241)."
>> >
>> > This same process has worked successfully several times, the last time in
>> > September 2007. A backup file created in December 2006 will restore. What
>> > has changed to cause this error now and how can it be fixed?
>> >
>> > Thanking you in anticipation.|||"Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
news:F9BB51AA-2EE6-4C2E-98FB-62A3423BB0A4@.microsoft.com...
> Thanks for this, but it didn't fix the problem.
> Process is:
> 1 Use DTS to make a copy of operational database into laptop1 (this has
> SQL
> server 2000). (Use of BACKUP DATABASE is prohibited on the operational
> server, which is in a shared environment).
I'm curious why BACKUP DATABASE is prohibited.
In my experience DTS never quite got the "copy db" process down well.
> 2 Make a backup with TSQL in laptop1 (I can restore from this backup in
> laptop1 successfully).
> 3 Restore with this backup on laptop2 (running SQL Server 2005) using
> TSQL
> gives the same error.
Hmm, obvious questions, like "32 bit vs 64 bit?" or different language?
And compression on the 2nd laptop?
> TSQL used is simply BACKUP/RESTORE DATABASE dbname TO/FROM DISK => 'C:\...\file.bak
> This process has worked successfully in the past. Last accomplished in
> September 2007.
> Thanking you in anticipation.
> Roger
> "Tibor Karaszi" wrote:
>> I'd re-create the backup file, this should work. And, to be certain that
>> the right steps are taken,
>> I'd do both the backup and the restore using TSQL commands instead of the
>> GUI (BACKUP DTABASE and
>> RESTORE DATABASE).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in
>> message
>> news:481F37E4-F944-4F1C-A738-0075F7E2BD05@.microsoft.com...
>> >I create a backup file in Enterprise Manager on SQL Server 2000. When I
>> >try
>> > to restore this file using SQL Server 2005 Express on another machine,
>> > I get
>> > the error "The media family on [my .bak file] is incorrectly formed.
>> > SQL
>> > Server cannot process this media family. RESTORE HEADER is terminating
>> > abnormally. (Error 3241)."
>> >
>> > This same process has worked successfully several times, the last time
>> > in
>> > September 2007. A backup file created in December 2006 will restore.
>> > What
>> > has changed to cause this error now and how can it be fixed?
>> >
>> > Thanking you in anticipation.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

.bak file is incorrectly formed error

I create a backup file in Enterprise Manager on SQL Server 2000. When I try
to restore this file using SQL Server 2005 Express on another machine, I get
the error "The media family on [my .bak file] is incorrectly formed. SQL
Server cannot process this media family. RESTORE HEADER is terminating
abnormally. (Error 3241)."
This same process has worked successfully several times, the last time in
September 2007. A backup file created in December 2006 will restore. What
has changed to cause this error now and how can it be fixed?
Thanking you in anticipation.
Thanks for this, but it didn't fix the problem.
Process is:
1 Use DTS to make a copy of operational database into laptop1 (this has SQL
server 2000). (Use of BACKUP DATABASE is prohibited on the operational
server, which is in a shared environment).
2 Make a backup with TSQL in laptop1 (I can restore from this backup in
laptop1 successfully).
3 Restore with this backup on laptop2 (running SQL Server 2005) using TSQL
gives the same error.
TSQL used is simply BACKUP/RESTORE DATABASE dbname TO/FROM DISK =
'C:\...\file.bak
This process has worked successfully in the past. Last accomplished in
September 2007.
Thanking you in anticipation.
Roger
"Tibor Karaszi" wrote:

> I'd re-create the backup file, this should work. And, to be certain that the right steps are taken,
> I'd do both the backup and the restore using TSQL commands instead of the GUI (BACKUP DTABASE and
> RESTORE DATABASE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
> news:481F37E4-F944-4F1C-A738-0075F7E2BD05@.microsoft.com...
>
|||"Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
news:F9BB51AA-2EE6-4C2E-98FB-62A3423BB0A4@.microsoft.com...
> Thanks for this, but it didn't fix the problem.
> Process is:
> 1 Use DTS to make a copy of operational database into laptop1 (this has
> SQL
> server 2000). (Use of BACKUP DATABASE is prohibited on the operational
> server, which is in a shared environment).
I'm curious why BACKUP DATABASE is prohibited.
In my experience DTS never quite got the "copy db" process down well.

> 2 Make a backup with TSQL in laptop1 (I can restore from this backup in
> laptop1 successfully).
> 3 Restore with this backup on laptop2 (running SQL Server 2005) using
> TSQL
> gives the same error.
Hmm, obvious questions, like "32 bit vs 64 bit?" or different language?
And compression on the 2nd laptop?
[vbcol=seagreen]
> TSQL used is simply BACKUP/RESTORE DATABASE dbname TO/FROM DISK =
> 'C:\...\file.bak
> This process has worked successfully in the past. Last accomplished in
> September 2007.
> Thanking you in anticipation.
> Roger
> "Tibor Karaszi" wrote:
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

.BAK file is growing up

Hello.
SQL Server 2000 (SP3)
I monitor my db's size and it has not changed about a month , however .BAK
file is grown up for about 5gb. Is it possible?
Db size is about 30gb and we have autogrow feature turned on (10 percent)
Thanks
Although your db size is 30 gigs. It could be that there was ample free
space in it and it is filling up.
|||Although your db size is 30 gigs, it could be that there was ample free
space in it and it is filling up.
|||Hi,
Since if the file backup file growth is very high; then i feel that you are
appending to the backup file.
Execute the below command to check how many backup sets you have in your
backup file.
RESTORE Headeronly FROM disk='c:\master.bak'
A client can use RESTORE HEADERONLY to retrieve all the backup header
information for all backups on a particular backup device. The header
information is sent as a row by the server for each backup on a given backup
device in a table
Thanks
Hari
SQL Server MVP
"Alex" <test@.test.com> wrote in message
news:eNrI6J62GHA.3944@.TK2MSFTNGP04.phx.gbl...
> Hello.
> SQL Server 2000 (SP3)
> I monitor my db's size and it has not changed about a month , however
> .BAK file is grown up for about 5gb. Is it possible?
> Db size is about 30gb and we have autogrow feature turned on (10 percent)
> Thanks
>
|||Hari

> Since if the file backup file growth is very high; then i feel that you
> are appending to the backup file.
No, it suddenly happened (growing .BAK ) last night and we have been
using WITH INIT option for all time.
So I have only one file in .BAK sets
Any ideas please
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23qhOwp62GHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Since if the file backup file growth is very high; then i feel that you
> are appending to the backup file.
> Execute the below command to check how many backup sets you have in your
> backup file.
> RESTORE Headeronly FROM disk='c:\master.bak'
>
> A client can use RESTORE HEADERONLY to retrieve all the backup header
> information for all backups on a particular backup device. The header
> information is sent as a row by the server for each backup on a given
> backup device in a table
> Thanks
> Hari
> SQL Server MVP
> "Alex" <test@.test.com> wrote in message
> news:eNrI6J62GHA.3944@.TK2MSFTNGP04.phx.gbl...
>
|||Hello,
Can you execute a SP_Spaceused on that database and see how much is utilized
for data and index.
THanks
Hari
SQL Server MVP
"Alex" <test@.test.com> wrote in message
news:%23FWeB372GHA.3476@.TK2MSFTNGP04.phx.gbl...
> Hari
>
> No, it suddenly happened (growing .BAK ) last night and we have been
> using WITH INIT option for all time.
> So I have only one file in .BAK sets
> Any ideas please
>
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23qhOwp62GHA.4764@.TK2MSFTNGP05.phx.gbl...
>
|||Hi,Hari
Here iis an output
database_name
database_size unallocated space
------
-- --
dbname
28511.69 MB 519.01 MB
reserved data index_size
unused
-- -- -- --
23568632 KB 6408816 KB 6066240 KB 11093576 KB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%238MZOy92GHA.4164@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Can you execute a SP_Spaceused on that database and see how much is
> utilized for data and index.
> THanks
> Hari
> SQL Server MVP
> "Alex" <test@.test.com> wrote in message
> news:%23FWeB372GHA.3476@.TK2MSFTNGP04.phx.gbl...
>

.BAK file is growing up

Hello.
SQL Server 2000 (SP3)
I monitor my db's size and it has not changed about a month , however .BAK
file is grown up for about 5gb. Is it possible?
Db size is about 30gb and we have autogrow feature turned on (10 percent)
ThanksAlthough your db size is 30 gigs. It could be that there was ample free
space in it and it is filling up.|||Although your db size is 30 gigs, it could be that there was ample free
space in it and it is filling up.|||Hi,
Since if the file backup file growth is very high; then i feel that you are
appending to the backup file.
Execute the below command to check how many backup sets you have in your
backup file.
RESTORE Headeronly FROM disk='c:\master.bak'
A client can use RESTORE HEADERONLY to retrieve all the backup header
information for all backups on a particular backup device. The header
information is sent as a row by the server for each backup on a given backup
device in a table
Thanks
Hari
SQL Server MVP
"Alex" <test@.test.com> wrote in message
news:eNrI6J62GHA.3944@.TK2MSFTNGP04.phx.gbl...
> Hello.
> SQL Server 2000 (SP3)
> I monitor my db's size and it has not changed about a month , however
> .BAK file is grown up for about 5gb. Is it possible?
> Db size is about 30gb and we have autogrow feature turned on (10 percent)
> Thanks
>|||Hari
> Since if the file backup file growth is very high; then i feel that you
> are appending to the backup file.
No, it suddenly happened (growing .BAK ) last night and we have been
using WITH INIT option for all time.
So I have only one file in .BAK sets
Any ideas please
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23qhOwp62GHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Since if the file backup file growth is very high; then i feel that you
> are appending to the backup file.
> Execute the below command to check how many backup sets you have in your
> backup file.
> RESTORE Headeronly FROM disk='c:\master.bak'
>
> A client can use RESTORE HEADERONLY to retrieve all the backup header
> information for all backups on a particular backup device. The header
> information is sent as a row by the server for each backup on a given
> backup device in a table
> Thanks
> Hari
> SQL Server MVP
> "Alex" <test@.test.com> wrote in message
> news:eNrI6J62GHA.3944@.TK2MSFTNGP04.phx.gbl...
>> Hello.
>> SQL Server 2000 (SP3)
>> I monitor my db's size and it has not changed about a month , however
>> .BAK file is grown up for about 5gb. Is it possible?
>> Db size is about 30gb and we have autogrow feature turned on (10
>> percent)
>> Thanks
>|||Hello,
Can you execute a SP_Spaceused on that database and see how much is utilized
for data and index.
THanks
Hari
SQL Server MVP
"Alex" <test@.test.com> wrote in message
news:%23FWeB372GHA.3476@.TK2MSFTNGP04.phx.gbl...
> Hari
>> Since if the file backup file growth is very high; then i feel that you
>> are appending to the backup file.
> No, it suddenly happened (growing .BAK ) last night and we have been
> using WITH INIT option for all time.
> So I have only one file in .BAK sets
> Any ideas please
>
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23qhOwp62GHA.4764@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> Since if the file backup file growth is very high; then i feel that you
>> are appending to the backup file.
>> Execute the below command to check how many backup sets you have in your
>> backup file.
>> RESTORE Headeronly FROM disk='c:\master.bak'
>>
>> A client can use RESTORE HEADERONLY to retrieve all the backup header
>> information for all backups on a particular backup device. The header
>> information is sent as a row by the server for each backup on a given
>> backup device in a table
>> Thanks
>> Hari
>> SQL Server MVP
>> "Alex" <test@.test.com> wrote in message
>> news:eNrI6J62GHA.3944@.TK2MSFTNGP04.phx.gbl...
>> Hello.
>> SQL Server 2000 (SP3)
>> I monitor my db's size and it has not changed about a month , however
>> .BAK file is grown up for about 5gb. Is it possible?
>> Db size is about 30gb and we have autogrow feature turned on (10
>> percent)
>> Thanks
>>
>|||Hi,Hari
Here iis an output
database_name
database_size unallocated space
------
-- --
dbname
28511.69 MB 519.01 MB
reserved data index_size
unused
-- -- -- --
23568632 KB 6408816 KB 6066240 KB 11093576 KB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%238MZOy92GHA.4164@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Can you execute a SP_Spaceused on that database and see how much is
> utilized for data and index.
> THanks
> Hari
> SQL Server MVP
> "Alex" <test@.test.com> wrote in message
> news:%23FWeB372GHA.3476@.TK2MSFTNGP04.phx.gbl...
>> Hari
>> Since if the file backup file growth is very high; then i feel that you
>> are appending to the backup file.
>> No, it suddenly happened (growing .BAK ) last night and we have been
>> using WITH INIT option for all time.
>> So I have only one file in .BAK sets
>> Any ideas please
>>
>>
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23qhOwp62GHA.4764@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> Since if the file backup file growth is very high; then i feel that you
>> are appending to the backup file.
>> Execute the below command to check how many backup sets you have in your
>> backup file.
>> RESTORE Headeronly FROM disk='c:\master.bak'
>>
>> A client can use RESTORE HEADERONLY to retrieve all the backup header
>> information for all backups on a particular backup device. The header
>> information is sent as a row by the server for each backup on a given
>> backup device in a table
>> Thanks
>> Hari
>> SQL Server MVP
>> "Alex" <test@.test.com> wrote in message
>> news:eNrI6J62GHA.3944@.TK2MSFTNGP04.phx.gbl...
>> Hello.
>> SQL Server 2000 (SP3)
>> I monitor my db's size and it has not changed about a month , however
>> .BAK file is grown up for about 5gb. Is it possible?
>> Db size is about 30gb and we have autogrow feature turned on (10
>> percent)
>> Thanks
>>
>>
>

.BAK file is growing up

Hello.
SQL Server 2000 (SP3)
I monitor my db's size and it has not changed about a month , however .BAK
file is grown up for about 5gb. Is it possible?
Db size is about 30gb and we have autogrow feature turned on (10 percent)
ThanksAlthough your db size is 30 gigs. It could be that there was ample free
space in it and it is filling up.|||Although your db size is 30 gigs, it could be that there was ample free
space in it and it is filling up.|||Hi,
Since if the file backup file growth is very high; then i feel that you are
appending to the backup file.
Execute the below command to check how many backup sets you have in your
backup file.
RESTORE Headeronly FROM disk='c:\master.bak'
A client can use RESTORE HEADERONLY to retrieve all the backup header
information for all backups on a particular backup device. The header
information is sent as a row by the server for each backup on a given backup
device in a table
Thanks
Hari
SQL Server MVP
"Alex" <test@.test.com> wrote in message
news:eNrI6J62GHA.3944@.TK2MSFTNGP04.phx.gbl...
> Hello.
> SQL Server 2000 (SP3)
> I monitor my db's size and it has not changed about a month , however
> .BAK file is grown up for about 5gb. Is it possible?
> Db size is about 30gb and we have autogrow feature turned on (10 percent)
> Thanks
>|||Hari

> Since if the file backup file growth is very high; then i feel that you
> are appending to the backup file.
No, it suddenly happened (growing .BAK ) last night and we have been
using WITH INIT option for all time.
So I have only one file in .BAK sets
Any ideas please
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23qhOwp62GHA.4764@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Since if the file backup file growth is very high; then i feel that you
> are appending to the backup file.
> Execute the below command to check how many backup sets you have in your
> backup file.
> RESTORE Headeronly FROM disk='c:\master.bak'
>
> A client can use RESTORE HEADERONLY to retrieve all the backup header
> information for all backups on a particular backup device. The header
> information is sent as a row by the server for each backup on a given
> backup device in a table
> Thanks
> Hari
> SQL Server MVP
> "Alex" <test@.test.com> wrote in message
> news:eNrI6J62GHA.3944@.TK2MSFTNGP04.phx.gbl...
>|||Hello,
Can you execute a SP_Spaceused on that database and see how much is utilized
for data and index.
THanks
Hari
SQL Server MVP
"Alex" <test@.test.com> wrote in message
news:%23FWeB372GHA.3476@.TK2MSFTNGP04.phx.gbl...
> Hari
>
> No, it suddenly happened (growing .BAK ) last night and we have been
> using WITH INIT option for all time.
> So I have only one file in .BAK sets
> Any ideas please
>
>
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23qhOwp62GHA.4764@.TK2MSFTNGP05.phx.gbl...
>|||Hi,Hari
Here iis an output
database_name
database_size unallocated space
----
----
-- --
dbname
28511.69 MB 519.01 MB
reserved data index_size
unused
-- -- -- --
23568632 KB 6408816 KB 6066240 KB 11093576 KB
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%238MZOy92GHA.4164@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Can you execute a SP_Spaceused on that database and see how much is
> utilized for data and index.
> THanks
> Hari
> SQL Server MVP
> "Alex" <test@.test.com> wrote in message
> news:%23FWeB372GHA.3476@.TK2MSFTNGP04.phx.gbl...
>

.bak file format?

Hi.

I was wondering if anybody could piont me to a reference for the .bak
file format.

I need to upload a huge amount of data from a unix machine into an MS
SQLServer database and have tried lots of different things with mixed
results.

I occurs to me that if I can generate a .bak file on the unix side,
move it over to the MS side and 'restore' the database, that would
probably be the fastest possible method.

Thanks,

-jimAssuming that you are referring to the MS-specific binary .bak DB backup
file format, I would be very surprised if MS releases those types of
details. It could/would prevent them from changing the format..

Even aside from this, generating a .bak file on the Unix side is
unlikely. There are potential big-endian, little-endian issues. You do
know that SQL Server runs only on Windows, yes?

Is there another DB on the unix side? Could you hook up to it as a
linked server w/ an OLE DB driver or something?

I would export in ascii delimited CSV format or something, and then
import into SQL server w/ the integration services import/export wizard.

hth

Allen Jantzen

jims wrote:

Quote:

Originally Posted by

Hi.
>
I was wondering if anybody could piont me to a reference for the .bak
file format.
>
I need to upload a huge amount of data from a unix machine into an MS
SQLServer database and have tried lots of different things with mixed
results.
>
I occurs to me that if I can generate a .bak file on the unix side,
move it over to the MS side and 'restore' the database, that would
probably be the fastest possible method.
>
Thanks,
>
-jim
>

|||jims (jim@.asrc.cestm.albany.edu) writes:

Quote:

Originally Posted by

I was wondering if anybody could piont me to a reference for the .bak
file format.
>
I need to upload a huge amount of data from a unix machine into an MS
SQLServer database and have tried lots of different things with mixed
results.
>
I occurs to me that if I can generate a .bak file on the unix side,
move it over to the MS side and 'restore' the database, that would
probably be the fastest possible method.

It would take you ages until you have produced that .bak file. Your data
will be stale by then.

The fastest way to load lots of data into SQL Server is bulk copy. You have
a lot more options if you first put the file in a place where you can access
it from Windows. The simplest is probably to use the command-line tool
BCP. You need to format the data in a way so that BCP can read it. (But
BCP is flexible.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

.BAK file double in size

Very strange. My .bak file for a SQL2000 databae has doubled in size. The
database .mdf file has not increased in size. My .bak file is .25g and my
.mdb file is 15g.
The only change I can determine is that a week ago we started a replication
publication on this database. But it is really strange the the .bak file has
grown an not the .mdf file.
Do make sure the file was not corrupt, I did restore it and it restored
okay. I actually restored to a 2005 server (because that is where I had some
room). I restored fine as I said. So then I backed up the database there and
the .bak file is back down to normal size around 11g.
Any ideas. Since we save 4 copies of the .bak on the server it is taking up
a lot of space, but I mainly concerned with the strangeness of it all.
Thanks for your help
DebDo not worry about it. The backup file may also have information from the
transaction log. So, if you were able to restore it then it is fine.
Hope this helps,
Ben Nevarez
"DebHerman" wrote:
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a replication
> publication on this database. But it is really strange the the .bak file has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had some
> room). I restored fine as I said. So then I backed up the database there and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb|||Did you back up to the same file without using INIT? This will append a
second backup to the original backup file. As Erland posted in another
thread today, you can check how many backups are in the BAK file by RESTORE
... HEADERONLY and simply counting the rows.
"DebHerman" <DebHerman@.discussions.microsoft.com> wrote in message
news:C84C48BD-E6DE-4736-B12B-0F5E2DCC12B3@.microsoft.com...
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a
> replication
> publication on this database. But it is really strange the the .bak file
> has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had
> some
> room). I restored fine as I said. So then I backed up the database there
> and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking
> up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb|||Can you tell me the scenarion where it would have that much information from
the transaction log?
"Ben Nevarez" wrote:
> Do not worry about it. The backup file may also have information from the
> transaction log. So, if you were able to restore it then it is fine.
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:
> > Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> > database .mdf file has not increased in size. My .bak file is .25g and my
> > .mdb file is 15g.
> >
> > The only change I can determine is that a week ago we started a replication
> > publication on this database. But it is really strange the the .bak file has
> > grown an not the .mdf file.
> > Do make sure the file was not corrupt, I did restore it and it restored
> > okay. I actually restored to a 2005 server (because that is where I had some
> > room). I restored fine as I said. So then I backed up the database there and
> > the .bak file is back down to normal size around 11g.
> > Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> > a lot of space, but I mainly concerned with the strangeness of it all.
> > Thanks for your help
> > Deb|||"DebHerman" <DebHerman@.discussions.microsoft.com> wrote in message
news:C84C48BD-E6DE-4736-B12B-0F5E2DCC12B3@.microsoft.com...
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a
> replication
> publication on this database. But it is really strange the the .bak file
> has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had
> some
> room). I restored fine as I said. So then I backed up the database there
> and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking
> up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb
Your backup should be fine. Maybe you have some long running transactions or
heavy concurrent activity during the backup and that makes your log bigger
than usual. Check your log file size.
--
Rubén Garrigós
Solid Quality Mentors|||From 'Full Database Backups' on BOL.
"A full database backup backs up the whole database. This includes part of
the transaction log so that the full database backup can be recovered. Full
database backups represent the database at the time the backup finished."
"Enough of the transaction log is included in the backup to let you recover
the database to the time when the backup finished."
Hope this helps,
Ben Nevarez
"DebHerman" wrote:
> Can you tell me the scenarion where it would have that much information from
> the transaction log?
> "Ben Nevarez" wrote:
> >
> > Do not worry about it. The backup file may also have information from the
> > transaction log. So, if you were able to restore it then it is fine.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "DebHerman" wrote:
> >
> > > Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> > > database .mdf file has not increased in size. My .bak file is .25g and my
> > > .mdb file is 15g.
> > >
> > > The only change I can determine is that a week ago we started a replication
> > > publication on this database. But it is really strange the the .bak file has
> > > grown an not the .mdf file.
> > > Do make sure the file was not corrupt, I did restore it and it restored
> > > okay. I actually restored to a 2005 server (because that is where I had some
> > > room). I restored fine as I said. So then I backed up the database there and
> > > the .bak file is back down to normal size around 11g.
> > > Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> > > a lot of space, but I mainly concerned with the strangeness of it all.
> > > Thanks for your help
> > > Deb|||I understand that transactions that happen during the back up are stored in
the back up file, but nothing "should" be happening at the time of the
backup. The transaction log file is actually backed up just prior to the full
database back up and it is really small.
But my suspicion is that a snapshot is running at the same time as the back
up since we just started replication. Would a snapshot create transactions.
If so this is probably my problem. I adjusted the timing last night but not
quite enough. The snapshot didn't quite finish before the backup up of the
transaction log. But it did finish before the backup of the database.
Confusing. I'm not sure a full snapshot should be running each night but
that is a whole other discussion.
I need to determine what is causing this. We store 4 days worth .bak on the
server and these are backed up to tape each night, so 4 12g is 48g a space
that makes the network backup people cranky.<g>
Thanks for any help?
"Ben Nevarez" wrote:
> From 'Full Database Backups' on BOL.
> "A full database backup backs up the whole database. This includes part of
> the transaction log so that the full database backup can be recovered. Full
> database backups represent the database at the time the backup finished."
> "Enough of the transaction log is included in the backup to let you recover
> the database to the time when the backup finished."
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:
> > Can you tell me the scenarion where it would have that much information from
> > the transaction log?
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Do not worry about it. The backup file may also have information from the
> > > transaction log. So, if you were able to restore it then it is fine.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > >
> > >
> > >
> > >
> > > "DebHerman" wrote:
> > >
> > > > Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> > > > database .mdf file has not increased in size. My .bak file is .25g and my
> > > > .mdb file is 15g.
> > > >
> > > > The only change I can determine is that a week ago we started a replication
> > > > publication on this database. But it is really strange the the .bak file has
> > > > grown an not the .mdf file.
> > > > Do make sure the file was not corrupt, I did restore it and it restored
> > > > okay. I actually restored to a 2005 server (because that is where I had some
> > > > room). I restored fine as I said. So then I backed up the database there and
> > > > the .bak file is back down to normal size around 11g.
> > > > Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> > > > a lot of space, but I mainly concerned with the strangeness of it all.
> > > > Thanks for your help
> > > > Deb

.BAK file double in size

Very strange. My .bak file for a SQL2000 databae has doubled in size. The
database .mdf file has not increased in size. My .bak file is .25g and my
..mdb file is 15g.
The only change I can determine is that a week ago we started a replication
publication on this database. But it is really strange the the .bak file has
grown an not the .mdf file.
Do make sure the file was not corrupt, I did restore it and it restored
okay. I actually restored to a 2005 server (because that is where I had some
room). I restored fine as I said. So then I backed up the database there and
the .bak file is back down to normal size around 11g.
Any ideas. Since we save 4 copies of the .bak on the server it is taking up
a lot of space, but I mainly concerned with the strangeness of it all.
Thanks for your help
Deb
Do not worry about it. The backup file may also have information from the
transaction log. So, if you were able to restore it then it is fine.
Hope this helps,
Ben Nevarez
"DebHerman" wrote:

> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a replication
> publication on this database. But it is really strange the the .bak file has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had some
> room). I restored fine as I said. So then I backed up the database there and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb
|||Did you back up to the same file without using INIT? This will append a
second backup to the original backup file. As Erland posted in another
thread today, you can check how many backups are in the BAK file by RESTORE
... HEADERONLY and simply counting the rows.
"DebHerman" <DebHerman@.discussions.microsoft.com> wrote in message
news:C84C48BD-E6DE-4736-B12B-0F5E2DCC12B3@.microsoft.com...
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a
> replication
> publication on this database. But it is really strange the the .bak file
> has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had
> some
> room). I restored fine as I said. So then I backed up the database there
> and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking
> up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb
|||Can you tell me the scenarion where it would have that much information from
the transaction log?
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Do not worry about it. The backup file may also have information from the
> transaction log. So, if you were able to restore it then it is fine.
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:
|||From 'Full Database Backups' on BOL.
"A full database backup backs up the whole database. This includes part of
the transaction log so that the full database backup can be recovered. Full
database backups represent the database at the time the backup finished."
"Enough of the transaction log is included in the backup to let you recover
the database to the time when the backup finished."
Hope this helps,
Ben Nevarez
"DebHerman" wrote:
[vbcol=seagreen]
> Can you tell me the scenarion where it would have that much information from
> the transaction log?
> "Ben Nevarez" wrote:
|||I understand that transactions that happen during the back up are stored in
the back up file, but nothing "should" be happening at the time of the
backup. The transaction log file is actually backed up just prior to the full
database back up and it is really small.
But my suspicion is that a snapshot is running at the same time as the back
up since we just started replication. Would a snapshot create transactions.
If so this is probably my problem. I adjusted the timing last night but not
quite enough. The snapshot didn't quite finish before the backup up of the
transaction log. But it did finish before the backup of the database.
Confusing. I'm not sure a full snapshot should be running each night but
that is a whole other discussion.
I need to determine what is causing this. We store 4 days worth .bak on the
server and these are backed up to tape each night, so 4 12g is 48g a space
that makes the network backup people cranky.<g>
Thanks for any help?
"Ben Nevarez" wrote:
[vbcol=seagreen]
> From 'Full Database Backups' on BOL.
> "A full database backup backs up the whole database. This includes part of
> the transaction log so that the full database backup can be recovered. Full
> database backups represent the database at the time the backup finished."
> "Enough of the transaction log is included in the backup to let you recover
> the database to the time when the backup finished."
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:

.BAK extension ?

Hi !
Usualy I do not use SQL server, but this time I have got some data to
import. Data is on the CD, and it is named with extension .BAK. The
organization I've got data from does not have true DBA at this moment,
and I am not sure what I actualy got. The previous DBA resigned. I have
requested dump of database structure only, but I could manage with full
database dump, too. I am afraid that the file I've got is not either of
those two.
Currently I do not have SQL server installed.
Could .BAK files be interchanged between different machines ?
Could .BAK be an incremental backup ?
Are they compatibile for different versions of SQL server ?
Person that gave me the CD was not able to tell what version of SQL
server are they running.
DG.BAK is the standard extension for a full backup. You'll have to have a
recent version of SQL Server to try and restore it. If it restores OK, you
can extract the structure in the form of a script.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Drazen Gemic" <anyone@.anywhere.tk> wrote in message
news:dv7haf$me7$1@.magcargo.vodatel.hr...
Hi !
Usualy I do not use SQL server, but this time I have got some data to
import. Data is on the CD, and it is named with extension .BAK. The
organization I've got data from does not have true DBA at this moment,
and I am not sure what I actualy got. The previous DBA resigned. I have
requested dump of database structure only, but I could manage with full
database dump, too. I am afraid that the file I've got is not either of
those two.
Currently I do not have SQL server installed.
Could .BAK files be interchanged between different machines ?
Could .BAK be an incremental backup ?
Are they compatibile for different versions of SQL server ?
Person that gave me the CD was not able to tell what version of SQL
server are they running.
DG

.BAK extension ?

Hi !
Usualy I do not use SQL server, but this time I have got some data to
import. Data is on the CD, and it is named with extension .BAK. The
organization I've got data from does not have true DBA at this moment,
and I am not sure what I actualy got. The previous DBA resigned. I have
requested dump of database structure only, but I could manage with full
database dump, too. I am afraid that the file I've got is not either of
those two.
Currently I do not have SQL server installed.
Could .BAK files be interchanged between different machines ?
Could .BAK be an incremental backup ?
Are they compatibile for different versions of SQL server ?
Person that gave me the CD was not able to tell what version of SQL
server are they running.
DG.BAK is the standard extension for a full backup. You'll have to have a
recent version of SQL Server to try and restore it. If it restores OK, you
can extract the structure in the form of a script.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Drazen Gemic" <anyone@.anywhere.tk> wrote in message
news:dv7haf$me7$1@.magcargo.vodatel.hr...
Hi !
Usualy I do not use SQL server, but this time I have got some data to
import. Data is on the CD, and it is named with extension .BAK. The
organization I've got data from does not have true DBA at this moment,
and I am not sure what I actualy got. The previous DBA resigned. I have
requested dump of database structure only, but I could manage with full
database dump, too. I am afraid that the file I've got is not either of
those two.
Currently I do not have SQL server installed.
Could .BAK files be interchanged between different machines ?
Could .BAK be an incremental backup ?
Are they compatibile for different versions of SQL server ?
Person that gave me the CD was not able to tell what version of SQL
server are they running.
DG

.BAK extension ?

Hi !
Usualy I do not use SQL server, but this time I have got some data to
import. Data is on the CD, and it is named with extension .BAK. The
organization I've got data from does not have true DBA at this moment,
and I am not sure what I actualy got. The previous DBA resigned. I have
requested dump of database structure only, but I could manage with full
database dump, too. I am afraid that the file I've got is not either of
those two.
Currently I do not have SQL server installed.
Could .BAK files be interchanged between different machines ?
Could .BAK be an incremental backup ?
Are they compatibile for different versions of SQL server ?
Person that gave me the CD was not able to tell what version of SQL
server are they running.
DG
..BAK is the standard extension for a full backup. You'll have to have a
recent version of SQL Server to try and restore it. If it restores OK, you
can extract the structure in the form of a script.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Drazen Gemic" <anyone@.anywhere.tk> wrote in message
news:dv7haf$me7$1@.magcargo.vodatel.hr...
Hi !
Usualy I do not use SQL server, but this time I have got some data to
import. Data is on the CD, and it is named with extension .BAK. The
organization I've got data from does not have true DBA at this moment,
and I am not sure what I actualy got. The previous DBA resigned. I have
requested dump of database structure only, but I could manage with full
database dump, too. I am afraid that the file I've got is not either of
those two.
Currently I do not have SQL server installed.
Could .BAK files be interchanged between different machines ?
Could .BAK be an incremental backup ?
Are they compatibile for different versions of SQL server ?
Person that gave me the CD was not able to tell what version of SQL
server are they running.
DG