Francisco H Tapia
my.lists at verizon.net
Mon Feb 24 09:51:15 CST 2003
Found out some GREAT NEWS!, Hope you all had a good weekend... it TURNS out that when you don't auto-shrink your log files, meaning you set a fixed lenght and then auto-backup whenever your log file hits 60% there are some outstanding benefits.... One of which you NEVER loose any data... When you go and choose to restore your database and you have your full backup selected (hopefully from last night) and your logs from during the day, simply plug in a point in time to restore to and viola!, 15 minutes before I was alerted was the time I chose and it restored the entire db up until that point, I kept playing with it pushing less and less time until I found the point (minute) in time that it was killed.. I did this of course NOT on the production server as it has already been restored loosing only 2 hrs worth of data, but instead I did this on my TEST database that I run on my Box. I hope this helps you guys out... as an added bonus, I found this great script at www.SQLSERVERCENTRAL.com, I can't say enough good things about that site, it is a kick all users out of my DB script.. just drop it into QA or make a sproc out of it, and run it, it will kick out all your database users, which is required in order to restore it.. here it is....(BTW, if you've never visited the site and you're dealing more and more with SQL you really ought to stop by, there are articles written here with many times real life situations taken into consideration... ) DECLARE @dbname varchar(50) SET @dbname = 'YourDBNAME HERE' DECLARE @strSQL varchar(255) PRINT 'Killing Users' PRINT '-----------------' CREATE table #tmpUsers( spid int, eid int, status varchar(30), loginname varchar(50), hostname varchar(50), blk int, dbname varchar(50), cmd varchar(30)) INSERT INTO #tmpUsers EXEC SP_WHO DECLARE LoginCursor CURSOR READ_ONLY FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname DECLARE @spid varchar(10) DECLARE @dbname2 varchar(40) OPEN LoginCursor FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT 'Killing ' + @spid SET @strSQL = 'KILL ' + @spid EXEC (@strSQL) END FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 END CLOSE LoginCursor DEALLOCATE LoginCursor DROP table #tmpUsers go -Francisco http://rcm.netfirms.com ----- Original Message ----- From: "Stuart McLachlan" <stuart at lexacorp.com.pg> To: <dba-sqlserver at databaseadvisors.com>; <CWortz at tea.state.tx.us> Sent: Saturday, February 22, 2003 5:12 PM Subject: RE: [dba-SQLServer]Rolling back a transaction log? : If he deleted a database, he would have deleted the mirrored version : as well. The only thing mirroring protects you from is physical failure of : a drive. : : > Francisco, : > : > How frequently you backup is dependent on how valuable is the lost data. : > If you can afford to lose a day's worth of data, or if you can easily : > recreate the day's worth of data, then you backup daily. If you can : > afford to lose an hour's worth of data, or if you can easily recreate : > the hour's worth of data, then you backup hourly. If you cannot afford : > to lose any data, then you mirror your database. : > : > Charles Wortz : > Software Development Division : > Texas Education Agency : > 1701 N. Congress Ave : > Austin, TX 78701-1494 : > 512-463-9493 : > CWortz at tea.state.tx.us : > (SELECT * FROM users WHERE clue > 0) : > : > : > -----Original Message----- : > From: Francisco H Tapia [mailto:my.lists at verizon.net] : > Sent: Friday 2003 Feb 21 13:19 : > To: dba-SQLServer at databaseadvisors.com; sswug-sql2k at topica.com : > Subject: [dba-SQLServer]Rolling back a transaction log? : > : > : > Well it finally happened... We have a Complaint database that runs in : > conjunction with a goldmine database. The Goldmine database tracks : > outgoing calls made by our company to customers in order to track sales : > leads and now complaints. Since 4/1/2002 we have not had a database : > error or hiccup until now. The company's official DBA, while working on : > a development database on the production server (yes, that's right) : > inadvertently wiped out my database instead of his test one this : > morning, The users of the Complaint db suddenly began to complaining : > that there were no records, and upon checking I found this to be the : > case. I backup every night, and I have the log file backup when it : > reaches 60%. BUT. I did not have *ANY* protection for the moments : > before the wipe out. Initially I panicked about not being able to kick : > the users out quickly enough... I didn't bother to *remember* that I had : > a Kill All Users In Db script. So about 10 minutes later (after kicking : > all the users out) I restored the database back to last log backup, but : > that was not good as it had the transactions that wiped out the : > database. SO I had to restore to last nights copy officially killing : > all entries from 10am and prior. :( : > : > I've secured my script for killing Active Users in the DB. And My boss : > knows *who* wiped out the database, in fact I made sure he knew as soon : > as it happened ... maybe that's not a good political move, but I'm in : > charge of the db. Now the question is... Since the log file is only : > 1meg long and on average it doesn't backup the log for perhaps every 2 : > to 3 days... (I do make a full backup every night). I suppose I could : > manage the backups to include incremental changes every hr, so that as : > little data is lost? What do you guys suggest? : > : > : > -Francisco : > http://rcm.netfirms.com : > _______________________________________________ : > dba-SQLServer mailing list : > dba-SQLServer at databaseadvisors.com : > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : > http://www.databaseadvisors.com : > : : : -- : Lexacorp Ltd : http://www.lexacorp.com.pg : Information Technology Consultancy, Software Development,System Support. : : : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com :