September 4, 2008 at 6:24 am
Heheh.
I guess what it all boils down to is that a real DBA will always have a real DB backup. 😉
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
September 4, 2008 at 3:26 pm
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 db
where (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 name
and 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..
September 4, 2008 at 3:46 pm
Yeah, 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.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 4, 2008 at 4:05 pm
Vika,
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!!
September 5, 2008 at 4:43 am
Vika (9/4/2008)
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..
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!
September 5, 2008 at 8:24 am
No, 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"]"And how often do they test that they can restore the server from tape backup? "[/font]
You gotta be kidding me... none of course.. 😛
September 5, 2008 at 8:29 am
What 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)
September 5, 2008 at 9:11 am
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?
September 5, 2008 at 9:39 am
Vika (9/4/2008)
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 db
where (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 name
and 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..
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.crdate
FROM 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 desc
You can add a date filter if your results are too big, such as:
WHERE backup_finish_date > DATEADD(DAY, -90, (getdate())) -- Within last 90 days
September 5, 2008 at 9:54 am
Vika (9/5/2008)
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?
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.
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
September 5, 2008 at 10:49 am
Thanks 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?
September 5, 2008 at 11:00 am
Yes, 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.
😎
September 5, 2008 at 11:10 am
After 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.
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
September 5, 2008 at 12:04 pm
I 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]
GO
ALTER DATABASE [North]
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (N'North_log' , 0, TRUNCATEONLY)
GO
or shoule I write here
DBCC SHRINKFILE (N'North_log' , 1024)
GO
ALTER DATABASE [North]
SET RECOVERY FULL;
GO
BACKUP 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 = 10
GO
September 5, 2008 at 4:16 pm
OK 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.
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply