July 24, 2014 at 8:28 am
Dear all
I'm all new to SQL so I apologize if this question seems trivial to you.
I have pasted the stored procedure used at my company for backup (it was written by a subcontractor a few years ago). It works sometimes...
The problem is I suspect when it gets to big databases (i've found 3 about 170GB, and about 35 others between 20mb to 5 GB). It does the backup for the smaller ones but stops when it gets to big ones, but the issue then for me is, the Job gives back "Succeded" in the Logviewer, but it shouldn't 🙁 I have excluded the big Databases for now because I can't risk not having daily backups on a productive system (I've removed the names of these databases from this example)
Is there a way to set a timer for example that it waits for an aknowledge that the backup succeded?
Many thanks for help and insights
Metin
USE [master]
GO
/****** Object: StoredProcedure [dbo].[daily_rotating_backup_simple] Script Date: 07/24/2014 16:18:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[daily_rotating_backup_simple]
@path VARCHAR(400), -- path to backup location (w/o db name!!!). Eg. 'x:\backups\simple'
@databasenamelist as varchar(500) -- list of databases to exclude in this backup eg: 'tempdb,other_db_to_exclude'
AS
SET NOCOUNT ON
DECLARE @date VARCHAR(100)
DECLARE @dbname VARCHAR(100)
DECLARE @bkup VARCHAR(500)
DECLARE @doscommand VARCHAR(8000)
DECLARE @mypath VARCHAR(400)
DECLARE @doscommandreturnvalues VARCHAR(8000)
DECLARE @recoveryscript VARCHAR(8000)
DECLARE @recoveryscriptname VARCHAR(200)
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
--Fill the list passed to the stored procedure into a temp database
DECLARE @myquery VARCHAR(8000);
DECLARE @spot int
DECLARE @mystring varchar(8000)
--SET @path='\\wntmas\SQLPOOLBACKUP\Simple'
SET @databasenamelist='tempdb,otherdatabases'
create table #daily_tempdb_simple (excludeddbname varchar(100))
while @databasenamelist <> ''
BEGIN
set @spot = CHARINDEX(',', @databasenamelist)
if @spot > 0
begin
set @mystring = left(@databasenamelist, @spot - 1)
set @databasenamelist = right(@databasenamelist, len(@databasenamelist) - @spot)
end
else
begin
set @mystring = @databasenamelist
set @databasenamelist = ''
end
set @myquery = 'insert into #daily_tempdb_simple values('''+convert(varchar(100), @mystring)+''')'
exec(@myquery)
end
--Server Details
PRINT '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$'
PRINT '$ SERVERNAME: '+CONVERT(VARCHAR(50),SERVERPROPERTY('SERVERNAME'))+'DATE: '+ CONVERT(VARCHAR(50),GETDATE()) + ' $'
PRINT '$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$'
PRINT ''
--Adding backslash
IF SUBSTRING(REVERSE(@path),1,1) <> '\'
SET @path=@path + '\'
--prepare recovery script
SET @recoveryscriptname = @path+'myrecovery.sql'
set @recoveryscript='echo --recovery script for all databases in simple instance > '+@recoveryscriptname
PRINT ''
PRINT 'Database Backups Started'
PRINT '^^^^^^^^^^^^^^^^^^^^^^^^'
--Backup Script
SET @date= CONVERT(VARCHAR(10),GETDATE(),112)
DECLARE bkup_cursor CURSOR FOR SELECT NAME FROM master.dbo.sysdatabases WHERE name not in (select excludeddbname from #daily_tempdb_simple) and name not like 'ReportServer%' and DATABASEPROPERTYEX(name,'status') = 'ONLINE'
OPEN bkup_cursor
FETCH NEXT FROM bkup_cursor INTO @dbname
IF @@FETCH_STATUS <> 0
PRINT 'No database to backup...!!'
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @@CURSOR_ROWS
--SET @dbname = replace(CAST(@dbname AS VARCHAR),'-','_')
--delete oldest backup
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF EXIST "' +@path+@dbname+'\daily.6" rmdir /Q /S "' +@path+@dbname+'\daily.6"'',no_output'
PRINT 'delete oldest daily backup'
PRINT '^^^^^^^^^^^^^^^^^^^^^^^^^^'
EXEC (@doscommand)
PRINT @doscommand
--rotate all other backups
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF EXIST "' +@path+@dbname+'\daily.5" move "' +@path+@dbname+'\daily.5" "' +@path+@dbname+'\daily.6"'',no_output'
EXEC (@doscommand)
PRINT @doscommand
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF EXIST "' +@path+@dbname+'\daily.4" move "' +@path+@dbname+'\daily.4" "' +@path+@dbname+'\daily.5"'',no_output'
EXEC (@doscommand)
PRINT @doscommand
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF EXIST "' +@path+@dbname+'\daily.3" move "' +@path+@dbname+'\daily.3" "' +@path+@dbname+'\daily.4"'',no_output'
EXEC (@doscommand)
PRINT @doscommand
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF EXIST "' +@path+@dbname+'\daily.2" move "' +@path+@dbname+'\daily.2" "' +@path+@dbname+'\daily.3"'',no_output'
EXEC (@doscommand)
PRINT @doscommand
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF EXIST "' +@path+@dbname+'\daily.1" move "' +@path+@dbname+'\daily.1" "' +@path+@dbname+'\daily.2"'',no_output'
EXEC (@doscommand)
PRINT @doscommand
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF EXIST "' +@path+@dbname+'\daily.0" move "' +@path+@dbname+'\daily.0" "' +@path+@dbname+'\daily.1"'',no_output'
EXEC (@doscommand)
PRINT @doscommand
--create new actual backup directory
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF NOT EXIST "' +@path+@dbname+'\daily.0" mkdir "' +@path+@dbname+'\daily.0"'',no_output'
EXEC (@doscommand)
PRINT @doscommand
SET @bkup='BACKUP DATABASE ['+@dbname+'] TO DISK = '''+@path+@dbname+'\daily.0\'+@dbname+'.bak'' WITH INIT,COMPRESSION'
PRINT '************Processing '+@dbname+' Backup... **************'
EXEC (@bkup)
PRINT 'Backed up to ' + @path+@dbname+'\daily.0\'+@dbname+'.bak'
PRINT '********************************************************'
PRINT ''
--add a file to every backup set to mark the backupset as ready for recovery
SET @doscommand='EXEC master.dbo.xp_cmdshell ''IF NOT EXIST "'+@path+@dbname+'\daily.0\'+@dbname+'.ready" echo "'+@path+@dbname+'\daily.0\'+@dbname+'.bak" > "'+@path+@dbname+'\daily.0\'+@dbname+'.ready"'',no_output'
EXEC (@doscommand)
--PRINT @doscommand
--add recovery statement for this db into a file (can then be used to recovery all dbs in one time) but not for system databases!
if LOWER(@dbname) <> 'master' and LOWER(@dbname) <> 'model' and LOWER(@dbname) <> 'msdb' and LOWER(@dbname) <> 'tempdb'
set @recoveryscript=@recoveryscript+' && echo RESTORE DATABASE '+@dbname+' >> '+@recoveryscriptname+' && echo FROM DISK = '''''+@path+@dbname+'\daily.0\'+@dbname+'.bak'''' >> '+@recoveryscriptname+' && echo WITH RECOVERY,REPLACE >> '+@recoveryscriptname
print @recoveryscript
FETCH NEXT FROM bkup_cursor INTO @dbname
END
CLOSE bkup_cursor
DEALLOCATE bkup_cursor
SET @doscommand='EXEC master.dbo.xp_cmdshell '''+@recoveryscript+''',no_output'
print @doscommand
--print len(@doscommand)
--EXEC (@doscommand)
PRINT '============Backup Completed Successfully============'
July 24, 2014 at 8:35 am
Drop the procedure from your server and go download the SQL Server Backup from Ola Hallengren .
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
July 25, 2014 at 1:01 am
Hi, thanks for the advice, I know that backupscript already but it looks like to me that you haven't checked out what ours does at all.
Anyway thanks.
July 28, 2014 at 1:12 am
metin.albayrak (7/25/2014)
Hi, thanks for the advice, I know that backupscript already but it looks like to me that you haven't checked out what ours does at all.Anyway thanks.
How can it "look like" I checked your script out or not? I infact read through the whole script intially multiple times, otherwise I would not have made the statement.
If this is a production system what are you doing for backups while trying to figure out this procedure? If this is production and you are using code written by someone no longer there, I would implement maintenance plans to keep the data backed up for the company. Then while that is running successfully, work on rewriting the procedure in a manner that I understood everything it was doing, especially if I was sole support for the environment.
There is nothing in that procedure that would cause it to skip any database based on the size of it. The script is not aware of the size of any database, just the name of it. In order to deteremine what might be causing it to "stop" (which I have no clue what you mean by that) would be to include error handling (e.g. try/catch blocks).
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply