How to create Database backup at any specific path using sql query ?

In this post I wrote a SQL query to create Database backup at specified path.

-- by using this script you can create a backup of your database at specified loaction.

         use DemoDB

         GO

                        

      DECLARE @dbName varchar(200);

      DECLARE @backupPath varchar(200);

      DECLARE @backupQuery varchar(800);

        

         -- set you DB name here

         set @dbName = 'DemoDB';

         -- set your backup path here

         set @backupPath = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\';

         -- declare a date variable

         DECLARE @dtval varchar(200);

         -- Set current date value in any formate as you wish

         set @dtval = '_Date_' + convert(varchar, getdate(), 105)

         -- declare a date variable

         declare @tmval varchar(200);

         -- Set current time value in any formate as you wish

         set @tmval = (REPLACE(RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11), ':', '.' ));

         set @tmval = '_Time_'+ rtrim(ltrim(left(@tmval, len(@tmval)-2))) + '_' + ltrim(rtrim(right(@tmval,2)))

        

        -- And here set your query with above variables

         set @backupQuery =  'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + @dtval + @tmval + '.bak'''

         -- Print SQL statement

      print @backupQuery

      --Finally Execute backup script

      EXEC (@backupQuery)

        

        

        

In the above article I try to explain a SQL query to create database backup. I hope its useful to you. Please post your feedback, question, or comments about this article.