locked
weekly full backup sql 2016 RRS feed

  • Question

  • Hi experts,

     

    I am running sql 2016 engine; it is a reporting data mart, so I consume all the data with ETLs;even though that, I would like to have it backed up…

     

     

    My question are:

     

    -are ‘maintenance plans’ still used? (Or will it soon be deprecated)?

    -what’s the quickest way to achieve: weekly full backup of all dbs to a folder XYZ and not sure if overwrite backups or delete (any file in the folder older than 2 weeks).

     

     

    Thoughts? Any link is appreciated.

     ps: no, I do not need to restore to 'point in time'

     

    Thursday, April 12, 2018 9:51 AM

Answers

  • Hello,

    Maintenance plans are still available, behind the Scene a SSIS package is created for the maintenance Task.

    Plans can easily schedule od daily, weekly or monthly base and there is also a cöeanup Task available to delete backup older the x days.

    Use the Maintenance Plan Wizard


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621MVP Thursday, April 12, 2018 11:36 AM
    • Marked as answer by Olaf HelperMVP Saturday, April 28, 2018 4:58 AM
    Thursday, April 12, 2018 10:08 AM

All replies

  • Hello,

    Maintenance plans are still available, behind the Scene a SSIS package is created for the maintenance Task.

    Plans can easily schedule od daily, weekly or monthly base and there is also a cöeanup Task available to delete backup older the x days.

    Use the Maintenance Plan Wizard


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Shanky_621MVP Thursday, April 12, 2018 11:36 AM
    • Marked as answer by Olaf HelperMVP Saturday, April 28, 2018 4:58 AM
    Thursday, April 12, 2018 10:08 AM
  • Create a table where you store iDs of the databases you want to backup. The below procedure traverse thru the is and backups the databases to the path you provided. It also creates sub-folder for that database and last step it deletes the old (14 days) backups
    EXEC sp_DatabasesToBackup @LocalBackupPath = 'E:\folder\DB'
         
    CREATE PROCEDURE [dbo].[sp_DatabasesToBackup] @LocalBackupPath AS VARCHAR(8000)
    AS
    DECLARE @name AS SYSNAME
    DECLARE @CommandString AS VARCHAR(8000)
    DECLARE @SQL AS VARCHAR(8000)
    DECLARE  @FS SMALLINT

    SET NOCOUNT ON

    CREATE TABLE #t(dbname VARCHAR(8000))

    BEGIN TRY
    ;WITH cte
    AS
    (
    SELECT  [name],ROW_NUMBER() OVER (ORDER BY name) rn
    FROM sysdatabases INNER JOIN
    DatabasesToBackup ON
    sysdatabases.dbid = DatabasesToBackup.dbid
    WHERE  DATABASEPROPERTYEX ( [name] , 'status' )='ONLINE'
    ) INSERT INTO #t SELECT [name] FROM cte 
    ORDER BY rn

    DECLARE sysdatabasesCursor CURSOR FOR
    SELECT dbname FROM #t

    ----END

    OPEN sysdatabasesCursor
    FETCH NEXT FROM sysdatabasesCursor
    INTO @name
    SELECT  @FS =  @@FETCH_STATUS
    WHILE (@FS <>-1)-- 0 ---=<>-1
    BEGIN
      ---PRINT 'Start Backup ' + @name + ' database. '

      SET @CommandString = 'IF NOT EXIST "' + @LocalBackupPath + '\' + @name + '" MD "' + @LocalBackupPath + '\' + @name + '"'

      EXEC xp_cmdshell @CommandString

      ---PRINT 'Create Local Dirctory Complete.'

      SET @SQL = 'BACKUP DATABASE [' + @name + '] TO DISK = N''' + @LocalBackupPath + '\' + @name + '\' + @name + CONVERT(VARCHAR, GETDATE(), 112) + '.bak'' WITH INIT, CHECKSUM, NOUNLOAD, NAME = N''' + @name + ' backup'', SKIP, STATS = 10, NOFORMAT'

      EXEC(@SQL)

      PRINT 'End Backup ' + @name + ' database.' 

      SET @CommandString = 'del "' + @LocalBackupPath + '\' + @name + '\' + @name + CONVERT(VARCHAR, GETDATE(), 112)-14 + '.bak"'

      EXEC xp_cmdshell @CommandString

      PRINT 'Delete1 Complete.'




      FETCH NEXT FROM sysdatabasesCursor INTO @name
      SELECT @FS =  @@FETCH_STATUS

      SELECT @@FETCH_STATUS  AS '@@FETCH_STATUS'
      SELECT @FS AS '@FS'
    END

    CLOSE sysdatabasesCursor
    DEALLOCATE sysdatabasesCursor
    END TRY

    BEGIN CATCH
           SELECT ERROR_MESSAGE()
           ---INSERT INTO Errors(ERRMSG,CommandString) SELECT ERROR_MESSAGE(),@CommandString
    END CATCH;




    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 12, 2018 10:14 AM
    Answerer