DB Back Up

How To Take a BAck Up Using TSQL:

CREATE DATABASE [TestDB]

ALTER DATABASE [TestDB] SET RECOVERY FULL -- (OR SIMPLE OR BULK LOGGED)
BACKUP LOG [TestDB] TO
DISK = N'C:\TestDBLog.bak'
WITH NOFORMAT, NOINIT,
NAME = N'TestDB-Transaction Log  Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10

If you are managing a lot of databases, you might need to scratch your head for a while to remember where and when did you backed up a database. Fortunately, this information is stored in MSDB database and can be easily retrieved using below tables:
1. backupmediafamily – contains one row for each media family.
2. backupset – contains a row for each backup set.
3. backupmediaset – contains a row for each backup media set.
You can join these tables to find out where/when a backup was made.

SELECT      BS.Backup_Start_Date, BS.Backup_Finish_Date,

            BMF.Physical_Device_Name AS [Backed-Up To],

            BMS.Software_Name AS [Performed Using],

            BS.Name AS [Backup Set Name],

            BS.User_Name AS [Performed By], BS.Server_Name,

            BS.Database_Name,

            CASE BS.Type      WHEN 'D' THEN 'Full'

                              WHEN 'I' THEN 'Differential'

                              WHEN 'L' THEN 'T-Log'

                              WHEN 'F' THEN 'File'

                              WHEN 'G' THEN 'Diff. File'

                              WHEN 'P' THEN 'Partial'

                              WHEN 'Q' THEN 'Diff. Partial'

                              ELSE 'Other' END AS [Backup Type],

            CAST(((BS.Backup_Size/1024)/1204) AS NUMERIC(10,2))

            AS [Size (MB)] ,

            CAST(((BS.Compressed_Backup_Size/1024)/1204) AS NUMERIC(10,2))

            AS [Compressed (MB)],

            CASE BS.Is_Password_Protected WHEN 1 THEN 'Yes'

                                          ELSE 'No' END AS [Password?],

            CASE BMS.Is_Compressed  WHEN 1 THEN 'Yes'

                                    ELSE 'No' END AS [Compressed?]

FROM        msdb..BackupMediaFamily BMF

INNER JOIN  msdb..BackupMediaSet BMS
            ON BMF.Media_Set_ID = BMS.Media_Set_ID

INNER JOIN  msdb..BackupSet BS
            ON BS.Media_Set_ID = BMS.Media_Set_ID

–WHERE      BS.Database_Name = 'MyDatabaseName'

–AND        BS.Backup_Finish_Date BETWEEN 'StartDate' AND 'EndDate'

ORDER BY    BS.Backup_Finish_Date DESC

You can add a filter for a specific database name and backup duration to filter the result set.

The above query will return all information for all databases present on the current instance of SQL Server.