Sql Backing up Tables: original

Showing posts with label original. Show all posts

Thursday, March 29, 2012

backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larger than the original working database (approx 4GB). This doesn't seems logical to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
Bernard Goh
I would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||I would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh

backup file much larger than the actual working database file

Thursday, March 8, 2012

Backup checking

Hello, everyone:
After backup is finished, how to check if the backup is successful, and it is same as the original database? Thanks.
ZYTOriginally posted by yitongzhang
Hello, everyone:

After backup is finished, how to check if the backup is successful, and it is same as the original database? Thanks.

ZYT
check
RESTORE VERIFYONLY
in BOL

Thursday, February 16, 2012

backup

Hai,

I have taken back of sql2005 beta 2 version. But now i am unable to restore it sql2005 original version.

it is displaying the error incompatable version.

Please let me know how i can restore the backup from sql2005 beta 2 version to sql2005 original version.

regards

Suresh S

I think Microsoft made some breaking changes between Beta2 and RTM. I think you will have to script the schema from the Beta2 and create a new blank database in SQL Server 2005 RTM (or later), then use Integration Services to copy your data. Visual Studio 2005 for Database Professionals (Datadude), can automate much of this.

Friday, February 10, 2012

Backing up tables

I use SQL Server 2000 and I create scripts on a daily basis to do the following

1. Rename a table
2. Create table with original name and add new field
3. Copy data from renamed table to new table
4. Build indexes
-------------
execute sp_rename ORIGINAL, OLD
GO

CREATE TABLE [dbo].[ORIGINAL] (
[A] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[B] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[C] [int] NOT NULL ,
[Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO ORIGINAL (A, B, NOTES)
SELECT A, B, NOTES
FROM OLD

CREATE CLUSTERED INDEX [ORIGINAL_A_B] ON [dbo].[ORIGINAL]([A], [B] DESC ) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
-------------

ON A TABLE WITH 100,000 RECORDS THIS KIND OF SCRIPT TAKES A LONG TIME TO DO THE INSERT/SELECT PART

I WOULD USE ALTER/ADD TO UPDATE THE TABLES, BUT MY SUPERIOR INSISTS THAT NOT HAVING TEXT FIELDS AS THE LAST FIELD IN THE TABLE WILL CAUSE QUERKY PROBLEMS WITH HIS SQL STATEMENTS
DOES ANYONE HAVE ANY KNOWLEDGE THAT THIS COULD BE TRUE?

IF SO,
DOES ANYONE HAVE ANY IDEAS FOR DOING THIS SAME PROCESS TO ADD A NEW FIELD (C) BEFORE THE FINAL FIELD (NOTES - TEXT FIELD), AND BACKUP THE TABLE BUT WITHOUT IT TAKING SUCH A LONG TIME ON THE INSERT/SELECT PART?

Forgive me if I'm asking a lot, but I'm lost and these scripts take way too long for what seems like such a simple task.

Thanks,
MitchWow sounds like he has a DB2 OS/390 v2.3 background

Look up the datatype in BOL...

They store pointer values, not the data...it's stored separatley...

If you don't mind being fired, call him a scrub...

The order of columns in a table should have very little impact...

But being anal goes with the job...

Try a bcp out and in with a format file...

The reason it's slow is because it's all being logged...the bcp will log only the pages...not every row....|||EDIT:

Oh, and btw, I use ALTER

Thursday, March 29, 2012

backup file much larger than the actual working database file

backup file much larger than the actual working database file

backup file much larger than the actual working database file

Thursday, March 8, 2012

Backup checking

Thursday, February 16, 2012

backup

Friday, February 10, 2012

Backing up tables

Sql Backing up Tables

Blog Archive

About Me