January 15, 2010 at 2:18 pm
Hi,
I have a DB that I would like to clone for testing on the same SQL Box, basicly what I tried is "backing" it up and then I created a new DB and tried restoring to it. this did not work well, the error message that I got was:
"The backup set holds a backup of a database other than the existing database"
The message is true, the database I am restoring to has a different name, because the original database that I backed up from is stil on that server.
The purpose of this is to clone it, so that I can do an update on that database to test our software, without affecting the original DB.
What would be the best way of doing this?
Thank you for your help.
January 15, 2010 at 2:20 pm
You can choose copy database option also..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
January 15, 2010 at 2:24 pm
I am going to give that a try, thanks.
January 15, 2010 at 2:55 pm
backup data base MyDB to disk = N'MyDB.bak'; -- remove space between data and base, only way to get this to post from work
restore database MyDBClone
from disk = N'MyDB.bak'
with file = 1,
move 'MyDB_data' to N'MyDBClone.mdf',
move 'MyDB_log' to N'MyDBClone.ldf';
For more information, please read about RESTORE in BOL (Books Online, the SQL Server Help System).
Of course the example I have provided is somewhat simplistic.
January 15, 2010 at 3:18 pm
What method of backup are you doing ? Is it appending to backup device with other backups ?
Simplest method is backup to disk by itself (Database_Name.BAK), then restore from that, specifying the new DB name.
January 17, 2010 at 2:32 am
Hello There,
Perform the below steps:
(1) Take a backup of the database which you want to clone.
(2) Run the verifyonly and filelistonly queries to check the consistency of the backups.
xp_restore_filelistonly @filename ='location of the bkp file' (use this if you are having litespeed, if not, check the BOL for the correct syntax)
(3) Now you can restore the db (with the new name) using the backup.
The above steps will work fine.
Note: Run the restore script in the master DB.
Let us know if we can assist you further.
Thanks.
January 18, 2010 at 6:07 am
mirde (1/15/2010)
... basicly what I tried is "backing" it up and then I created a new DB and tried restoring to it. this did not work well, the error message that I got was:"The backup set holds a backup of a database other than the existing database"
The message is true, the database I am restoring to has a different name, because the original database that I backed up from is stil on that server.
Why did you put it this way? "backing" as if you didn't really back it up? Either you did back it up or you didn't.
It sounds like you're trying to either restore a snapshot or a differential. You need a full database backup, restore it using "WITH MOVE" (see BOL as previously advised-- NOTE: Database name doesn't matter. DB Backup type and File location does matter), then fix the orphaned users. Once all that is done, you're good to go.
I am interested in what you meant by " basically..."backing" it up", though. Could you please clarify?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply