September 5, 2008 at 7:55 am
I found a simple script that will automatically backup all databases on the server which I've posted below:
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 = 'C:\Backup\'
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
What I need help on is the coding to have the file name changed to this convention: DbNameMMDDYYYYTTTT.bak (TTTT meaning military time) and also to automatically zip the .bak files to save on space. If anyone can help, that would be great.
September 5, 2008 at 8:22 pm
Here you go:
Set @filedate = Replace(Convert(varchar(10), getdate(), 101), '/', '') +
Replace(Convert(varchar(6), getdate(), 114), ':', '')
The first convert gets MMDDYYYY and the second gets HHMM
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 8, 2008 at 5:51 am
You forgot to backup MASTER, MSDB and MODEL 😀 (also important)
If you want to backup these databases also, you could strip the script to something like this:
exec sp_msforeachdb 'if ''?''<>''tempdb'' backup database ? to disk=''c:\backup\?.bak'''
Wilfred
The best things in life are the simple things
September 8, 2008 at 7:35 am
Thanks. But what about compressing the .bak files into .zip files and deleting the .bak back files to save on space? Any ideas?
September 8, 2008 at 7:49 am
You could compress them, but you'd need to either Sp_OACREATE to grab an object handle to some zip utility or shell out (xp_cmdshell) to do the command line call.
You might be better off writing a VB script or Powershell script of some sort that would run from the OS using SQL Agent or the Windows Scheduled Tasks item.
September 8, 2008 at 8:32 am
Thanks. But what about compressing the .bak files into .zip files and deleting the .bak back files to save on space? Any ideas?
Try Idera SQLSafe freeware!
Wilfred
The best things in life are the simple things
September 24, 2008 at 4:05 pm
I was trying to download Idera: SqlSafe but it said 'Trial' . Are you sure is that full operational?
Anyway that is the great software for someone who got SQL 2005 Express version. Becasue I found lots of trouble with Agent Job in Express Version.
Leo
September 24, 2008 at 11:41 pm
Make sure you download the freeware version.
SQL Express doesn't have a job scheduler. SQLSafe comes with an commandline utility and several stored procedures. So you have the choice of scheduling a SQLAgent job, or a windows scheduled task (in case you have SQL Express).
Wilfred
The best things in life are the simple things
September 25, 2008 at 10:34 am
For backup and clean up, you can use the built in 2005/2008 Maintenance Plan/Wizard
You can specify WHICH DB's to backup, and how long to keep them
As for compression, 3rd party do it better like LiteSpeed or SQLBackup (RedGate)
or SQL 2008 Enterprise has built in backup compression
SQL Safe Freeware link
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply