I want to make full database backup of my server's all databases with unique name daily. For that I have an idea to keep timestamp which will make database copy separate. Suppose there is a database on server named ABCD then it should be backuped like:
ABCD_21_03_2013
ABCD_22_03_2013
How can I do this. I don't know much about these types of SQL Backup JOBS.
In Task Scheduler, right-click on Task Schedule Library and click on Create Basic task…. Enter the name for the new task (for example: SQLBackup) and click Next. Select Daily for the Task Trigger and click Next. Set the recurrence to one day and click Next.
A backup of data in a complete database (a database backup), a partial database (a partial backup), or a set of data files or filegroups (a file backup).
Right-click the database that you want to back up, click Tasks, and then click Back Up. In the Back Up Database - DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.
It includes the original file name, plus the date of the backup. This can be useful when trying to locate a suitable backup file to restore the database.
To create a daily backup with a name such as Filename_MM_DD_YYYY:
Existing code looks like:
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'E:\Test\AdventureWorks.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Replace it to be
DECLARE @SQLStatement VARCHAR(2000)
SET @SQLStatement = 'E:\Test\AdventureWorks_' + CONVERT(nvarchar(30), GETDATE(), 110) +'.bak'
BACKUP DATABASE [AdventureWorks2012] TO DISK = @SQLStatement
6 Save the job
The database backups will be named:
AdventureWorks_07-29-2013
AdventureWorks_07-30-2013
AdventureWorks_07-31-2013
Now all you have to do is make the right schedule
Automatic backup of all databases on the server.
About Jobs:
http://msdn.microsoft.com/en-us/library/ms190268.aspx
Query:
SET NOCOUNT ON;
DECLARE
@FileName NVARCHAR(1024)
, @DBName NVARCHAR(256)
, @PathName NVARCHAR(256)
, @Message NVARCHAR(2048)
, @IsCompressed BIT
SELECT
@PathName = 'D:\BACKUP\'
, @IsCompressed = 1
DECLARE db CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
SELECT
sd.name
, file_path = @PathName + FileDate + '_' + name + '.bak'
FROM sys.databases sd
CROSS JOIN (
SELECT FileDate = 'ABCD_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '_')
) fd
WHERE sd.state_desc != 'OFFLINE'
AND sd.name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY sd.name
OPEN db
FETCH NEXT FROM db INTO
@DBName
, @FileName
WHILE @@FETCH_STATUS = 0 BEGIN
DECLARE @SQL NVARCHAR(MAX)
SELECT @Message = REPLICATE('-', 80) + CHAR(13) + CONVERT(VARCHAR(20), GETDATE(), 120) + N': ' + @DBName
RAISERROR (@Message, 0, 1) WITH NOWAIT
SELECT @SQL =
'BACKUP DATABASE [' + @DBName + ']
TO DISK = N''' + @FileName + '''
WITH FORMAT, ' + CASE WHEN @IsCompressed = 1 THEN N'COMPRESSION, ' ELSE '' END + N'INIT, STATS = 15;'
EXEC sys.sp_executesql @SQL
FETCH NEXT FROM db INTO
@DBName
, @FileName
END
CLOSE db
DEALLOCATE db
Output:
BACKUP DATABASE [AdventureWorks2008R2]
TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2008R2.bak'
WITH FORMAT, COMPRESSION, INIT, STATS = 15;
BACKUP DATABASE [AdventureWorks2008R2_Live]
TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2008R2_Live.bak'
WITH FORMAT, COMPRESSION, INIT, STATS = 15;
BACKUP DATABASE [AdventureWorks2012]
TO DISK = N'D:\BACKUP\ABCD_24_05_2013_AdventureWorks2012.bak'
WITH FORMAT, COMPRESSION, INIT, STATS = 15;
Results:
2013-05-24 09:54:34: AdventureWorks2008R2
15 percent processed.
30 percent processed.
45 percent processed.
60 percent processed.
75 percent processed.
90 percent processed.
Processed 23416 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Data' on file 1.
Processed 1 pages for database 'AdventureWorks2008R2', file 'AdventureWorks2008R2_Log' on file 1.
BACKUP DATABASE successfully processed 23417 pages in 4.052 seconds (45.148 MB/sec).
.....
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With