Back up Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Back up Issue

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-07-15 : 00:49:10

Dear All,

I am taking a full back up from production environment it close to &&GB on SQL server 2005 Enterprise edition through management studio.

After that i am trying to verify the back up.

RESTORE VERIFYONLY FROM DISK = 'E:\EE\database\backup\EE_Fullbackup_15072011.bak'

It is throwing the following error pls advice how to fix this issue.

Msg 3189, Level 16, State 1, Line 1
Damage to the backup set was detected.
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-15 : 00:55:30

It seems your backup is corrupt.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-07-15 : 01:00:50

Hi Tara,

Thanks. And how do i get good back up.

I have a sysadmin access to database and i have taken from SSMS.

Pls suggest how to take the correct backup.

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-15 : 01:03:29

Run another backup. If that one shows up as corrupt, then you're going to need to investigate your disks. Something is behaving.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-07-15 : 02:39:54

Hi Tara,

I tried to take a differential backup and below is the error.

The transaction log for database "XYZ" is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases [SQLSTATE 42000] (Error 9002). The step failed.

Could you please suggest something.

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-15 : 02:45:11

Well you need to perform a transaction log backup.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-07-15 : 03:09:04

Even after doing the tran log back up i have done the Full backup again and its now 76GB.

When i run the verify back up query i am gettign below error in spite of that we have 230 GB of free space in the Disk drive,

Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
Msg 701, Level 17, State 17, Line 1
There is insufficient system memory to run this query.

Thanks,
Gangadhara MS
SQL Developer and DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-15 : 12:21:21

It's referring to memory, not storage. Check the error log for more details. I can't imagine that this would be it, but you might need to set the lock pages in memory option for the SQL Server service account.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-07-16 : 03:04:57

Hi Tara,

We have system memory of 32GB with AWE enables as this is 32 bit system and i have restarted the SQL server instance.

Even after doing that i am not able to verify the backup.

Please help if any alternate method to do it.

Thanks,
Gangadhara MS
SQL Developer and DBA

skybvi
Posting Yak Master

193 Posts

Posted - 2011-07-22 : 15:45:16

hey, gangadhar
Can you check 1 thing,
Go the server where you getting memory issues.
Open hte task manager.
then performance tab
then see and tell wats hte value of kernel memory..

Regards,
Sushant
DBA
West Indies

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources