Backup DB Full- Diff- Log


SolutionThere are two components to this; the first is the backup command and the second is the scheduling needed to run the backups.
Backup CommandsThere are a few things that we need to setup.  The first is to create a stored procedure that allows us to dynamically generate the backup file name as well as what type of backup to run Full, Differential or Transaction Log backup. The default for this stored procedure is to create the backups in the "C:\Backup" folder.  This can be changed to any folder you like.
The following stored procedure should be created in the master database.  This is just one way of handling this.  There are several other options and enhancements that can be made.
USE [master] 
GO  /****** Object:  StoredProcedure [dbo].[sp_BackupDatabase]    Script Date: 02/07/2007 11:40:47 ******/  SET ANSI_NULLS ON  GO  SET QUOTED_IDENTIFIER ON  GO 

-- ============================================= 
-- Author: Edgewood Solutions 
-- Create date: 2007-02-07 
-- Description: Backup Database 
-- Parameter1: databaseName 
-- Parameter2: backupType F=full, D=differential, L=log
-- =============================================  CREATE PROCEDURE [dbo].[sp_BackupDatabase]  
       @databaseName sysname, @backupType CHAR(1)  AS 
BEGIN 
       SET NOCOUNT ON; 

       DECLARE @sqlCommand NVARCHAR(1000) 
       DECLARE @dateTime NVARCHAR(20) 

       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') + 
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

       IF @backupType = 'F' 
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName + 
               ' TO DISK = ''C:\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK''' 
        
       IF @backupType = 'D' 
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName + 
               ' TO DISK = ''C:\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL' 
        
       IF @backupType = 'L' 
               SET @sqlCommand = 'BACKUP LOG ' + @databaseName + 
               ' TO DISK = ''C:\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN''' 
        
       EXECUTE sp_executesql @sqlCommand  END
The second part of this is to create a SQLCMD file to run the backup commands.  Here is a simple SQLCMD file that backups databases master, model and msdb. 
This file gets saved as backup.sql and for our purposes this is created in the "C:\Backup" folder, but again this could be put anywhere.
sp_BackupDatabase 'master', 'F'
GO
sp_BackupDatabase 'model', 'F'
GO
sp_BackupDatabase 'msdb', 'F'
GO
QUIT

Comments

Post a Comment

Popular posts from this blog

Who Deleted and Drop the record in table

CREATE DATABASE ReadingDBLog; GO -- Create tables. USE ReadingDBLog; GO CREATE TABLE [Location] (     [Sr.No] INT IDENTITY,     [Date] DATETIME DEFAULT GETDATE (),     [City] CHAR (25) DEFAULT 'Bangalore');           select * from [Location]       INSERT INTO Location DEFAULT VALUES ; GO 100 DELETE Location WHERE [Sr.No]=10 GO SELECT * FROM Location WHERE [Sr.No]=10 GO SELECT     [Transaction ID],     Operation,     Context,     AllocUnitName   FROM     fn_dblog(NULL, NULL) WHERE     Operation = 'LOP_DELETE_ROWS'       SELECT     Operation,     [Transaction ID],     [Begin Time],     [Transaction Name],     [Transaction SID] FROM     fn_dblog(NULL, NULL) WHERE     [Transaction ID] = '0000:000002ef' AND     [Operat...
Read more

Generate Password

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_GeneratePassword](@Length int, @PasswordTemp varchar(100) output) AS BEGIN DECLARE @RandomID varchar(32) DECLARE @counter smallint DECLARE @RandomNumber float DECLARE @RandomNumberInt tinyint DECLARE @CurrentCharacter varchar(1) DECLARE @ValidCharacters varchar(255) SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' DECLARE @ValidCharactersLength int SET @ValidCharactersLength = len(@ValidCharacters) SET @CurrentCharacter = '' SET @RandomNumber = 0 SET @RandomNumberInt = 0 SET @RandomID = '' SET NOCOUNT ON SET @counter = 1 WHILE @counter < (@Length + 1) BEGIN         SET @RandomNumber = Rand()         SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))         SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1) ...
Read more

Key Board Shortcuts

Image
100 KEYBOARD SHORTCUTS CTRL + C (Copy) CTRL + X (Cut) CTRL + V (Paste) CTRL + Z (Undo) DELETE (Delete) SHIFT + DELETE (Delete the selected item permanently without placing the item in the Recycle Bin) CTRL while dragging an item (Copy the selected item) CTRL + SHIFT while dragging an item (Create a shortcut to the selected item) F2 KEY (Rename the selected item) CTRL + RIGHT ARROW (Move the insertion point to the beginning of the next word) CTRL + LEFT ARROW (Move the insertion point to the beginning of the previous word) CTRL + DOWN ARROW (Move the insertion point to the beginning of the next paragraph) CTRL + UP ARROW (Move the insertion point to the beginning of the previous paragraph) CTRL + SHIFT with any of the arrow keys (Highlight a block of text) SHIFT with any of the arrow keys (Select more than one item in a window or on the desktop, or select text in a document) CTRL + A (Select all) F3 KEY (Search for a file or a folder) ALT...
Read more