Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Should I backup more than the database(s) on a server? Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 1:10 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:01 PM
Points: 1,296, Visits: 1,813
I'm a rookie when it comes to backing up a server.

I've been able to successfully backup and restore database(s) on SQL Server 2008. But I'm wondering if I should be backing up more than just the database(s). What I mean by this is what if something happens to the server or someone screws up a SSIS package (.dtsx) or screws up a SQL Agent job or I need to recover yesterdays version of some T-SQL code? How would I be able to restore these to an older version?

Should I be backing these items to another location off the server or am I over thinking things?

I read where some of you forum posters say "I'll be putting that in my toolbox". Where do you put that and how do you take your toolbox from one job to the next?

If yes, is there a script or proc I could use as a guide?

Thanks.
Post #1434491
Posted Friday, March 22, 2013 1:30 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:50 PM
Points: 2,691, Visits: 3,374
Our SE team takes snapshots/images of the server itself for this purpose. Although, we store our SSIS packages in MSDB, so those are protected by MSDB backups.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1434499
Posted Friday, March 22, 2013 1:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:01 PM
Points: 1,296, Visits: 1,813
SQLKnowItAll (3/22/2013)
Our SE team takes snapshots/images of the server itself for this purpose. Although, we store our SSIS packages in MSDB, so those are protected by MSDB backups.


Thanks, I was unaware of that about SSIS packages.

Do you use the snapshot/images for the Analysis Services msmdsrv.ini for recovery purposes also?

Sqlraider
Post #1434502
Posted Friday, March 22, 2013 1:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:50 PM
Points: 2,691, Visits: 3,374
Not specifically... but sure it will work. As a DBA, I design the backup strategy for the Databases and such. I work with the SE team to figure out how to get a server (OS and programs) recovered back to a working state in case of disaster. Any config files I need will all be backed up through snapshots and also through our SAN redundancy.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1434505
Posted Friday, March 22, 2013 1:48 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:11 AM
Points: 840, Visits: 2,299
This can depend on how you document your systems. You do document them right?

I support a branch of the military, and the organization I support right now has requirements for configuration management. You basically identify each configuration item (CI) in an instance of SQL Server and then put them under version control. Version control can be done with different products, some open source and some cost money (e.g. Team Foundation Server).

I have only been with my current organization for about a year now and previous to my arrival they had nothing on the production SQL Server under proper configuration management. I am fixing that by putting the production database into a SQL Server Data Tools project, and then checking that into our configuration library. I am then taking any SSRS and SSIS project I create into the same library. So the version I end up deploying to production I will have in the configuration library. The SQL Agent jobs I have scripted out and loaded into a spreadsheet that just has some more details about each job.

The "toolbox" thing, I have most of them in my Google account online. I have heard some folks will use DropBox. I don't really tend to go out of my way to much on this because most of the scripts in my toolbox I have gotten from SSC or other SQL Community sites (SQLSkills, Brent Ozar, etc.).


Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1434506
Posted Friday, March 22, 2013 2:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:01 PM
Points: 1,296, Visits: 1,813
Shawn Melton (3/22/2013)
This can depend on how you document your systems. You do document them right?

I support a branch of the military, and the organization I support right now has requirements for configuration management. You basically identify each configuration item (CI) in an instance of SQL Server and then put them under version control. Version control can be done with different products, some open source and some cost money (e.g. Team Foundation Server).

I have only been with my current organization for about a year now and previous to my arrival they had nothing on the production SQL Server under proper configuration management. I am fixing that by putting the production database into a SQL Server Data Tools project, and then checking that into our configuration library. I am then taking any SSRS and SSIS project I create into the same library. So the version I end up deploying to production I will have in the configuration library. The SQL Agent jobs I have scripted out and loaded into a spreadsheet that just has some more details about each job.

The "toolbox" thing, I have most of them in my Google account online. I have heard some folks will use DropBox. I don't really tend to go out of my way to much on this because most of the scripts in my toolbox I have gotten from SSC or other SQL Community sites (SQLSkills, Brent Ozar, etc.).


I'm a one-person team when it comes to the data warehouse server. I'm the designer, developer, and the DBA. Since I have no experience in setting up a configuration library, I have no check-in check-out process in place. There is no money to purchase any tools and everything I've accumilated has come from SSC or other SQL Community sites. I'm also learning SSIS & SSAS on the fly, so I was looking for an 'easy' way (if there is such a thing) to restore any packages, code, SQL agent jobs in case I mess up a working version. That way I have something to fall back on.

Thanks for your input.
Post #1434514
Posted Friday, March 22, 2013 3:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:55 PM
Points: 896, Visits: 7,087
Just for ease of use and potentially quicker response, I tend to script out things like logins, database users, procs, triggers, and views that I might need to restore quickly. If the objects are kept in a source control system somewhere, this become less critical for the non-login related stuff. But not all of my clients lock production down appropriately, so sometimes they 'accidentally lose' important things that are needed back very quickly.



And then again, I might be wrong ...
David Webb
Post #1434522
Posted Saturday, March 23, 2013 4:26 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
In addition to backups, if you're developing the databases, I'd get the source for the structures and code into a source control management system. I'm pretty sure there ways to do that with SSIS packages too.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1434587
Posted Monday, March 25, 2013 7:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
Alot depends on a lot of things. For example how much data can you afford to lose, or better worded, is allowable in the worst case scenario? How big is your database? Is it practical to perform a full backup once a day or would once a week be more apprpriate together with differential backups and transaction logs?

These are questions you need to go through your documentation and company policies for in order to find the answers.

The system databases don't need much attention except when:

The MASTER database contains changed database structures.
The MSDB database has new jobs, packages etc;
The MODEL database has changed templates for database creation, required when restarting the instance so TEMPDB can be rebuilt.
TEMPDB is rebuilt every instance restart/start and does not need to be backed up.
Post #1434915
Posted Monday, March 25, 2013 8:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:01 PM
Points: 1,296, Visits: 1,813
David Webb-200187 (3/22/2013)
Just for ease of use and potentially quicker response, I tend to script out things like logins, database users, procs, triggers, and views that I might need to restore quickly. If the objects are kept in a source control system somewhere, this become less critical for the non-login related stuff. But not all of my clients lock production down appropriately, so sometimes they 'accidentally lose' important things that are needed back very quickly.


Would you be willing to share your scripts? Or are you talking about creating them by right clicking, script out, create to, new query editor window from MSSMS?
Post #1434952
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse