January 10, 2013 at 11:54 am
So, to put it simply, I suddenly found myself as unofficial DBA. I've been working with SQL in a casual test environment on my own at home for about a year.
And now I'm officially in charge of our server, The One Ring and it's backup, Gollum.
I know there isn't a huge difference between 2005 and 2008 (at home is 2k8, work is 2k5), but I've been going through the files to see what's basically going on with our server here.
Now, maybe I'm not looking in the right place, but I can't seem to find any backup schedules. I checked Config Manager, I checked Task Scheduler, I even checked to see if maybe there was just a plain Word doc somewhere.
I'm also pretty concerned because, on our backup server, there's a bunch of folders indicating that, until recently, backups were being done daily (diffs and fulls weekly on Sundays). But there's no record showing backups for this month, so to be safe, I went ahead and created a full backup.
My question is, should I continue in the pattern that was set? (Diffs daily and fulls weekly). It seems about how I'd do it, but I feel REALLY inexperienced.
I definitely want to set up Jobs and arrange so the backups are done automatically. I also know we need to purchase a new battery; the Event Viewer is FULL of error messages to do with our battery.
The problem is, its expensive (average 700 from what Googling I did) and I'm not sure if they'll sanction it.
Anyone have any basic advice for a (very) new DBA?
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
January 10, 2013 at 12:19 pm
Jamie Scharbrough (1/10/2013)
Now, maybe I'm not looking in the right place, but I can't seem to find any backup schedules. I checked Config Manager, I checked Task Scheduler, I even checked to see if maybe there was just a plain Word doc somewhere.
In the SQL Server itself (when you connect to the engine), there's a node for SQL Agent. Check there, that's where most SQL tasks are scheduled from.
My question is, should I continue in the pattern that was set? (Diffs daily and fulls weekly). It seems about how I'd do it, but I feel REALLY inexperienced.
Normally, yes. This is a solid method of implementing backups. You'll also want to check the recovery model of your databases. If they're in anything but simple, you'll want to check your log backups as well.
Anyone have any basic advice for a (very) new DBA?
Over on the left side of the site, there's a section for books. There's a number of free ebooks in there. Near the top, Troubleshooting SQL Server, a guide for the accidental DBA is basically your #1 read. It'll help you get the keywords and general ideas you need to help you feel more confident in your own research.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 10, 2013 at 12:22 pm
If you're talking about a RAID array write cache backup battery, then you either must replace it or must disable the write cache or you are risking a corrupt database.
Start with this
As for backup strategies, don't start with the backups, start with the business requirements for maximum data loss. If the business thinks that there will be no more than 15 minutes of data lost in any disaster (and losing a few hours would be catastrophic), then a strategy of weekly fulls and daily diffs could result in unpleasant surprises and job losses (probably yours)
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
January 10, 2013 at 12:28 pm
Sounds like you're off to a good start.
A couple of things you can do:
Right-click a database in SSMS (Management Studio), then Report -> Standard Reports -> Backup and Restore Events. That'll give you the backup/restore history of a particular database.
Get familiar with the system tables in database msdb. It's under Sytem Database in SSMS. The ones you want (for this subject) all start with "backup". They'll look cryptic at first, but once you read up on them, they'll tell you everything about backup history: where the backup files are going, when/if the databases are being backed up, what types of backups, etc. There are lots of good scripts available online for querying details out of these tables.
Get familiar with testing Restore on the backups. Always remember, you don't need a "backup plan", you need a "restore plan". Backups are useless if they can't be restored effectively and rapidly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 10, 2013 at 12:50 pm
GilaMonster (1/10/2013)
If you're talking about a RAID array write cache backup battery, then you either must replace it or must disable the write cache or you are risking a corrupt database.Start with this
As for backup strategies, don't start with the backups, start with the business requirements for maximum data loss. If the business thinks that there will be no more than 15 minutes of data lost in any disaster (and losing a few hours would be catastrophic), then a strategy of weekly fulls and daily diffs could result in unpleasant surprises and job losses (probably yours)
You're right about the backups; I'll doublecheck.
As for the battery, we currently have a APC Smart-UPS 1500VA USB & Serial 230V and I'm not sure if its a RAID array or not.
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
January 10, 2013 at 12:54 pm
That's a UPS, not a storage device.
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
January 10, 2013 at 1:04 pm
That's what I thought but wasn't sure.
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
January 11, 2013 at 5:11 am
Okay so I just got in and I realized...there's no SSMS on this server. Since our original IT guy was overseas, it may be located on his machine as I know he remote logged in to set up backups and such.
Should I download SSMS (and by extension SQL Agent), or is that going to screw something up?
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
January 11, 2013 at 5:22 am
You can use SSMS on your workstation. Personally I don't feel that user apps should be installed on a server.
(no, it can't be downloaded, you'd install SSMS from the SQL installation disks/ISO). Also, SQL Agent is not part of SSMS. It's a SQL Server service, it's installed with the database engine.
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
January 11, 2013 at 5:25 am
Sorry, not downloaded, I used the wrong word.
Yeah that's definitely doable (installing it on my workstation). I'm so used to everything being in the same place that I kind of panicked when I realized it wasn't there.
EDIT: Oh good grief. The original installation disks are overseas. And there would be some serious other issues in trying to get them. (The long and the short of it is, our parent company has disassociated from us and legally we are no longer allowed to work with them. It's complicated.)
Is it possible to download the 6 month freebie SSMS that Microsoft offers, or would there be issues in connecting them?
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
January 11, 2013 at 5:41 am
Jamie Scharbrough (1/11/2013)
Sorry, not downloaded, I used the wrong word.Yeah that's definitely doable (installing it on my workstation). I'm so used to everything being in the same place that I kind of panicked when I realized it wasn't there.
EDIT: Oh good grief. The original installation disks are overseas. And there would be some serious other issues in trying to get them. (The long and the short of it is, our parent company has disassociated from us and legally we are no longer allowed to work with them. It's complicated.)
Is it possible to download the 6 month freebie SSMS that Microsoft offers, or would there be issues in connecting them?
I am not sure if a SQL 2005 Evaluation download is still going to be around, but you can download the 180-day Evaluation copy of SQL 2012 from Microsoft's website and that will get you a copy of SSMS. SSMS 2012 can connect to SQL 2005 instances. You may run into some quirks trying to manage some of the subsytems (e.g. replication) but Microsoft touts that SSMS 2012 can manage SQL 2005 instances. This would only be a stopgap until you got the SQL 2005 media and could install SSMS 2005, or upgraded to SQL 2012 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 11, 2013 at 5:47 am
Okay so I emailed the original IT guy, asking him to doublecheck if the disks were over there or not.
...It turns out he'd been using Arcserve the whole time and he has no idea how SQL 2005 got onto our server.
I have no clue how to use Arcserve and I'd really feel better using SQL. However, we're only running one instance of SQL, our data backups are relatively small, and I have no idea if we'd be willing to fork out the cash for a Enterprise edition of 2K12 or 2K8.
I know I can download the trial copy and use it, but the question is, should I? And essentially start from scratch?
I want to personally, as this will give me great hands on experience and I can begin anew.
What would you guys do in my position (beside crack open the vodka at 8 am and keep going until you can't stand up)?
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
January 11, 2013 at 5:56 am
JESDBA (1/11/2013)
Okay so I emailed the original IT guy, asking him to doublecheck if the disks were over there or not....It turns out he'd been using Arcserve the whole time and he has no idea how SQL 2005 got onto our server.
I have no clue how to use Arcserve and I'd really feel better using SQL. However, we're only running one instance of SQL, our data backups are relatively small, and I have no idea if we'd be willing to fork out the cash for a Enterprise edition of 2K12 or 2K8.
I know I can download the trial copy and use it, but the question is, should I? And essentially start from scratch?
I want to personally, as this will give me great hands on experience and I can begin anew.
What would you guys do in my position (beside crack open the vodka at 8 am and keep going until you can't stand up)?
If ArcServe is not "SQL Server"-aware then you probably do not have valid SQL database backups. I am not too familiar with the product but from a couple minutes on their site it looks like "SQL Server"-awareness is optional. I would say, start taking native SQL Server backups immediately. I would probably do that regardless since recovering a database from a file system backup is not always going to give you what you need.
Installing SSMS locally so you can manage the 2005 instance is not necessarily starting from scratch. I do not see it that way. The server will still be 2005. SSMS 2012 is slightly different with some of the keyboard shortcuts, but not drastically different than SSMS 2005 so much that you will not recognize it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 11, 2013 at 6:24 am
I'm currently talking to a tech friend of mine and browsing Arc's website.
It looks like we've just been using it to backup our FILES, not our database. Sorry, it's early, I have the dumb (and the inexperience).
I'll be taking backups of SQL and downloading SSMS on my workstation.
Thanks for the advice!
_____________________________________________________________________
-Jamie Scharbrough
MCTS: SQL 2008R2
January 11, 2013 at 7:13 am
Here is a quick script to see the last backup for each database on the server. Gives file name (so you know where they are going), whether the database is a current database (could have been dropped in the past), and the recovery model.
Should give you an idea of the basics.
/* ========================================================================================================================== */
/* Get the very last backup set (Database and all Tranlog backups) for each database in instance */
/* ========================================================================================================================== */
CREATE TABLE #dummybackups
(
DBNamevarchar(300),
LastBackupDatedatetime
)
CREATE TABLE #dummydb
(
DBNvarchar(300),
RecModvarchar(50)
)
INSERT INTO #dummydb
SELECT name, recovery_model_desc FROM master.sys.databases
WHERE name NOT IN ('tempdb') AND is_read_only = 0 AND state = 0
INSERT INTO #dummybackups
SELECT DISTINCT database_name, NULL FROM msdb..backupset ORDER BY database_name
--Get last full backup for each database and put in dummy table for that database
UPDATE #dummybackups
SET LastBackupDate = (SELECT TOP 1 backup_start_date FROM msdb..backupset WHERE database_name = DBName AND type = 'D' ORDER BY backup_start_date DESC)
SELECT S.database_name,
CONVERT(varchar(20),'') AS CurrentDB,
S.recovery_model, S.user_name,
S.backup_start_date,
CASE WHEN CONVERT(varchar(200),GETDATE(),101) = CONVERT(varchar(200),S.backup_start_date,101) THEN '' ELSE ' * ' END AS NotFromToday,
S.type,
CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0) AS backup_size_MB, M.physical_device_name
INTO #dummybackups2
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.backup_start_date >= (SELECT LastBackupDate FROM #dummybackups WHERE database_name = DBName) AND (S.recovery_model IS NOT NULL)
--AND S.database_name = 'somedbname'
ORDER by S.database_name, backup_start_date DESC
UPDATE #dummybackups2
SET CurrentDB = 'NO'
WHERE NOT EXISTS(SELECT name FROM master.sys.databases WHERE master.sys.databases.name = database_name)
-- insert into final resultset the databases that are in the instance, but don't have a record in the backups
INSERT INTO #dummybackups2
SELECT DBN,'',RecMod,'',NULL,'','',0,NULL FROM #dummydb WHERE DBN NOT IN (SELECT database_name FROM #dummybackups2)
SELECT *
,ISNULL(SUBSTRING(physical_device_name,1,LEN(physical_device_name) - PATINDEX('%\%',REVERSE(physical_device_name)) + 1),NULL) AS PathName
,ISNULL(SUBSTRING(physical_device_name,LEN(physical_device_name) - PATINDEX('%\%',REVERSE(physical_device_name)) + 2,PATINDEX('%\%',REVERSE(physical_device_name))-1),NULL) AS FileNm
FROM #dummybackups2 ORDER BY database_name, backup_start_date DESC
DROP TABLE #dummybackups
DROP TABLE #dummybackups2
DROP TABLE #dummydb
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply