SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Should I backup more than the database(s) on a server?


Should I backup more than the database(s) on a server?

Author
Message
Sqlraider
Sqlraider
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2670 Visits: 2311
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.
Jared Karney
Jared Karney
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12662 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Sqlraider
Sqlraider
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2670 Visits: 2311
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
Jared Karney
Jared Karney
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12662 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Shawn Melton
Shawn Melton
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5575 Visits: 3536
This can depend on how you document your systems. You do document them right? :-D

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
Twitter: @wsmelton
Blog: blog.wsmelton.info
Sqlraider
Sqlraider
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2670 Visits: 2311
Shawn Melton (3/22/2013)
This can depend on how you document your systems. You do document them right? :-D

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.
David Webb-CDS
David Webb-CDS
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3826 Visits: 8586
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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93489 Visits: 33009
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
kevaburg
kevaburg
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4191 Visits: 1025
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.
Sqlraider
Sqlraider
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2670 Visits: 2311
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search