January 23, 2007 at 8:06 am
Does anyone have a simple script to backup a single datbase? Would like YYYYMMDDHHMM as part of the file name to make it unique.
DatabaseName = ABC
Backup Location = C:\BackUpSql\
The simpler the better! Thanks.
January 23, 2007 at 9:10 am
You will have to mess with dynamic SQL and fiddly datepart functions. It can be done, but if you want to keep it simple, probably best to use a database maintenance plan.
John
January 23, 2007 at 9:15 am
I did that and for some reason the backup failed. I'm trying again with a new plan, hope it works.
January 23, 2007 at 9:29 am
I'd echo John here. Most people will want to use maintenance plans. Something like this should do it for you, but you'll still have to manage removing old backups, etc...
DECLARE @filenameNVARCHAR ( 500 )
,@hourNCHAR ( 2 )
,@min-2NCHAR ( 2 )
IF DATEPART(hh, GETDATE()) < 10
SET @hour = '0' + CONVERT(NVARCHAR, DATEPART(hh, GETDATE()))
ELSE
SET @hour = CONVERT(NVARCHAR, DATEPART(hh, GETDATE()))
IF DATEPART(mi, GETDATE()) < 10
SET @min-2 = '0' + CONVERT(NVARCHAR, DATEPART(mi, GETDATE()))
ELSE
SET @min-2 = CONVERT(NVARCHAR, DATEPART(mi, GETDATE()))
SET @filename = 'C:\BackUpSql\ABC_' + CONVERT(NVARCHAR, GETDATE(), 112) + @hour + @min-2 + '.BAK'
BACKUP DATABASE ABC TO DISK = @filename
January 23, 2007 at 10:49 am
You can use what Aaron has posted as your first step in your backup job and use this script as the second step to clean up old backups. This one will clean up anything older than two days. Great script that someone on this site gave me!
SET NOCOUNT ON
DECLARE @command VARCHAR(1000)
DECLARE @currentfile VARCHAR(500)
-- Create a temporary table
CREATE TABLE #output (
txtOutput VARCHAR(1000) )
-- Get those database backups that are present on disk
SELECT @command = 'dir X:\Backups\test_BACKUP_*.BAK /B'
INSERT INTO #output (txtOutput)
EXEC master.dbo.xp_cmdshell @command
-- Delete unusful information
DELETE #output
WHERE txtOutput IS NULL OR
txtOutput LIKE 'file not found' OR
txtOutput LIKE 'test_BACKUP_' + CONVERT(VARCHAR,GETDATE(),112) + '.BAK'
OR txtOutput LIKE 'test_BACKUP_' + CONVERT(VARCHAR,DATEADD(dd,-1,GETDATE()),112) + '.BAK'
-- Delete old database backups
IF ( SELECT COUNT(*) FROM #output ) > 0
BEGIN
DECLARE c_file CURSOR FOR
SELECT txtOutput
FROM #output
OPEN c_file
FETCH NEXT FROM c_file INTO @currentfile
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @command = 'DEL X:\Backups\' + @currentfile
EXEC master.dbo.xp_cmdshell @command, no_output
-- Report to what logfile that has been deleted
PRINT 'DELETED FILE: X:\Backups\' + @currentfile
FETCH NEXT FROM c_file INTO @currentfile
END
CLOSE c_file
DEALLOCATE c_file
END
ELSE
BEGIN
-- Report if no files were deleted
PRINT 'NO FILES DELETED'
END
Thanks!
January 23, 2007 at 4:16 pm
What about without cursor...to deleted 3 days older backup....
select @deldate = getdate()- 3
WHILE @deldate <= (getdate()-@FileDelDays)
BEGIN
select @cmd = 'del X:\Backups\test_BACKUP_*'+convert(char(8), @deldate , 112)+'*.trn /S '
EXEC master.dbo.xp_cmdshell @cmd, no_output
select @deldate = @deldate+1
END
MohammedU
Microsoft SQL Server MVP
January 24, 2007 at 2:05 pm
How's about:
use ABC
EXEC sp_updatestats
GO
DECLARE @rtn int
EXEC @rtn = master.dbo.xp_sqlmaint N'-D ABC -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB C:\BackUpSql\ -DelBkUps 2DAYS'
IF @rtn = 0
print 'ABC Backed up.'
ELSE
print 'ABC Backup Error.'
You can take out the updatestats and the error checking for pure simple.
January 25, 2007 at 2:37 am
Or this nice SP? I created this in master. I use a scheduled VB script to handle old file removal. Works for me.
Chris
=========
/*
usp_BackupDB
Backups up a database
Creates a .bak file in the given path with the name of the database and a datestamp
*/
CREATE procedure usp_BackupDB
(
@INP_DatabaseName varchar(100), --name of the db
@INP_BackupPath varchar(160) = 'd:\ms-sql\projects\backup\' --where to store backup files (default value)
)
as
--Get the ISO-formatted date (for use in BAK file suffixes)
declare @ISONow char(8)
set @ISONow = (select convert(char(10),getdate(),112))
declare @BackupPath varchar(260)
set @BackupPath = @INP_BackupPath + @INP_DatabaseName + '_' + @ISONow + '.bak'
print 'Creating backup device: ' + @BackupPath
--Start by adding a dump device
exec sp_addumpdevice 'disk', 'TempDevice', @BackupPath
--Do the backup
BACKUP DATABASE @INP_DatabaseName TO TempDevice
--Drop the device again
exec sp_dropdevice 'TempDevice'
GO
January 25, 2007 at 11:36 am
we only keep one backup on disk, the rest are on tape. I find scheduled backups to a backup devices the way to go for this.
March 19, 2008 at 5:58 pm
I used the code below to allow my payroll specialist to manually back up a database before she performs a payroll run. It works great but I was wanting to do one other thing to it...
1. Is there a way I can turn this script into a shortcut on the desktop of my payroll specialist? I'm ultimately trying to make this a simple one-step back up for her. If it is a short cut, will she still need to have SQL client tools installed on her system? Currently I'm using SQL Server 2000 and the client computer is using Windows XP Pro sp2.
Code currently being used:
DECLARE @filename NVARCHAR ( 500 )
, @hour NCHAR ( 2 )
, @min-2 NCHAR ( 2 )
IF DATEPART(hh, GETDATE()) < 10
SET @hour = '0' + CONVERT(NVARCHAR, DATEPART(hh, GETDATE()))
ELSE
SET @hour = CONVERT(NVARCHAR, DATEPART(hh, GETDATE()))
IF DATEPART(mi, GETDATE()) < 10
SET @min-2 = '0' + CONVERT(NVARCHAR, DATEPART(mi, GETDATE()))
ELSE
SET @min-2 = CONVERT(NVARCHAR, DATEPART(mi, GETDATE()))
SET @filename = '\\thrha-sql\apps\DatabaseBU\v32Live' + CONVERT(NVARCHAR, GETDATE(), 112) + @hour + @min-2 + '.BAK'
BACKUP DATABASE v32Live TO DISK = @filename
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply