| Author |
Topic |
|
Gyto
Posting Yak Master
144 Posts |
 Posted - 2009-01-08 : 05:38:55
|
| Hi there,I am having trouble making a test backup of a database in the same instance as the live one. I was under the impression I could simply make a backup of the live database and then restore it under a new database name, but when I do this it appears to be trying to overwrite the data and log files for the live database.Has anyone got any advice?ThanksMatt |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2009-01-08 : 07:42:21
|
| I am using the GUI restore wizard in Enterprise Manager. On the Options page there are 2 database file columns - one called "original file name" and one called "restore as". Do these both need to be set to the new file path? By default the original file name column contains the paths to the live database files and the restore as column contains the paths to where the new files will be stored.Thanks |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-08 : 09:07:56
|
| Change the location for Mdf and Ldf files in Options page. |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2009-01-08 : 10:11:27
|
| There are 2 different locations specified...."original file name" and "restore as".....I'm not sure which needs changing....or both? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-08 : 10:40:12
|
| Change the location in restore as. It is quite obvious you can modify Restore as from the screen. |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2009-01-08 : 10:51:37
|
| The restore as ones are already correct, which is what is confusing me.... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-08 : 10:55:50
|
quote:
Originally posted by Gyto
The restore as ones are already correct, which is what is confusing me....
I am not understanding what you are doing. Did you change location of MDF and LDF and put the name of database Like(DatabasenameCopy). |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2009-01-08 : 10:55:53
|
| It seems to be working now....I was trying to restore it from a device before but restoring it from the overnight backup seems to work....not sure why it didn't work before though....thanks for all your help! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-08 : 10:57:03
|
Cool  . |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2009-01-08 : 11:02:22
|
| One more thing though....the backup and restore doesn't appear to include Stored procedures....what's the best way to move these seperately?Thanks |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-08 : 11:06:07
|
quote:
Originally posted by Gyto
One more thing though....the backup and restore doesn't appear to include Stored procedures....what's the best way to move these seperately?Thanks
Backup covers everthing done at that time.But if you have created Sp after backup then you can script it out. |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2009-01-08 : 11:17:12
|
| Hmm...strange....it doesn't seem to have restored the SPs....perhaps I missed a checkbox or something?! Ha |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-08 : 12:04:26
|
| You don't have to tick checkbox. Take a full backup of old database and restore it with same procedure and you will see everything. |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2009-01-09 : 05:41:49
|
| How strange....I did it again exactly the same way and this time the SPs are there....! |
|
|
|
|
Copying database to same instance