December 16, 2010 at 7:59 am
skcadavre (12/16/2010)
Backups used to be made regularly, but due to disk space it was turned off (not by me).In the end, the tape disk was there, so I was able to recover everything except that day of transactions. I then wrote an SSIS package to pick up the post from that day and re-upload to a new table, then updated my original table with the correct parts of that. All in all, we lost nothing (except me, since I was at work from 8:30am to 10:30pm).
I'm hoping that management agree that we could do with some more space to keep backups - also have been asking since I started here for a development environment but so far it has been a non-starter. Have a meeting later on today to discuss what happened and how we could have prevented it, so should be fun.
Post mortems are a great way for an IT team to grow. Just remember to keep it to the facts and what your initial plans are (reducing perms) and stress the need for a dev environment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 17, 2010 at 1:15 am
Transaction log records all changes in db (insert,update,delete, create index, etc, but NOT select's for example) and you can roll it back or forward in time IF you are in full recovery mode and have continuity (did not truncate transaction log) from your last full/diff backup of db.
It does not matter that you did not backup transaction log (but it does have it's conseqeunces, e.g. log will grow and think what will you have in case of disk failure and loosing the tran log file).
If you have full backup and did not truncate the trunsaction log since the last full od differential backup, you can restore data point-in-time prior to UPDATE to a new database. Then you can take the data from this newly created db and restore that table data to original db with plain SQL.
I did that few time (for testing), and it works. If you need a script, let me know.
Transaction log will grow until you backup it, or trucate it (truncate will break continuity from your last full/diff backup and you loose point-in-time recovery).
On some db's I have full backup once a week, differential backup once a day, and transaction log backup every 15 minutes, just to give you a example numbers. Differential backups save much space.
Good luck!
December 17, 2010 at 3:38 am
Ninja's_RGR'us (12/16/2010)
You can download sql express and install it on your pc. That's always a good start. If that's a no go because the DB is over 4 GB, then you can install a trial version at first while you ask them for 60$ to buy a dev version to install.
SQL 2008 R2 Express has upped the limit to 10Gb.
December 17, 2010 at 6:09 am
paul.knibbs (12/17/2010)
Ninja's_RGR'us (12/16/2010)
You can download sql express and install it on your pc. That's always a good start. If that's a no go because the DB is over 4 GB, then you can install a trial version at first while you ask them for 60$ to buy a dev version to install.SQL 2008 R2 Express has upped the limit to 10Gb.
Thanks for the info... that would be enough for almost all my mid-size clients (300+ employees).
December 21, 2010 at 7:12 am
skcadavre (12/16/2010)
Backups used to be made regularly, but due to disk space it was turned off (not by me).In the end, the tape disk was there, so I was able to recover everything except that day of transactions. I then wrote an SSIS package to pick up the post from that day and re-upload to a new table, then updated my original table with the correct parts of that.
Can you explain that more? How were you able to use SSIS to recover actions taken? Did you have the day's modifications stored in a flat file or Excel or something?
Thanks,
Rich
December 22, 2010 at 10:45 am
You can save quite a bit of space even without R2 compression. Set the folder where you backups are going as compressed. Then any file put in there will also be compressed. It is not unusual to save up to 50% by just doing this compression.
December 22, 2010 at 2:03 pm
royce.bacon (12/22/2010)
You can save quite a bit of space even without R2 compression. Set the folder where you backups are going as compressed. Then any file put in there will also be compressed. It is not unusual to save up to 50% by just doing this compression.
Using NTFS compression like this is generally not recommended. Depending on the disk's cluster size it may not even work at all.
This site provides a more detailed discourse on its limitations:
http://blogs.msdn.com/b/ntdebugging/archive/2008/05/20/understanding-ntfs-compression.aspx
December 22, 2010 at 3:08 pm
Ninja's_RGR'us (12/17/2010)
paul.knibbs (12/17/2010)
Ninja's_RGR'us (12/16/2010)
You can download sql express and install it on your pc. That's always a good start. If that's a no go because the DB is over 4 GB, then you can install a trial version at first while you ask them for 60$ to buy a dev version to install.SQL 2008 R2 Express has upped the limit to 10Gb.
Thanks for the info... that would be enough for almost all my mid-size clients (300+ employees).
Except SQLExpress does not have SQL Agent for automated backups. Unless you don't mind doing manual backups at the end of every day, at some point you'll find yourself in the situation described at the start of this thread.
December 23, 2010 at 1:29 am
dan-572483 (12/22/2010)
Except SQLExpress does not have SQL Agent for automated backups. Unless you don't mind doing manual backups at the end of every day, at some point you'll find yourself in the situation described at the start of this thread.
Windows Scheduler.
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
December 23, 2010 at 6:09 am
GilaMonster is correct--it's fairly trivial to set up a SQL script that can be called via SQLCMD in a scheduled task to do backups. We have a SQL Express install containing databases for things like our BES installation which we back up in this way.
December 30, 2010 at 9:50 am
rmechaber (12/21/2010)
Can you explain that more? How were you able to use SSIS to recover actions taken? Did you have the day's modifications stored in a flat file or Excel or something?Thanks,
Rich
Sorry if I wasn't clear (also for the late reply, just spotted this!).
Basically, the table that was nuked contained all of our post records, which during normal process are scanned into the system as PDFs then uploaded using a .Net application which calls an SSIS package. I simply wrote an SSIS package to grab only the post that was contained in the folder as a PDF but not as a record in the table, then after some validation checks placed the post back into the nuked table.
Hope that makes more sense than I think it does.
December 31, 2010 at 2:55 am
Notice the other limitations of SQL Express: this edition will only use 1 processor and 1 GB of server memory. Of course you can run multiple instances and spread your databases among them. But if you take the additional maintanance costs into account, I doubt whether it really pays off to use the free version. I know how hard it can be to convince the management to spend some money on a decent version of SQL Server, because they don't see the added value of good maintenance facilities. I hope this incident will help you to get things done the right way ...
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply