full database backup - 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

General SQL Server Forums

New to SQL Server Programming

full database backup

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-04-22 : 12:25:43

I have SQL Server 2005 with 25-30 databases, I created weekly maintenance plans using following script. Its works but this week it does not backup all database. I get email for success, when i check the drive it does not backup all database.
anybody know why did not backup all database?

USE Master
GO
DECLARE @Name VARCHAR(50)
DECLARE @Path VARCHAR(256)
DECLARE @FileName VARCHAR(256)
DECLARE @FileDate VARCHAR(20)

SELECT @FileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
SET @Path='D:\DbBak\'

DECLARE DbCursor CURSOR FOR
SELECT [Name] FROM sys.Databases WHERE [Name] NOT IN ('tempdb')

OPEN DbCursor
FETCH NEXT FROM DbCursor INTO @Name

WHILE @@FETCH_STATUS = 0

BEGIN
SET @FileName = @Path + @Name + '_' + @FileDate + '.BAK'
BACKUP DATABASE @Name TO DISK = @FileName WITH NOFORMAT, INIT, NAME = @FileName, SKIP, REWIND, NOUNLOAD, STATS = 5

FETCH NEXT FROM DbCursor INTO @Name
END

CLOSE DbCursor
DEALLOCATE DbCursor

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 12:41:37

You have no error handling in the script.

Did it run out of space?
Did it fail then not back up any more databases or miss some - that will be a bit difficult to tell as it doesn't have any ordering to the backups.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-04-22 : 12:55:37

no i have enough free space.
when i check log file. i see this:

Informational: Full-text Auto population initialized for table or indexed view '[dbname].[dbo].[tablename]' (table or indexed view ID '142623551', database ID '25'). Population sub-tasks: 1.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-04-22 : 15:52:12

Can you glean any useful information from the SQL logs?

Terry

-- Procrastinate now!

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-04-22 : 17:38:57

It says the error no is : Error 3041
I checked all database are online, i have enought free space........

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 18:12:20

Doesn't sound like yours but
http://support.microsoft.com/default.aspx/kb/921106

Which service pack?

Is it possible that something was locking the folder?
I would put some error processing into your sp.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-04-22 : 19:51:06

I have sp 2.
A day before backup schedule, my server was stopped. I started all the services manually.
All databases are running normally. Is there any locked ?

I am using same maintenance plan since long time, i never had this problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources