Backup Of All Database of SQL which are attached

DECLARE @path VARCHAR (500) /* Path of the Backup Files */

DECLARE @folderdate VARCHAR (75) /* The subdir for my backups with Format YYYYMMDD */

DECLARE @cmd VARCHAR (4000) /* The command to create Subdir */

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE @PreciceDateTime varchar(255)

DECLARE @ServerName varchar(50)

DECLARE @NetworkBackupShare varchar(75)



--Network or local disk path you wish to use, such as D:\Backup

SET @NetworkBackupShare = N'C:\backup'



--Create a dynamic path for the backup of the databases based on datetime



EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

/* gives us YYYYMMDD

hour hh

minute mi, n

second ss, s

*/


--PRINT @folderdate



SET @ServerName = (SELECT @@servername)

/* Path as C:\Backup\YYYYMMDD */

SET @path =''

/* Create the command that will do the creation of the folder*/

SET @cmd = N'mkdir ' + @path

--PRINT @cmd

/* Create the new directory */

EXEC master.dbo.xp_cmdshell @cmd , no_output

/* now I can direct all the backup file to the created subdirectory like,

SET filename = path [other_variable/s] ‘.BAK‘ */

/*******************************************/

--Now let's actually do the backups to the path created above

DECLARE DATABASES_CURSOR CURSOR FOR

   select

       DATABASE_NAME   = db_name(s_mf.database_id)

   from

       sys.master_files s_mf

   where

      -- ONLINE

      -- s_mf.state = 0



      -- Only look at databases to which we have access

    has_dbaccess(db_name(s_mf.database_id)) = 1



       -- Not master, tempdb or model

   and db_name(s_mf.database_id) not in ('Master','tempdb','model')

   group by s_mf.database_id

   order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

   declare @DBFileName varchar(256)   

   set @DBFileName = --datename(dw, getdate()) + ' - ' +

                      replace(replace(@DBName,':','_'),'\','_') + '.BAK'

       PRINT @path

   exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = ''' + @path +

       @DBFileName + ''' WITH NOFORMAT, INIT,  NAME = N''' +

       @DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 100')



   FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR