Database Create Date after restore.
-
I restored from a *.bak to two different servers. The restore was succesful yet the database create date is different. Should this date change?
Does the create date update at the time of "define new DB" Does this date ever change without delete and define?
Unable to confirm it myself but will keep trying and report back.
-
This was removed by the editor as SPAM
-
I'm resurecting this old post.
I found the answer. 
The create date changes if you drop the database and restore after.
SELECT name, crdate
FROM MASTER.dbo.sysdatabases
But if you restore over an existing DB it doesn't change.
Now to find the latest restore use
SELECT destination_database_name, MAX(restore_date)
FROM MSDB.dbo.restorehistory
GROUP BY destination_database_name
ORDER BY MAX(restore_date), destination_database_name
Hope this helps
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Just an fyi. On really large database, dropping and restoring vs. restoring over old database is waaaaay slower because of how SQL Server 2000 claims space in the database file. Be careful with this one.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA. -
Trust me, I know.
Now does anyone have an SP to restore databases automatically?
I have a DB that needs to be restored at EOM to another DB to do the balancing. Basically on the morning of the first of the month restore the OLTP DB as an OLAP DB with a different DBEOM.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Jim, what exactly does the stored proc need to do? If the backup name is the same every month, then a job with the restore statement coded in the job step should suffice. If the backup name changes (ie with a date/time stamp), then a proc might be the answer. I don't have a proc that would fit exactly, but I do have code that might help.
Steve
-
The backup name changes (ie with a date/time stamp).
That's the problem. I cheat and just use the maint plan wizard to do the backups.
I know it should just be a a simple pull last full from the msdb..backuphistory. Then I need to do the with move. I just can't wrap my mind around it at the moment.
And I'm getting to be busier than a one handed wallpaper hanger at the moment as well. BTW NetVision isn't as easy as it looks.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Here's what I've got. I have a stored proc that deletes my old backup files based on the date/time stamp in the filename. I also have a stored proc that PUTs the date/time stamp on the file name in the first place. AND, I have a stored proc which builds RESTORE statements for all the transaction log backups according to start and stop time parameters, and based on the date/time stamp on the filename. If you think any or all of these may help you, send me a private message with your e-mail and I'll be happy to send them to you.
Steve
-
:mad
Smacking my forehead) 
Actually, I appreciate the offer....but I just came up with a quicker idea. Just the following freakin steps..
..I could have done this a couple of years back.create cp_EOM
AS
BACKUP DATABASE PROD TO DISK ='L:\MSSQL\BACKUPS\PRODEOM.BAK'
RESTORE DATABASE PRODEOM FROM
DISK ='L:\MSSQL\BACKUPS\PRODEOM.BAK'
WITH REPLACE, MOVE LogicalName TO FileSpec
I have enough disk space to easily hold a spare backup.I just have to run a delete
xp_cmdshell 'delete L:\MSSQL\BACKUPS\PRODEOM.BAK' a couple days after so that if I have a crash it is recoverable.
I think that would work. Or am I off my rocker?
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Looks good to me. The only part I'm not completely understanding is...
"I just have to run a delete
xp_cmdshell 'delete L:\MSSQL\BACKUPS\PRODEOM.BAK' a couple days after so that if I have a crash it is recoverable."
I'm not sure how deleting the backup improves recoverability. To me, if you leave it there (assuming sufficient space), your recovery is actually simpler, and quicker. You'd just have to re-run the restore job. At the end of the month, when the new backup is created, just overwrite it. Am I missing something?
Steve
-
I meant it in reference to recovery of the production database. My normal backup time for the DB is at 7:30PM for it to be out there for the nightly tape backup to pick it up.
I normally keep 3 days backup and 2 days trans log backups on the server for recovery. If I immediately delete the full backup I then hack into the tran log backups and point in time recovery. Therefore I have to keep it around for a few days to get it picked up on tape and wait until it cycles out.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Let me see if I understand what you are saying...
If you delete the EOM backup, your transaction log chain is broken, prohibiting point in time recovery?
If that's what you meant, it actually is not true. Additional full backups give you more flexibility in your point in time recovability. If you take daily full backups, and hourly transaction log backups, and assuming that you permanently keep ALL of them, you could use last night's full backup, and all the transaction log backups to recover to 9:00 this morning. But if last night's full was corrupt or unavailable, you could use the previous night's full backup, and all transaction log backups to recover to 9:00 this morning. Or, even last year's EOY full backup, and all tr.... well, you get the picture. A full backup will not break the tlog chain. Only a non-logged operation or truncate will do that. So, you do not need to depend on that EOM backup for point in time recoverability.
Steve
-
True. Just that when you look at the EM Restore - it's a bear to skip a full. And as Steve Jones pointed out in The Value of A DBA your speed of recovery can mean congrats or update your resume. And you are only as your last disaster.
I've done QA restores before of a full and 20 tran logs.
That was a major nightmare. 
I have better things to do with my life.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Its easy if you script it! We take log backups every 15 minutes. 95 log backups in a single day (skip the time that the full starts). So, manual restores are NOT something that I want to do! I wrote a procedure that creates the restore statements for me. Give it a database name, start time, stop time, and a "restoreas" name, and it creates all of the restore tlog statements including the last one with the "stopat". Then creates a "restore database with recovery" to run when it finishes.
Let me know if you'd like to have it.
Steve
-
Please.
I'll PM you my e-mail.But while you're at it, throw a copy out in the contribution center. I'm sure others would like to have it.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply