SQL Database backup - append, overwrite or new file?

[DaveBenson]

Hi,

I am in the process of working out a backup strategy for an SQL database that uses the full recovery model and also looking for a better general understanding that I would be able to apply to future databases.
I am clear (I think) about using full / differential / log backups. However I'm confused about backup sets and whether to use append to existing set or overwrite or whether to write to a new file each time.
Say for the sake of argument I have decided to take a full backup every month, a differential backup every week and a log backup every day and with the backup files going to a network drive.
If I used append for every backup, I presume I could get recovery up to the nearest day if needed. However the file for the backup set would soon become pretty huge as it would have a new copy of full database added to it each month, as well as everything else.
If on the other hand, I used overwrite every time does this mean I lose everything apart from the last backup? If this is the case and my last backup was the log (the most likely scenario), does this mean the backup would be useless for recovery as I would also need the full backup and any log backups before this one (all of which will have been overwritten) for recovery to work?
If I have got this right, neither strategy seems acceptable. Would it instead make more sense to overwrite when taking the full backup and then append when taking the differential and logs. Then I would in effect start a group of backups each month (I would have to accept that I would not be able to restore any further back than the last month).
I have noticed on some posts people say about making a different file for each backup. This makes some sense as well - however I'm not clear at all in what I should shouldn’t do - eg can all backups go in different files or should all log backups go in the same file etc. When dabbling with this on a practice database, I get a message saying media is only formatted to support one media family.
Whilst I could probably work out what seems a sensible way forward ,bearing in mind I'm pretty new to SQL server, I think it would be sensible to get an idea of is how experienced DBA's would normally approach this. The part of backups relating to full backups, log backups etc seems very well documented on the web with plenty of advice for any novices but I haven't found anything that combines this with whether backups should append, overwrite or be on different backup files

Any feedback welcome - even if it is just to direct me to a page / blog that clearly explains this!

Thanks

 

[SQLScholar]

My pesonal way of doing it is that each backup should be to its own file. Then there should be a clean up process running to delete old backups (once they are on backup medium and offsite, and enough newer onsite backups are available).

The reason i do this.... well if a file corrupts - i would rather lose one backup then 10!



----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

by the way - the easiest way i have found to do this is to backup to a different file per date base and just give the foldername.

If i remember correctly (not using SQL much a mo) the backups will be date stamped in the name. That way you will never overwrite and just need a clean up of backups on your schedule.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[DaveBenson]

Thanks SQLScholar from what I know I'm inclined to agree with you. I'd rather have different backup in separate files so you can delete old ones etc. As I mentioned above I had a bit of trouble when trying to make a new backup file but I'll look into this further. In the meantime, any other feedback is welcome!

 

[SQLScholar]

The way i did it was not to set a filename but to say to backup to a folder with each database backing up to a separate file.

Worked for me!

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[fredericofonseca]

This is one sp one of my customers uses to do backups - the backup is one per week day - filename is created as "weekday - dbname.bak".. e.g. "friday - dbname.bak"

Work from this one to create what you need

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[BackupSQLDatabases]    Script Date: 04/11/2012 23:20:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BackupSQLDatabases]
AS
BEGIN

/* Declarations & Variables */
DECLARE @path VARCHAR (500) /* Path of the Backup Files */
DECLARE @folderdate VARCHAR (75) /* The subdir for my backups with Format YYYYMMDD */
DECLARE @cmd VARCHAR (4000) /* The command to create Subdir */
DECLARE @sql VARCHAR (4000) /* The command to create Subdir */
DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE @PreciceDateTime varchar(255)
DECLARE @ServerName varchar(50)
DECLARE @NetworkBackupShare varchar(75)
--Network or local disk path you wish to use, such as D:\Backup
SET @NetworkBackupShare = N'D:\Backup'
--Create a dynamic path for the backup of the databases based on datetime
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
/* gives us YYYYMMDD
hour hh
minute mi, n
second ss, s
*/
SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) + N'-' + N'H' + CONVERT(VARCHAR(20), DATEPART(hour, GETDATE()), 112) + N'M' + CONVERT(VARCHAR(20), DATEPART(minute, GETDATE()), 112) + N'S' + CONVERT(VARCHAR(20), DATEPART(second, GETDATE()), 112)
--PRINT @folderdate
--Get Server name/
SET @ServerName = (SELECT @@servername)
/* Path as C:\Backup\YYYYMMDD */
--SET @path = @NetworkBackupShare + N'\' + @ServerName + N'\' + @folderdate + N'\'
SET @path = @NetworkBackupShare + N'\'
/* Create the command that will do the creation of the folder*/
SET @cmd = N'mkdir ' + @path
--PRINT @cmd
/* Create the new directory */
EXEC master.dbo.xp_cmdshell @cmd , no_output
/* now I can direct all the backup file to the created subdirectory like,
SET filename = path [other_variable/s] ‘.BAK‘ */
/*******************************************/
--Now let's actually do the backups to the path created above
DECLARE DATABASES_CURSOR CURSOR FOR
    select
        DATABASE_NAME   = db_name(s_mf.database_id)
    from
        sys.master_files s_mf
    where
       -- ONLINE
        s_mf.state = 0 
       -- Only look at databases to which we have access
    and has_dbaccess(db_name(s_mf.database_id)) = 1 
        -- Not master, tempdb or model
    and upper(db_name(s_mf.database_id)) not in ('MASTER','TEMPDB','MODEL','MSDB')
    group by s_mf.database_id
    order by 1
OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    declare @DBFileName varchar(256)    
    set @DBFileName = datename(dw, getdate()) + ' - ' + 
                       replace(replace(@DBName,':','_'),'\','_') + '.BAK'
        PRINT @path
    set @sql =  'BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @path + 
        @DBFileName + ''' WITH FORMAT, INIT, NAME = N''' + 
        @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100'
exec  (@sql)
    FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
/*******************************************/
EXEC sp_configure 'xp_cmdshell', 0;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE
END

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SQLScholar]

If you wish to do it by script i would use this guys procs:

http://ola.hallengren.com/

He has won many awards for this code.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[DaveBenson]

Thanks guys.
I'd probably just be doing it through SQL management studio at the moment, but will start using more script once I understand the process better, so code is useful.