Creating new 2005 database

Question

Post reply

Creating new 2005 database

WILLIAM MITCHELL

SSChampion

Points: 13766
More actions
June 26, 2007 at 10:38 am

#173120

I'm trying to create a new 2005 db on my dev system that uses the same database, logical, and file names as the production db. I took a backup from production and I am using something like this:
RESTORE FILELISTONLY FROM DISK = N'C:\path\backupfile.bak'
to get the production names out of the backup file. Fine.
However, when I try to use SSMS to create the new db, I can specify the db and logical names and file path, but I can't enter the file name. Is this a bug, or am I missing something?
So what I've done is from SSMS, I right-click on Databases and then click New Database, fill in what I can, then I click the Script to new window option, and then I can modify the script to use the desired file name. When I run the script it does what I want.
Is this a limitation of SSMS?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Camilo Torres SSCrazy Points: 2034 More actions · Answer 1

Right Click on Databases

Select _Restore Database

Select radio button From Device, click on the elipsis (...), find the .bak file

Select the Check box and in the text field on the top To Database:, enter the new db name, of course different from the Production DB.

Then, if you want to script what you just have done, click on the Script to new query window and it creates the script below. But, if you want to proceed with the graphical interface, just press OK.

RESTORE

DATABASE [My_Development_DB] FROM DISK = N'G:\Data_2005\Production_DB_20070319.bak'

WITH

FILE = 1

,

MOVE N'Production_DB_Data' TO N'G:\Data_2005\My_Development_DB.MDF'

,

MOVE N'Production_DB_Log' TO N'G:\Data_2005\My_Development_DB.LDF'

,

NOUNLOAD

,

STATS = 10

GO

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 2

Thanks for your reply. Your method certainly works from SSMS, but I still need to run RESTORE FILELISTONLY ... to get the physical file names, if I didn't make a note of them from the production server (which I forgot to do). And since the production file was on an E: drive, I had to drill-down to the right spot on my C: drive.

Don't you think that SSMS should let you specify a file name, when creating a new database? What do you suppose is MS's reasoning for that.

noeld SSC Guru Points: 96590 More actions · Answer 3

SSMS is a great example of "horrible bugs".

* Noel

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 4

I couldn't agree with you more. One of my pet peeves is that it sometimes loses the database context - e.g. you have a query window open, your db is selected; if you click New Query sometimes it will open to your selected db, other times it will revert to master for the new window so you have to re-select your db. Is that another secret setting somewhere?

And whose bright idea was it to use those irritating fly-out property sheets, like Visual Studio programmers have to put up with? They really can get in the way.

End of rant. Happy 4th of July.