BACK UP - 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

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-08-26 : 02:02:05

HI

i have maintance plan of BACKUP.
for some days it is faild ,and i do'nt knoe why
i tried to backup it to same server and it still not work

i get this message (i paste the second half of the message)

70% complete End Progress Progress: 2009-08-25 23:35:44.65 Source: FULL Back Up All Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'd:\SqlBackup...".: 75% complete End Progress Progress: 2009-08-25 23:35:44.66 Source: FULL Back Up All Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'd:\SqlBackup...".: 80% complete End Progress Progress: 2009-08-25 23:35:44.66 Source: FULL Back Up All Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'd:\SqlBackup...".: 85% complete End Progress Progress: 2009-08-25 23:35:44.66 Source: FULL Back Up All Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'd:\SqlBackup...".: 90% complete End Progress Progress: 2009-08-25 23:35:44.66 Source: FULL Back Up All Database Task Executing query "EXECUTE master.dbo.xp_create_subdir N'd:\SqlBackup...".: 95% complete End Progress Progress: 2009-08-25 23:35:44.66 Source: FULL Back Up All Database Task Executing query "EXECUTE master.dbo.xp_create_su... The package execution fa... The step failed.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-08-26 : 03:37:57

You need to get the actual error message. Go into the job step, set it to log output to a text file and check that text file after the job fails again.

--
Gail Shaw
SQL Server MVP

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-08-26 : 03:41:46

Is there sufficient disk space on the d: drive?
Has any permissions changed? Either on d:\SqlBackup? Or for the service account?
Does the SQL Agent account still have permission to create a sub-directory in d:\Sqlbackup?

Can you run this successfully, as the service account?
EXECUTE master.dbo.xp_create_subdir N'd:\SqlBackup...

When you say you tried to backup to the same server, how did you do that? With the BACKUP DATABASE command? Or by running a package?

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-08-27 : 03:12:16

i run it in managment studio and i get this message:

Msg 3202, Level 16, State 1, Line 1
Write on "d:\MyDB.bak" failed: 1130(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 3201, Level 16, State 2, Line 4
Cannot open backup device 'd:\MyDB.bak.bak'. Operating system error 2(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 4
VERIFY DATABASE is terminating abnormally.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-08-27 : 03:20:01

What's the exact command that you're running?
Does the SQL service account have write permissions to the root of D?

What is D drive? SAN, local storage? If you check the windows event log, are there any disk-related errors from around the time that you ran the backup?

--
Gail Shaw
SQL Server MVP

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-08-27 : 05:04:50

declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MyDB' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDB' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDB'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'd:\MyDB.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
BACKUP DATABASE [BI_MNG] TO DISK = N'd:\MyDB.bak' WITH NOFORMAT, NOINIT, NAME = N'MyDB', SKIP, REWIND, NOUNLOAD, STATS = 10
GO

yes i have permission and the account too.
i must to say that my backup of log is working,and backup of DB system too.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-08-27 : 11:16:09

You're doing a restore verify before doing the backup. Does the backup file exist? The fact that the backupset is found doesn't mean the file exists.

--
Gail Shaw
SQL Server MVP

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-08-28 : 07:05:52

i solved it,

i have space in the name of the DB

instead [MyDB] i have [MyDB ]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources