How to create Full, LOG backup of database.

Problem: How to create Full, LOG backup of database.

Solution: BACKUP (Transact-SQL)

Backs up a complete SQL Server database to create a database backup, or one or more files or filegroups of the database to create a file backup (BACKUP DATABASE). Also, under the full recovery model or bulk-logged recovery model, backs up the transaction log of the database to create a log backup (BACKUP LOG)

Some developers take the full back up before they run any nasty code on the database. So just in case if something screws up the database the can restore it back to previous state, also it’s a good maintenance plan to take full back up regularly (at least once a week).

Example:

In this article I am going to show you how we can create a database backup to a disk.

Also make sure to create the folder where you have to save the backup, below i have created a temp folder in C drive for my backup drops

Example1:

-- Backing up a database to disk with noformat  to add to existing file

BACKUP DATABASE DB to disk = 'C:\Temp\db.bak'

WITH NOFORMAT

--backing up a database to disk with clearing out the existing files.

BACKUP DATABASE DB to disk = 'C:\Temp\db.bak'

WITH FORMAT

--backing up the log files to a disk(set the recover mode to full and also take a full backup before you run the log backup)

ALTER DATABASE DB SET RECOVERY FULL

BACKUP LOG DB to disk = 'C:\Temp\db.bak'

WITH NOFORMAT

OUTPUT:

Processed 1760 pages for database 'DB', file 'DB' on file 1.

Processed 2 pages for database 'DB', file 'DB_log' on file 1.

BACKUP DATABASE successfully processed 1762 pages in 0.085 seconds (161.948 MB/sec).

100 percent processed.

Processed 5 pages for database 'DB', file 'DB_log' on file 1.

BACKUP LOG successfully processed 5 pages in 0.018 seconds (2.170 MB/sec).

Featured Script:

DECLARE @DBName varchar(255)

DECLARE @DateStamp VARCHAR(50)

DECLARE @Path VARCHAR(255)

BEGIN

      SET @DBName = 'DB'

      SET @DateStamp = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '') + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')

      SET @Path = 'C:\Temp\'

      SET @Path = @path + @DBNAME+@DateStamp + '.bak'

EXEC('ALTER DATABASE '+ @DBNAME+' SET RECOVERY  FULL')

BACKUP DATABASE @DBNAME

TO DISK = @Path

WITH FORMAT

BACKUP LOG @DBNAME

 TO DISK = @Path

 WITH  NOFORMAT, --uses the existing path to write the backup

NOINIT,  --appends to the backupset

SKIP,--skips the check the database backup set expiration

NOREWIND,-- will keep the tape open when multiple backups are run to a tape(increases performance)

NOUNLOAD, --

STATS = 10 – shows the progress for every 10 percent

END

GO