February 13, 2009 at 7:51 am
Today in SQL Server management studio, I wanted to verify that a backup I performed last night worked.
So created a new temp database, and then right-clicked on that database, went to Tasks > Restore > Database
Here, everything looks pretty easy, I selected the backup file from device and then i would have clicked 'Ok', but I noticed an 'options' selected up in the top left window.
So I selected that, and noticed a section called "restore the database files as:"
This is where I got confused. Because the 3 files listed, are not the files that should be associated with the database I am trying to restore. In fact, I have no idea where they came from.
Should I be worried?
Thanks
February 13, 2009 at 8:26 am
Most likely means that the backup file you selected isn't the one you actually wanted. I'd definitely double-check that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 13, 2009 at 8:49 am
but it is the correct backup file. I even tested it again...and the files in the restore as option are always different from the actual name of the database.
Here is what I did:
I backed up a database called HomeDesignDB
I then created a database called HD_DB_Test
I then tried to restore the HomeDesignDB backup into the new database HD_DB_Test
However, as I said above, when I goto options, and look at the files for the 'restore as'...the files names for the .mdf. and .ldf files are not for HD_DB_Test...they are for HomeDesignDB.
Thanks
February 13, 2009 at 8:54 am
The name of the mdf/ldf in the restore are the physical names of the files of the database that was backed up. If you are restoring a database backup to a database with a different name, you need to either change the name of mdf/ldf's or the directory to which they are restored.
February 13, 2009 at 10:11 am
Magy (2/13/2009)
but it is the correct backup file. I even tested it again...and the files in the restore as option are always different from the actual name of the database.Here is what I did:
I backed up a database called HomeDesignDB
I then created a database called HD_DB_Test
I then tried to restore the HomeDesignDB backup into the new database HD_DB_Test
However, as I said above, when I goto options, and look at the files for the 'restore as'...the files names for the .mdf. and .ldf files are not for HD_DB_Test...they are for HomeDesignDB.
Thanks
When the original database was created, the file names were not set to match the database name for some reason. Possibly they were created from an earlier restore.
You can create a database called DatabaseA, and give the file name anything you want like DatabaseX.mdf. It's confusing, so I don't think anyone does it intentionally. But if that's the case, when you back up DatabaseA, then restore it, you will see the file name of DatabaseX.mdf during the restore process.
I came across some of these at my job, so during off hours, I detached the database, renamed the .mdf, then reattached.
February 13, 2009 at 10:33 am
Maybe this will help.
When you backup your database, HomeDesignDB, the backup file contains the physical location and filenames of the mdf/ndf(if present)/ldf files. For example D:\Databases\HomeDesignDB.mdf, D:\Databases\HomeDesignDB.ldf.
When you restore the database this is where it will put those files. In your case, you are trying to restore it as HD_DB_Test. It still wants to restore the the physical files D:\Databases\HomeDesignDB.mdf, D:\Databases\HomeDesignDB.ldf which is not what you want. You need to change these names to D:\Databases\HD_DB_Test.mdf and D:\Databases\D:\Databases\HomeDesignDB.mdf, D:\Databases\HomeDesignDB.ldf.ldf.
If then script the restore you will see this reflected in the WITH clause as MOVE clauses.
I have to do this any time I restore a production database to a development, test, or training database.
February 13, 2009 at 3:09 pm
note you don't have to create a database first before doing a restore. Its also a lot easier to understand what's going on if you do it via scripts rather than the GUI.
---------------------------------------------------------------------
February 13, 2009 at 3:17 pm
george sibbald (2/13/2009)
note you don't have to create a database first before doing a restore. Its also a lot easier to understand what's going on if you do it via scripts rather than the GUI.
I actually start with the GUI for my restores, use it to generate the script, then modify the script as needed and then run the script.
It sure saves me a lot of typing.
February 13, 2009 at 3:28 pm
Lynn Pettis (2/13/2009)
george sibbald (2/13/2009)
note you don't have to create a database first before doing a restore. Its also a lot easier to understand what's going on if you do it via scripts rather than the GUI.I actually start with the GUI for my restores, use it to generate the script, then modify the script as needed and then run the script.
It sure saves me a lot of typing.
I came to SQL from SYBASE, so was already in the habit of scripting and never got into the habit of using the GUI, especially for restores. But you're right, generating the sql script from the GUI is one way to get the code, and as a recent article reminded me, there are also the templates in SSMS.
---------------------------------------------------------------------
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply