As you all know, Ms SQL has the ability to automatically back up, but if for some reason it can't be backed up automatically, or if we want to back it up manually, we can use the SQL statement below to perform the backup.
--------------------Code starts-------------
Use [master]
------Delete old data-------------------------------------------------------------
DECLARE @delTime VARCHAR (100)
SET @delTime = CONVERT (VARCHAR (+), GETDATE (), 126)-----Current time
EXECUTE MASTER.dbo.xp_delete_file 0, ' D:\DataBak ', ' bak ', @delTime, 1
DECLARE @FILENAME VARCHAR (500)
DECLARE @DATABakPath VARCHAR (500)
DECLARE @DATABaseName VARCHAR (500)
DECLARE @DATAFullName VARCHAR (500)
SET @DATABakPath = ' D:\DataBak\ '
DECLARE @temp TABLE (a int, b int, c int)
DECLARE @exeText VARCHAR (100)
SET @FILENAME = REPLACE (
REPLACE (
REPLACE (CONVERT (VARCHAR, GETDATE (), 120), '-', '),
‘ ‘,
‘‘
),
‘:‘,
‘‘
) + '. Bak '
------------Traverse all user databases for backup-------------------------------------------------------------------
DECLARE my_cursor Cursor--Defining cursors
For
(
SELECT NAME
From sys.databases
WHERE NAME isn't in (' Master ', ' msdb ', ' tempdb ', ' model ',
' ReportServerTempDB ', ' ReportServer ')
)--Isolate the required set to cursor has an
OPEN My_cursor; --Open cursor
FETCH NEXT from My_cursor to @DATABaseName; --Read the first row of data
While @ @FETCH_STATUS = 0
BEGIN
SET @DATAFullName = @DATABakPath + @DATABaseName
SET @exeText = ' mkdir ' + @DATAFullName--Create a backup directory
EXEC xp_cmdshell @exeText,
No_output
SET @DATAFullName = @DATAFullName + ' \ ' + @FILENAME + '
BACKUP DATABASE @DATABaseName to DISK = @DATAFullName
FETCH NEXT from My_cursor to @DATABaseName; --Read the next line of data
END
CLOSE My_cursor; --Close cursor
Deallocate my_cursor; --Releasing cursors
PRINT '-----------backup complete---------------------' + CONVERT (VARCHAR (+), GETDATE (), 126) + '---------------'
--------------------End of Code-------------
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
SQL one-click Backup user Database
