SQLsafe
Problem: I want to be able to backup all SQL Server databases and create a separate folder for each database. Finally, I want to automatically delete any backups that I have over 2 days old.
Answer: Use the scripts in this article along with Idera SQL server to set backups for all of your databases and to create a separate folder for each database before doing the backup. In addition, these scripts offer a parameter that will delete any old backup archives that are older than a certain time period (2 days for example.)
Note: The text in RED will need to be changed after you generate the SQL Script in SQLsafe for All User DBs and System DBs. Also, this uses XP_CMDSHELL to create the directories. So, the account that your MSSQLSERVER Service runs as must have write permissions on the directory you want to backup to. Finally, XP_CMDSHELL is not enabled by default within SQL Server 2005, so if you are backing up on SQL Server 2005 databases, you will need to enable this first.
Full Backup for one user database deleting any user databases over 1 day old using T-SQL (Use this for backing up ONE user database and you will never have to edit it again):
DECLARE @result int
DECLARE @dbname nvarchar(128)
DECLARE @filepath nvarchar(1024)
DECLARE @fileext nvarchar(10)
DECLARE @rootpath nvarchar(619)
DECLARE @backuptype varchar(10)
DECLARE @time datetime
DECLARE @timestamp varchar(15)
DECLARE @servername nvarchar(255)
DECLARE @delete nvarchar(15)
DECLARE @mkdir nvarchar(128)
SET @dbname = 'Healthcare'
SET @servername = replace(@@servername, '\', '_')
SET @time = getdate()
SET @timestamp = convert(varchar, @time, 112) + cast(datepart(hh,@time) as varchar)+ cast(datepart(mi,@time) as varchar)
SET @rootpath = 'C:\Backup\'
SET @fileext = '.safe'
SET @backuptype = 'Full'
SET @filepath = @rootpath + @dbname + '\' + @servername +'_'+ @dbname +'_'+ @backuptype+'_'+ @timestamp+ @fileext
SET @mkdir = 'mkdir c:\backup\' + '"' + @dbname + '"'
exec xp_cmdshell @mkdir
EXEC @result = [master].[dbo].[xp_ss_backup] @database = 'Healthcare',
@filename = @filepath,@instancename = 'SQL2005',@backupname = 'Healthcare',
@desc = 'Healthcare',@init = 1,@compressionlevel = 'ispeed',@threads = 3,
@managementserver = 'JGAIGEL2',@delete = '1Days'
IF (@result != 0) RAISERROR('', 16, 1)
GO
Full Backup for all user databases deleting any user databases over 1 day old using T-SQL (Use this for capturing ALL user databases and you will never have to edit it again):
DECLARE @result int
DECLARE @dbname nvarchar(128)
DECLARE @rootpath nvarchar(619)
DECLARE @fileext nvarchar(10)
DECLARE @filepath nvarchar(1024)
DECLARE @backuptype varchar(10)
DECLARE @timestamp varchar(15)
DECLARE @time datetime
DECLARE @counter int
DECLARE @maxcount int
DECLARE @servername nvarchar(255)
DECLARE @failed int
DECLARE @delete nvarchar(15)
DECLARE @mkdir nvarchar(128)
IF object_id('tempdb..#AllDatabases') IS NOT NULL DROP TABLE #AllDatabases
CREATE TABLE #AllDatabases
(
id INT IDENTITY,
name NVARCHAR(128)
)
INSERT INTO #AllDatabases SELECT name FROM master..sysdatabases WHERE name NOT IN ('tempdb', 'master', 'model', 'msdb')
SET @dbname = NULL
SET @servername = replace(@@servername, '\', '_')
SET @time = getdate()
SET @timestamp = convert(varchar, @time, 112) + cast(datepart(hh,@time) as varchar) + cast(datepart(mi,@time) as varchar)
SET @rootpath = 'C:\Backup\'
SET @fileext = '.safe'
SET @backuptype = 'Full'
SET @failed = 0
SET @counter = 1
SELECT @maxcount = MAX(id) FROM #AllDatabases
WHILE @counter <= @maxcount
BEGIN
SELECT @dbname = name FROM #AllDatabases WHERE id = @counter
SET @filepath = @rootpath + @dbname + '\' + @servername +'_'+ @dbname +'_'+ @backuptype+'_'+ @fileext
SET @mkdir = 'mkdir c:\temp1\' + '"' + @dbname + '"'
exec xp_cmdshell @mkdir
EXEC @result = [master].[dbo].[xp_ss_backup] @database = @dbname,@filename = @filepath,@instancename = 'SQL2005',@backupname = @dbname,@desc = @dbname,@init = 1,@compressionlevel = 'ispeed',@threads = 3,@managementserver = 'JGAIGEL2',@delete = '1Days'
IF( @result != 0 ) SET @failed = 1
SET @counter = @counter + 1
END
IF (@failed != 0) RAISERROR('One or more backups failed to complete.', 16, 1)
T-Log Backup for User Database Logs and delete after 30 days Using T-SQL (Use this for capturing all user databases logs and you will never have to edit it again, even if you create new user database logs)
DECLARE @result int
DECLARE @dbname nvarchar(128)
DECLARE @rootpath nvarchar(619)
DECLARE @fileext nvarchar(10)
DECLARE @filepath nvarchar(1024)
DECLARE @backuptype varchar(10)
DECLARE @timestamp varchar(15)
DECLARE @time datetime
DECLARE @counter int
DECLARE @maxcount int
DECLARE @servername nvarchar(255)
DECLARE @failed int
DECLARE @delete nvarchar(15)
DECLARE @mkdir nvarchar(128)
IF object_id('tempdb..#AllDatabases') IS NOT NULL DROP TABLE #AllDatabases
CREATE TABLE #AllDatabases
(
id INT IDENTITY,
name NVARCHAR(128)
)
INSERT INTO #AllDatabases SELECT name FROM master..sysdatabases WHERE name NOT IN ('tempdb', 'master', 'model', 'msdb')
SET @dbname = NULL
SET @servername = replace(@@servername, '\', '_')
SET @time = getdate()
SET @timestamp = convert(varchar, @time, 112) + cast(datepart(hh,@time) as varchar) + cast(datepart(mi,@time) as varchar)
SET @rootpath = 'C:\Backup\'
SET @fileext = '.safe'
SET @backuptype = 'Log'
SET @failed = 0
SET @counter = 1
SELECT @maxcount = MAX(id) FROM #AllDatabases
WHILE @counter <= @maxcount
BEGIN
SELECT @dbname = name FROM #AllDatabases WHERE id = @counter
SET @filepath = @rootpath + @dbname + '\' + @servername +'_'+ @dbname +'_'+ @backuptype+'_'+ @fileext
SET @mkdir = 'mkdir c:\temp1\' + '"' + @dbname + '"'
exec xp_cmdshell @mkdir
EXEC @result = [master].[dbo].[xp_ss_backup] @database = @dbname,@filename = @filepath,@backuptype = 'Log',@instancename = 'SQL2005',@backupname = @dbname,@desc = @dbname,@init = 1,@compressionlevel = 'ispeed',@threads = 3,@managementserver = 'JGAIGEL2',@delete = '30Days'
IF( @result != 0 ) SET @failed = 1
SET @counter = @counter + 1
END
IF (@failed != 0) RAISERROR('One or more backups failed to complete.', 16, 1)
Full Backup for System Databases and delete after 1 day Using T-SQL (Use this for capturing all system databases)
DECLARE @result int
DECLARE @dbname nvarchar(128)
DECLARE @rootpath nvarchar(619)
DECLARE @fileext nvarchar(10)
DECLARE @filepath nvarchar(1024)
DECLARE @backuptype varchar(10)
DECLARE @timestamp varchar(15)
DECLARE @time datetime
DECLARE @counter int
DECLARE @maxcount int
DECLARE @servername nvarchar(255)
DECLARE @failed int
DECLARE @delete nvarchar(15)
DECLARE @mkdir nvarchar(128)
IF object_id('tempdb..#AllDatabases') IS NOT NULL DROP TABLE #AllDatabases
CREATE TABLE #AllDatabases
(
id INT IDENTITY,
name NVARCHAR(128)
)
INSERT INTO #AllDatabases SELECT name FROM master..sysdatabases WHERE name IN ('master', 'model', 'msdb')
SET @dbname = NULL
SET @servername = replace(@@servername, '\', '_')
SET @time = getdate()
SET @timestamp = convert(varchar, @time, 112) + cast(datepart(hh,@time) as varchar) + cast(datepart(mi,@time) as varchar)
SET @rootpath = 'C:\Backup\'
SET @fileext = '.safe'
SET @backuptype = 'Full'
SET @failed = 0
SET @counter = 1
SELECT @maxcount = MAX(id) FROM #AllDatabases
WHILE @counter <= @maxcount
BEGIN
SELECT @dbname = name FROM #AllDatabases WHERE id = @counter
SET @filepath = @rootpath + @dbname + '\' + @servername +'_'+ @dbname +'_'+ @backuptype+'_'+ @fileext
SET @mkdir = 'mkdir c:\temp1\' + '"' + @dbname + '"'
exec xp_cmdshell @mkdir
EXEC @result = [master].[dbo].[xp_ss_backup] @database = @dbname,@filename = @filepath,@instancename = 'SQL2005',@backupname = @dbname,@desc = @dbname,@init = 1,@compressionlevel = 'ispeed',@threads = 3,@managementserver = 'JGAIGEL2',@delete = ‘1Days’
IF( @result != 0 ) SET @failed = 1
SET @counter = @counter + 1
END
IF (@failed != 0) RAISERROR('One or more backups failed to complete.', 16, 1)
For more information, or assistance, please contact Idera Customer Support Team at
support@idera.com.
|