May 11, 2009 at 6:47 am
I'm working with SQL Server Express. I used the script at the end of this post to create backups, and then I used task scheduling on Windows Server 2003 to run the backups automatically.
What is the script to create backups of the logs? What is the script to delete old backups?
here's my script to backup the databases
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'D:\Backups\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
May 11, 2009 at 7:41 am
Does xp_CmdShell work on SQL Express?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 11, 2009 at 9:45 am
Yes, it is available - just an FYI for anyone that reads this, it is disabled by defualt in SQL server express, so you have to enable it
May 11, 2009 at 11:24 am
I would use something like thid to mak a stored procedure to purge the backup files:
drop table #DirOut
go
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @EarliestDate VARCHAR(20) -- purge all backups earlier than this
Declare @dos varchar(4000)
Set @dos = ''
Create table #DirOut(txt nvarchar(2048))
Select @dos = 'DIR ' + @path + '*.BAK'
INSERT into #DirOut
Exec xp_cmdshell @dos
Delete From #DirOut Where txt is Null
Delete From #DirOut Where txt Like '%%'
Delete From #DirOut Where txt NOT Like '[0-9]%'
Set @dos = ''
;With cteFileName as (
select substring(txt,40,len(txt)) as Nam
, Len(txt)-39 as txtLen
from #dirout
where Charindex('_',txt) > 0
), cteNamDat as (
Select Nam
, Substring(Nam, txtLen-Charindex('_',Reverse(Nam))+2
, Charindex('_',Reverse(Nam))-5 )
As Dat
From cteFileName
), cteDate as (
Select Nam, Dat
, CAST( Dat as datetime) as FileDate
From cteNamDat
Where IsDate(Dat) = 1
)
DELETE ' + @path + Nam
From cteDate
Where FileDate < @EarliestDate
Print ' Executing these DOS commands:'
Print @dos
Exec xp_cmdshell @dos
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply