April 25, 2012 at 7:47 am
While looking to restore a database to another server.
1). Took the nightly backup to restore to the other box
2). I find when looking at the original file name of the backup it is different from the original filenames of the existing backup.
What am I missing here?
April 25, 2012 at 7:57 am
Are you backing up with a maintenance plan? The file name contains a time and date stamp, so it will be different every time.
John
April 25, 2012 at 8:00 am
Yes I am backing up with a maintenance plan.
So there lies the problem correct?
April 25, 2012 at 8:08 am
it would be easier if you put in the post the names of the 2 files so we can see the difference
MVDBA
April 25, 2012 at 8:38 am
You can get the most recent backup file for each database like this:
SELECT
r.DBName
,r.type
,r.BackupStart
,s.backup_finish_date
,s.backup_size
,m.physical_device_name
FROM ( --gets most recent of each type of backup for each DB
SELECT
d.name AS DBName
,b.type
,max(b.backup_start_date) AS BackupStart
FROM master.dbo.sysdatabases d
LEFT JOIN msdb.dbo.backupset b
ON d.name = b.database_name
JOIN msdb..backupmediafamily f
ON b.media_set_id = f.media_set_id
WHERE b.backup_start_date > GETDATE()-14
GROUP BY d.name, b.type) r
JOIN msdb.dbo.backupset s
ON r.DBName = s.database_name
AND r.type = s.type
AND r.BackupStart = s.backup_start_date
JOIN msdb.dbo.backupmediafamily m
ON s.media_set_id = m.media_set_id
ORDER BY r.DBName, r.type
John
April 25, 2012 at 10:39 am
Jonathan Marshall (4/25/2012)
Yes I am backing up with a maintenance plan.So there lies the problem correct?
You haven't defined the problem. 🙂 For us, having different file names is a solution to another possible problem. So why is this a problem for you?
Jared
CE - Microsoft
April 25, 2012 at 12:01 pm
Well I tried backing up the database manually and that didn't change the fact that when I try to restore it on another server the original file name is completely different from the original database.
April 25, 2012 at 12:13 pm
One, unfortunately we can't see what you see. Two, how are you backing up and restoring the databases?
April 25, 2012 at 12:13 pm
Jonathan Marshall (4/25/2012)
Well I tried backing up the database manually and that didn't change the fact that when I try to restore it on another server the original file name is completely different from the original database.
Still don't see a problem. What does the file name matter to you at this point? There are, of course, situations where that can be a problem. However, you have not defined that yet.
So now, I say... Who cares if the restored file name is different than that of the production database? Why is that a problem for YOU?
Jared
CE - Microsoft
April 25, 2012 at 12:18 pm
Wait... I think I have it now!
You are creating a backup and have created an automated process to restore this backup to another server. However, since the filename changed, your automated task is not finding the file that it needs. Is this correct? (If it is, you could have just said it in clearer terms 🙂 )
Jared
CE - Microsoft
April 25, 2012 at 12:28 pm
SQLKnowItAll (4/25/2012)
Wait... I think I have it now!You are creating a backup and have created an automated process to restore this backup to another server. However, since the filename changed, your automated task is not finding the file that it needs. Is this correct? (If it is, you could have just said it in clearer terms 🙂 )
Okay, you must have ssen something, a flash of light perhaps. I'm still in the dark as to what he is talking about.
April 26, 2012 at 1:27 am
when looking at the original file name of the backup it is different from the original filenames of the existing backup.
i have no idea what the difference is between the original backup and the existing backup
MVDBA
April 27, 2012 at 2:38 pm
Okay I'm embarassed to have to report this but I have to given all the help and time everyone has put into this question. First and foremost thank you for your time and responses. I was confusing the logical name with the physical name #1. When restoring on the new box the data files were being named the same. The original database was broken up into a total of 11 different filegroups.
(Ex: 1.mdf, 2.mdf etc...) When restoring the name was the same for all the data files.
(Ex: 1.mdf, 1.mdf etc... ) So basically when back and looked at the filegroups so I could match the logical name with the physical name. The error was stemming from the fact that all datafiles where the same name (Ex: 1.mdf, 1.mdf etc..) This was a complete lack of research on my part.
Hopefully clarified.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply