March 22, 2013 at 1:10 pm
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.
March 22, 2013 at 1:30 pm
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.
Jared
CE - Microsoft
March 22, 2013 at 1:40 pm
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
March 22, 2013 at 1:43 pm
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.
Jared
CE - Microsoft
March 22, 2013 at 1:48 pm
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
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 22, 2013 at 2:41 pm
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.
March 22, 2013 at 3:00 pm
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.
March 23, 2013 at 4:26 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2013 at 7:44 am
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.
March 25, 2013 at 8:48 am
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?
March 25, 2013 at 9:00 am
Grant Fritchey (3/23/2013)
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.
What steps or how do I go about creating a "source control management system"? I have no buget to purchase one.
March 25, 2013 at 10:27 am
Sqlraider (3/25/2013)
Grant Fritchey (3/23/2013)
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.What steps or how do I go about creating a "source control management system"? I have no buget to purchase one.
There are free ones online such as SVN. If you have an MSDN license, you already have on in Team Foundation Services.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2013 at 10:58 am
Grant Fritchey (3/25/2013)
Sqlraider (3/25/2013)
Grant Fritchey (3/23/2013)
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.What steps or how do I go about creating a "source control management system"? I have no buget to purchase one.
There are free ones online such as SVN. If you have an MSDN license, you already have on in Team Foundation Services.
Thanks, I'll look into those.
March 25, 2013 at 11:30 am
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.
I would be careful about recovering SSIS packages from MSDB. If that system crashed and you build a new system that is not the exact same version you could have problems.
Besides, there are several source control systems available where you really should be managing these kinds of objects. SSIS packages are basically code - so a code repository would be the best solution, in my opinion.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply