InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 17:17:51 GMT20http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxAnd don't forget to test the backupsSun, 07 Sep 2008 19:44:54 GMTAndrew Peterson-472853http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxThis is a bizarre situation..I hope NONE of the DBA's end up in this situation. I remember when I was new my seniors asked me to take a backup no matter what before doing any DML operation. My sympathies with the poor guy, first off he shouldnt have been given this responsibilty and having given should have been monitored...Ok coming back to this problem how about this solution( just to try)DOING A SYSTEM RESTORE, I MEAN AT THE OS LEVEL TO THE POINT IN TIME..isn't this what we do on our PC's if we unintentionally do a SHIFT+DELETEHaving said that please be advised these kind of things should be brought up at the TEAM discussions becuase in a T.E.A.M..Together Everyone Achieves MoreRegards,Just Another D.B.A(Doing Better Always)Sun, 07 Sep 2008 13:47:45 GMTThe_SQL_DBAhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxTwo way street here... ;) What did you finally do to fix it?Fri, 05 Sep 2008 18:06:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxOK guys,thank you so much to everybody for help and advices. I fixed the situation with the log file today and didn't ruin anything. It's a good day.Fri, 05 Sep 2008 16:16:40 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxI am told to do it right now do I am in the same situation like my co-worker that dropped the database... but I still have a question regarding a script that I am going to run...USE [North]GOALTER DATABASE [North]SET RECOVERY SIMPLE;GO[i]DBCC SHRINKFILE (N'North_log' , 0, TRUNCATEONLY)GO[/i]or shoule I write here [i]DBCC SHRINKFILE (N'North_log' , 1024)GO[/i]ALTER DATABASE [North]SET RECOVERY FULL;GOBACKUP DATABASE [North] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\North.bak' WITH NOFORMAT, NOINIT, NAME = N'North-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GOFri, 05 Sep 2008 12:04:31 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxAfter switching to simple, run a checkpoint. It's the checkpoint that triggers log truncation in simple recovery.Make very, very sure you take a full backup after switching back to full recovery. Until you do that, your log chain is broken and you will not be able to do point in time recoveries.Fri, 05 Sep 2008 11:10:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxYes, changing the recovery model from full to simple will truncate the log allowing you to shrink the log. Immediately after shrinking the log, change recovery model back to full, run a full backup, and then start regularly scheduled t-log backups to keep the t-log relatively small.:cool:Fri, 05 Sep 2008 11:00:19 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxThanks for the script, it's just great!And if I set up a db into the simple recovery mode, then it will empty the log, and after that I will be able to shrink it?Fri, 05 Sep 2008 10:49:55 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Vika (9/5/2008)[/b][hr]And another question. If log is full and so huge that there is no space to back it up, would it help to shrink it first and then back it up?[/quote]No. If the log is full then it means there's no free space left inside. Shrinking a log just releases free space to the file system. Hence, if the log is full, you will not be able to shrink it.Fri, 05 Sep 2008 09:54:57 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Vika (9/4/2008)[/b][hr]Guess what I am doing now? Making SQL Agent jobs to back up all the databases on the production server. I run that useful query that some nice person offered me here:select * from master..sysdatabases dbwhere (not exists (select * from msdb.dbo.backupset where db.name = msdb.dbo.backupset.database_name and backup_start_date > DATEADD(DAY, -30, (getdate()))))order by nameand nothing including master etc is backed up. I was only allowed to create backup jobs because the log file got so big that application doesn't work anymor (Error, transaction log is full backup log ).I am not a DBA, I am a programmer. There is no DBA. At the same time tech services told me "There are backups!" They probably mean there are physical tape backups of the server. Or log wouldn't be full..[/quote]You're welcome :)Here's another script you might find useful. It lists all the backups (Full, Diff, Log) for all the databases. So although the above script might show no full backup in the last X days, this script will show any Differentials and Log backups that may have occured since the full.SELECT sysdb.name, bkup.description, bkup.backup_finish_date, case when type='D' then 'FULL' when type='I' then 'DIFFERENTIAL' when type='L' then 'LOG'end as Backup_Type, ceiling(bkup.backup_size /1048576) as 'Size Meg' , cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig', server_name, sysdb.crdateFROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name ORDER BY sysdb.name, bkup.backup_finish_date descYou can add a date filter if your results are too big, such as:WHERE backup_finish_date > DATEADD(DAY, -90, (getdate())) -- Within last 90 daysFri, 05 Sep 2008 09:39:17 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxAnd another question. If log is full and so huge that there is no space to back it up, would it help to shrink it first and then back it up?Fri, 05 Sep 2008 09:11:33 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxWhat should I do if I want to become an entry level DBA?What book to read (online?), what to study, is there any short course that would teach you basics (how to backup, how to move, how to set permissions for users etc, what to do so disk doesn't run out of space etc)Fri, 05 Sep 2008 08:29:24 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxNo, I didn't try to restore it with undelete.. Two weeks have passed and many people walked all over the place.. it's like a crime scene and evidence is already destroyed since too many people tried to do their job.. and I am not experienced in that kind of stuff.[font="Times New Roman"][size="3"][i]"And how often do they test that they can restore the server from tape backup? "[/i][/size][/font]You gotta be kidding me... none of course.. :PFri, 05 Sep 2008 08:24:17 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Vika (9/4/2008)[/b][hr]I am not a DBA, I am a programmer. There is no DBA. At the same time tech services told me "There are backups!" They probably mean there are physical tape backups of the server. Or log wouldn't be full..[/quote]And how often do they test that they can restore the server from tape backup? There is a world of difference between having backups, and having backups that work!Fri, 05 Sep 2008 04:43:08 GMTGraham_Dayhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxVika, I just want to stress how important it is not to do anything that may use the original data drive until you've taken a shot at recovering the deleleted files as Lowell has suggested. The data is still there until the OS writes over the disk sectors. You've posted a couple of times since he's suggested using an undelete utility which makes me think that you have not attempted to do this yet. I would think that you would be scrammbling to get the files recovered instead of setting up new backup plans. Just curious, did you see Lowell's suggestion? If not, go back and read it. Your data is still there until the OS overwrites it!!Thu, 04 Sep 2008 16:05:22 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxYeah, and as Gail replied earlier, the backups of the whole server are not backups as the database has to be in a state where there is no activity to the file and it is closed cleanly in order for there to be a successful tape backup. They may be doing that with a SQL Backup agent like one provided BackupExec or something similar but even then it would issue a checkpoint and the log would shrink. So, glad to hear that you are setting those up AND that the other guy did not lose his job.Thu, 04 Sep 2008 15:46:02 GMTDavid Benoithttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxGuess what I am doing now? Making SQL Agent jobs to back up all the databases on the production server. I run that useful query that some nice person offered me here:select * from master..sysdatabases dbwhere (not exists (select * from msdb.dbo.backupset where db.name = msdb.dbo.backupset.database_name and backup_start_date > DATEADD(DAY, -30, (getdate()))))order by nameand nothing including master etc is backed up. I was only allowed to create backup jobs because the log file got so big that application doesn't work anymor (Error, transaction log is full backup log ).I am not a DBA, I am a programmer. There is no DBA. At the same time tech services told me "There are backups!" They probably mean there are physical tape backups of the server. Or log wouldn't be full..Thu, 04 Sep 2008 15:26:57 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxHeheh. I guess what it all boils down to is that a real DBA will always have a real DB backup. ;)Thu, 04 Sep 2008 06:24:14 GMTVegard Hagenhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxIt is possible to get a good backup of SQL Server databases using block level copy, providing you do all the right things. If you miss something out, you are most likely to end up with SQL saying the database is corrupt.To get things right, you need to consider what happens if the power fails for a server while SQL is running. In this situation, the data in the MDF and LDF files is consistant - the LDF will know which transactions have been committed, which are open, what LRSNs should be on MDF pages associated with incomplete transactions, etc. When you restart SQL after a power failure, it should do the standard roll forward to the end of the log, then roll back incomplete transactions to get the MDF file to a stable state.If you use block level copy, you must get the same level of consistency or you will not be able to get a working recovery.This means all the disks holding your data and log files must be in the same SAN mirror group. This will allow a script to suspend the mirroring for the whole set of disks at the same time, simulating a power failure on the server. The frozen mirror can then be backed up using block level copy. After the backup is complete, re-enable the mirroring. You need to test this process a number of times with different databases to make sure it works for you. Even if it does work, you need to be aware this process is inherently more risky than taking normal SQL Server backups, as any small difference in timing the freezing of the mirror on the data and log disks will give you an non-useable backup.If you do something different, like suspend the mirror on the data disks at a different time to suspending the mirror on the log disks, then you will have an non-useable backup. If you just take a block level copy without using a frozen mirror, you will have a non-useable backup.The only truely safe way to take block level backups of SQL Server databases is to stop SQL server while the backups are being taken.So, if your SAN people have the right skills then block level backups while SQL is running are possible. Even so, my recommendation is to always take SQL Server backups then use the SAN to take a copy of these backups.Thu, 04 Sep 2008 06:14:55 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]GilaMonster (9/3/2008)[/b][hr]Then implement some backup jobs. Next time it could be you getting the written warning, or fired. [/quote]Yup. It only takes a couple of clicks in the GUI, and deciding on a location to place them. Less time than it took to post in this thread.Then put those backup files (*.BAK) to tape instead.I don't know the structure of your organization, but whoever has a job title closest to "DBA" seems to be asking for trouble.Wed, 03 Sep 2008 13:15:40 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxThen implement some backup jobs. Next time it could be you getting the written warning, or fired. You won't be able to say 'I didn't know there weren't backups'If the file backups are run on the SQL databases while the sQL service is running, there's a very good chance that those backups are useless.Wed, 03 Sep 2008 12:46:22 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxAnyway,he had to sign a paper... one strike... still there is no backup job that runs SQL on the regular basis. DB files just get backed up physically on the tape (SAN) somehow.. no disaster recovery plan.. and so it goes ... ;)Wed, 03 Sep 2008 12:41:44 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Lowell (8/29/2008)[/b][hr]when you delete a file, regardless of the recycle bin, the file is still there, but the disk space it used is open for use by other programs...if you hurry and grab an UNDELETE utility, you might still have a chance to recover the mdf and ldf files. google undelete and try any of the utilities you find.[/quote]this is prob about your only hope, but sadly if SQL server is still running and other database files are growing the deleted files could well be lost, might be worth a go though!Mon, 01 Sep 2008 06:42:18 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Vegard Hagen (9/1/2008)[/b][hr]Fearless and clueless is a [b]very[/b] dangerous combination.[/quote]ohh so true![quote][b]Vegard Hagen (9/1/2008)[/b][hr]What he should have done is of course to detach the DB, move the files and then attach the DB with the files in their new location. [/quote]you dont need to detach the database to move the files. Set the database offline and use the alter database statement to move the file locations, copy the files and then set back to online :cool:Mon, 01 Sep 2008 06:33:33 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxFearless and clueless is a [b]very[/b] dangerous combination...What he should have done is of course to detach the DB, move the files and then attach the DB with the files in their new location. But even then, he should have had a backup before tampering with a production DB. Bad things can happen anytime, anywhere - especially when people are involved. But another thing I don't understand is - why did the physical location of the files determine whether you got a backup or not? This sounds fishy to me. You need to have a backup of the database itself, not just a plain file backup. The actual location of the physical files shouldn't matter if this is set up right.Mon, 01 Sep 2008 03:56:34 GMTVegard Hagenhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Steve Jones - Editor (8/31/2008)[/b][hr]You should probably read the security sections in Books Online to understand this. [/quote]This in the wrong place?Sun, 31 Aug 2008 10:07:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxIf you get something to work, I'd be interested to see what happened here.And good luckSun, 31 Aug 2008 09:35:54 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxYou should probably read the security sections in Books Online to understand this. Not to beat on you, and I know you just want to get something working, but not understanding security and just getting bare minimums setup is one reason we have so many security issues today.http://msdn.microsoft.com/en-us/library/bb510418.aspxLogin - You need one setup to connect to the server. You can use a Windows account and set that in IIS, and I'd recommend that.User: You map a user to the login, you can do this when you create the login, I'd keep them the same name.Roles: Create one. You might think you'll only need one user, but you never know. Just create a role and add the user from above to it.Permissions: Choose the role, add permissions to each sproc and then insert/update/delete to tables. Note that I'd add the GRANT XX to (role) to each statement as I built objects just to be sure that they were there and you had good version control of permissions. If you only use the GUI, this should be a step you take with each object and I'd be sure you script out the objects periodically and drop that in a VCS.Sun, 31 Aug 2008 09:35:36 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Lowell (8/29/2008)[/b][hr]when you delete a file, regardless of the recycle bin, the file is still there, but the disk space it used is open for use by other programs...if you hurry and grab an UNDELETE utility, you might still have a chance to recover the mdf and ldf files. google undelete and try any of the utilities you find.[/quote]Spot on, Lowell!Sat, 30 Aug 2008 21:16:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxOh I feel sorry for that guy. But I surprise how the database can be deleted when it is in use. most of the production db's will be continuously in use. And wht happened to your backup. Dont you have proper disaster recovery strategy.Sat, 30 Aug 2008 01:43:20 GMTChand00http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote]google undelete and try any of the utilities you find. [/quote]But don't install them on the same drives where the mdf and ldf files used to be located on. In fact, don't do anything on those drives.Fri, 29 Aug 2008 10:31:55 GMTRay Mondhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxwhen you delete a file, regardless of the recycle bin, the file is still there, but the disk space it used is open for use by other programs...if you hurry and grab an UNDELETE utility, you might still have a chance to recover the mdf and ldf files. google undelete and try any of the utilities you find.Fri, 29 Aug 2008 09:59:32 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Loner (8/29/2008)[/b][hr]When I pointed out her mistakes, her boss complained to my boss that I was a difficult person to work with!!!!! Oh well!!!![/quote]Did you rat her out? Let me guess... she wasn't pretty and... you definitively are a "difficult person to work with" :DFri, 29 Aug 2008 09:35:44 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxIt amazes me that the lack of backups is so common. I will be starting a new job soon, and after finding my desk, the first think I will do will be to find out their backup procedures & verify their backups. I'll run that little script above, find out if/when the backup files get written to tape etc etc ......Accidents happen, servers crash, things break, corruption occurs, users want to restore data from odd-ball times ..... Sometimes a specific recovery is not possible, but someone in the organization needs to make sure that the basics are covered.Fri, 29 Aug 2008 09:34:28 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxI hope your co-worker and your boss learn a good lesson. Of course somebody never learns from mistake!!!!!! One of my former DBA kept making mistake and her boss kept backing her up. When I pointed out her mistakes, her boss complained to my boss that I was a difficult person to work with!!!!! Oh well!!!!There is no backup of that database ever? Not even a few months ago?Fri, 29 Aug 2008 09:18:26 GMTLonerhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxYou can run something like this to identify databases that have not been backed up in the last n days:select * from master..sysdatabases dbwhere (not exists (select * from msdb.dbo.backupset where db.name = msdb.dbo.backupset.database_name and backup_start_date > DATEADD(DAY, -2, (getdate()))))order by nameFri, 29 Aug 2008 09:18:18 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspx[quote][b]Vika (8/29/2008)[/b][hr]Guys,The database suppose to be setup on the disk where everything gets backed up automatically but following the written instructions (that had an error in the path!) he set it up on a different disk ( where nothing gets backed up). Database was in use for a while and then it was discovered that it doesn't get backed up.[/quote]That's not how you backup a SQL database. Copying the files of an in-use SQL database to tape/other backup device is not going to work properly. You're very likely to get a suspect database if you try to 'recover' from that.Use SQL's backup mechanism (backup database to..., or maintenance plan) to create database backup files, then back those files up to taps or whereever you put your backups.Fri, 29 Aug 2008 08:54:53 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxOkay... we all get your co-worker is "fearless" and has not guts to say "no", the question here is... what did your coworker do before dropping the database? Please don't tell me the first task in his "migration plan" was "drop the target database".Fri, 29 Aug 2008 08:36:04 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxGuys,all that you said is correct. I checked the recycle bin on the server. No db files there, in fact it's empty.The database suppose to be setup on the disk where everything gets backed up automatically but following the written instructions (that had an error in the path!) he set it up on a different disk ( where nothing gets backed up). Database was in use for a while and then it was discovered that it doesn't get backed up.The same evening it was discovered that programmer was told to move it to another drive asap. Yes, bosses told him/approved the procedure. The guy didn't do any manual backup before following the order, didn't tell the rest of us, never moved db in his live, and he is fearless!I always have that fear in my guts that I'll do something wrong and I think it over and over and try it on the test db. He just goes ahead and does things and bosses tell him to do things and it's dangerous. Somehow he thought he needed to drop db before restoring it on a different disk. Maybe he mixed up drop and de-attach commands or maybe just didn't think but it's too late to know. We re-created the schema from other db but data was gone. Yes, it should be a lesson but there is a pattern of being fearless for bosses and for him and this is no good. I hope we are safe and will keep our jobs LOLFri, 29 Aug 2008 08:24:05 GMTViiiikooolllaaaaaa!http://www.sqlservercentral.com/Forums/Topic560688-357-1.aspxWho made the decision not to have daily backups ? Can't just blame that on your co-worker too, can you ?Thu, 28 Aug 2008 17:51:02 GMThomebrew01