Jul 5, 2022

Schedule auto backup of SQL database at specific location

You can use this SQL script to backup particular database to a specific location, 

-----------------------------------------------------------------
-- *****    Backup Database Script - by Nitin Sawant [email protected]   *****
--
-- Modify only two variables
-- 1. @sourcedatabaseName
-- 2. @restoreDirectory
--
-- Date Created: Jul 05, 2022
-----------------------------------------------------------------
use master
go

declare @backupFileName varchar(100), @restoreDirectory varchar(100),
 @databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
 @databaseDataFile varchar(100), @databaseLogFile varchar(100),
 @databaseName varchar(100), @execSql nvarchar(1000), @sourcedatabaseName nvarchar(1000)
-----------------------------------------------------------------
-- Set the name of the database to restore
set @sourcedatabaseName = 'Gitea'--Source DB
-----------------------------------------------------------------
-- Set the path to the directory containing the database backup
set @restoreDirectory = 'C:\Softwares\gitea db backup\' -- such as 'c:\temp\', the directory where the .bak of source DB will be created

--Take a Fresh DB backup
DECLARE @Dt DATETIME
SET @Dt = GETDATE()
DECLARE @BkAddress NVARCHAR(1000)
--the database name and today's date
set @backupFileName = @restoreDirectory + @sourcedatabaseName + '_'+DATENAME ( day , @Dt )+''+LEFT(DATENAME ( month , @Dt ),3)+''+DATENAME ( year , @Dt )+''+REPLACE(RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7),':','.')+'-'+DATENAME ( s , @Dt )+'.bak'
PRINT 'Creating backup of the database at:' + @BkAddress
EXEC('Backup Database '[email protected]+' To Disk ='''+ @backupFileName+'''')
go

using SQL Server Agent you can schedule execution of this script to periodically take backup of SQL DB.


No comments:

Post a Comment

Be the first to comment on this post.