October 29, 2011 at 8:25 am
I need to implement a backup plan for my database. What's the easiest way to implement this?
Based on what I've read a simple backup should be done frequently and a full backup should be done during off-peak times. A simple backup is essentially an incremental backup from what I understand.
Is this generally done through a new maintenance plan through SSMS? I just glanced at this but I was slightly overwhelmed with options and didn't see a destination selector. Also I wasn't clear on how to do a simple backup on an hourly basis.
Sometimes I manually backup my database through SSMS by selecting Tasks > Backup through SSMS. I assume this is equivalent to a full backup. Then I'll restore the database through SSMS by selecting Tasks > Restore.
With an SSMS maintenance plan do hourly backup files get set with a timestamp? For example, if I needed a backup of the database from 2-3pm yesterday would SSMS set this info to the filename of the bak? Or is this info maintained internally in the bak file and can I simply view this info through SSMS?
Sorry for so many questions but this is my very first attempt to implement an automated backup plan in SS....
October 29, 2011 at 8:58 am
sqlguy-736318 (10/29/2011)
Based on what I've read a simple backup should be done frequently and a full backup should be done during off-peak times. A simple backup is essentially an incremental backup from what I understand.
SQL has nothing that's called a simple backup, and it has no incremental backups. I suspect you mean log backups, which aren't particuarly simple at all.
Good place to start: http://www.simple-talk.com/sql/database-administration/sql-server-backup-and-restore-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2011 at 9:13 am
GilaMonster (10/29/2011)
sqlguy-736318 (10/29/2011)
Based on what I've read a simple backup should be done frequently and a full backup should be done during off-peak times. A simple backup is essentially an incremental backup from what I understand.SQL has nothing that's called a simple backup, and it has no incremental backups. I suspect you mean log backups, which aren't particuarly simple at all.
Good place to start: http://www.simple-talk.com/sql/database-administration/sql-server-backup-and-restore-for-the-accidental-dba/
Sorry Gail - I should have said "simple recovery model" and "full recovery model"
October 29, 2011 at 9:15 am
Definitely start by reading the article Gail linked.
It's pretty important to have a basic understanding of how the SQL Server transaction log works, how the different recovery models work, etc. in order to avoid shooting yourself in the foot. SQL Server backups are a significantly different animal from regular file system backups.
Once you're read through that "Accidental DBA" article, it would probably be a good idea to go through the MSDN backup/restore articles found here: http://msdn.microsoft.com/en-us/library/ms187048.aspx.
October 29, 2011 at 9:21 am
And here's another article, a little more comprehensive. http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
Simple and full recovery models affect whether you'll be able to restore the DB to point of failure, or just the last full backup, so it's important to understand which is required for the usage of the database. This may help to understand recovery models: http://www.sqlservercentral.com/articles/Administration/75461/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 29, 2011 at 6:48 pm
I read the first article posted to this thread and it did seem like a good starter thread.
First things first, I'm going to attempt to create an SSMS maintenance plan to implement a full daily backup. That seems like a good first step since it's fundamental....
October 30, 2011 at 8:14 pm
I've created a Maintenance Plan for my database. Everything looks correct. It should be scheduled to run every hour and save the backups to my C:\PROD_DB_BAK\ directory. However, for some reason the backup does not appear to be getting triggered. Or maybe it's failing somewhere else? Is there a log file I can view for this information? I've pasted the T/SQL generated for the Maintenance Plan below. Please take a glance at it and let me know if something doesn't look right or if you have any ideas about where it might be failing:
============================================================================
BACKUP DATABASE [ExceptionManagement] TO DISK = N'C:\PROD_DB_BAK\ExceptionManagement_backup_2011_10_30_210500_5312500.bak' WITH NOFORMAT, NOINIT, NAME = N'ExceptionManagement_backup_2011_10_30_210500_5312500', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'ExceptionManagement' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'ExceptionManagement' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''ExceptionManagement'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'C:\PROD_DB_BAK\ExceptionManagement_backup_2011_10_30_210500_5312500.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
October 31, 2011 at 2:10 am
SQLAgent running?
Hourly full backups? That's a little unusual...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 3:15 am
You can right-click on the maintenance plan and select "View History" to verify if it has been running (and if so, if it failed). Do the same with the SQL Server Agent Job(s) that should have been automatically created to implement the schedule you chose. Make sure SQL Server Agent is actually running (right-click on SQL Server Agent and click "Start").
October 31, 2011 at 3:24 am
Knocked this up in a couple of minutes. It's based off what I remember from my previous job, in my new job I'm strictly a developer so I don't perform any admin tasks which means back-ups have nothing to do with me.
DECLARE @SQL VARCHAR(MAX)
SELECT @SQL = 'BACKUP LOG [' + NAME + '] TO DISK = N''D:\BackUps\' + NAME +
'TransactionLogBackup' + REPLACE(CONVERT(VARCHAR(12), GETDATE(), 102) + '_' +
CONVERT(VARCHAR(8), GETDATE(), 108), ':', '.') + '.bak''
WITH NOFORMAT, NOINIT, NAME = N''' + NAME + '-Transaction Log Backup' +
CONVERT(VARCHAR(12), GETDATE(), 102) + CONVERT(VARCHAR(8), GETDATE(), 108) + ''',
SKIP, NOREWIND, NOUNLOAD, STATS = 10'
FROM sys.databases
WHERE recovery_model_desc = 'FULL' AND NAME <> 'model'
--==Back up transaction log==--
IF @SQL IS NOT NULL
BEGIN
EXEC (@SQL)
END
--==Clean Up==--
SET @SQL = NULL
SELECT @SQL = 'BACKUP DATABASE [' + NAME + '] TO DISK = N''D:\BackUps\' + NAME +
'DatabaseBackup' + REPLACE(CONVERT(VARCHAR(12), GETDATE(), 102) + '_' +
CONVERT(VARCHAR(8), GETDATE(), 108), ':', '.') + '.bak'' WITH ' +
CASE WHEN Datepart(dw, getdate()) <> 2
THEN 'DIFFERENTIAL,'
ELSE '' END + ' NOFORMAT, NOINIT,
NAME = N''' + NAME + '-' + CASE WHEN Datepart(dw, getdate()) <> 2
THEN 'Differential'
ELSE 'Full' END + ' Database Backup' +
CONVERT(VARCHAR(12), GETDATE(), 102) + ''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
FROM sys.databases
WHERE recovery_model_desc = 'FULL' AND NAME <> 'model'
--Only run database back-up at 20:00. This can be adjusted by adjusting the number after the "hh"
AND GETDATE() = DATEADD(hh,20,DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))
--==Back up database==--
IF @SQL IS NOT NULL
BEGIN
EXEC (@SQL)
END
The idea is to keep this code in a SQLAgent job, which means you're able to set logging on and keep an eye on it.
In my previous job, we also had a second step that only started if the first step failed. In the second step was an e-mail that was sent to an address that had SMS forwarding, allowing the IT Director to know when something had gone wrong and then contact either the DBA or one of the developers.
October 31, 2011 at 10:27 am
This is the error logged in the Maintenance Plan job history:
The job failed. Unable to determine if the owner (MYDOMAIN\mynwuid) of job MaintenancePlan.Subplan_1 has server access (reason: Could not obtain information about Windows NT group/user 'MYDOMAIN\mynwuid', error code 0x54b. [SQLSTATE 42000] (Error 15404)).
I'm sure if you have any experience with Maintenance Plans then you probably know what the problem is here and how to fix it....
October 31, 2011 at 11:20 am
What version of SQL Server is this?
Who is set as the owner of the SQL Agent job that is failing?
Check this KB article: http://support.microsoft.com/kb/241643
October 31, 2011 at 1:20 pm
Is a domain controller online and accessible?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 31, 2011 at 2:51 pm
I usually set all job owners as SA just to avoid these issues.
November 1, 2011 at 10:26 am
Setting owner to SA fixed it for me - thanks!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply