May 21, 2013 at 3:07 pm
Hi,
I have problem My backup script runs on every sunday for full backup and should have to take 41 database backup but it only takes 22 or 7 databases backup and and it say that it ran successfully. Can please help me
May 22, 2013 at 1:39 am
can share script? so we can check more details and also check error log.There should all details.
May 22, 2013 at 4:29 am
The login that is running the backup script has enough permission for missing databases?
Any of the database are in restoring mode?
Please post your script to analyse.
May 22, 2013 at 7:22 am
Are you using sp_MSforeachdb by any chance? Please post the script you're using.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2013 at 10:22 am
DECLARE @dbname VARCHAR(max),
@fullfilename VARCHAR(max),
@getfullfileq VARCHAR(max),
@fullfilebackupq VARCHAR(max),
@deldate datetime,
@getdate-2 VARCHAR(30),
@gettime VARCHAR(8),
@hh VARCHAR (2),
@mm VARCHAR(2),
@ss VARCHAR(2)
/*This command will get all Producation databases with any recovery model*/
DECLARE getdbname CURSOR FOR
SELECT nameFROM sys.databases
Where name in ('test1','test2','test3','test4','test5','test6',test7')
OPEN getdbname
FETCH NEXT FROM getdbname
INTO @dbname
WHILE @@FETCH_STATUS =0
BEGIN
/* Below section will convert current date and time into varchar so we can put as file name*/
SET @getdate-2= CONVERT(VARCHAR(30), GETDATE(),110)
SET @gettime=CONVERT(VARCHAR(8), GETDATE(),108)
SET @hh = SUBSTRING(@gettime,1,2)
SET @mm = SUBSTRING(@gettime,4,5)
SET @ss = SUBSTRING(@gettime,7,8)
/*Below command will store backup full file query
Make path modification so we can use from any database*/
SET @fullfilebackupq ='BACKUP DATABASE ['+@dbname+']
TO DISK=''E:\Full\'+@dbname+'_full-BACKUP_'+@getdate+'-'+@hh+'-'+@mm+'-'+@ss+'.bak''
WITH
RETAINDAYS = 30,
NOFORMAT,
NOINIT,
NAME ='''+@dbname+'fullbackup'+@getdate+'_'+@gettime+''''
/*Here query executed*/
--print @fullfilebackupq
EXEC( @fullfilebackupq)
FETCH NEXT FROM getdbname
INTO @dbname
END
CLOSE getdbname
DEALLOCATE getdbname
May 22, 2013 at 1:01 pm
I have sysadmin write and I all database is online.
These are prductiong databases
May 22, 2013 at 2:14 pm
This:
/*This command will get all Producation databases with any recovery model*/
DECLARE getdbname CURSOR FOR
SELECT nameFROM sys.databases
Where name in ('test1','test2','test3','test4','test5','test6',test7')
will get you 7 databases: test1','test2','test3','test4','test5','test6',test7'.
If this is a "Sanitized" script it does us no good.
May 22, 2013 at 2:35 pm
I means there is name upto 33 databases test1 to test33
May 22, 2013 at 2:47 pm
Are you capturing the output of the job step to a log file or table, the options in job step advanced tab?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2013 at 2:57 pm
Log file and log file result is below
Executed as user: NT AUTHORITY\NETWORK SERVICE. ...
'test1' on file 1.
[SQLSTATE 01000] (Message 4035) Processed 2 pages for database 'test1', file 'test_log' on file 1.
[SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfully processed 136818 pages in 19.594 seconds (54.551 MB/sec).
[SQLSTATE 01000] (Message 3014) Processed 3150240 pages for database 'test2', file 'test2' on file 1.
[SQLSTATE 01000] (Message 4035) ... The step succeeded.
May 22, 2013 at 2:59 pm
Mind sharing what was logged for the run where you missed backing up some databases?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2013 at 3:06 pm
jignesh.swami82 (5/22/2013)
I means there is name upto 33 databases test1 to test33
Okay, if you list 33 database names how do you expect the procedure to backup 41 databases as you indicate in your original post?
Original post:
jignesh.swami82 (5/21/2013)
Hi,I have problem My backup script runs on every sunday for full backup and should have to take 41 database backup but it only takes 22 or 7 databases backup and and it say that it ran successfully. Can please help me
May 23, 2013 at 11:23 am
can check database log is full? Try for once database which not backup,Try take manual backup and check is there any problem ?Space available on disk ?
May 23, 2013 at 11:41 am
May be there are trailing spaces in the end of database name. Worth checking !! "DBA" is not same as "DBA " ..
May 24, 2013 at 7:52 am
Thank you all reply
I did two Thing
1) I ran my script manaully it backup and same problem so I took backup inducel database and it work fine by using same script
2)check space I have 1.7 TB free space
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply