asked on

restoring a copy database using a full backup

Hi,

I have a large database that I want to make a copy of so that I can then remove all the audit information that is stored in there to be able to copy it to my local computer for testing purposes.

I have the backup up file of my database, say mydatabase.bak

I am trying to run the following sql to restore it to a blank database I have created mydatabasebackup.

restore database simon
from disk ='c:\folder\mydatabase.bak'
with norecovery,
move 'c:\simon\weddingplanner.bak' to 'c:\folder\mydatabasebackup.bak'

Open in new window

but I'm getting:
he backup set holds a backup of a database other than the existing 'mydatabasebackup'
database.

What am I doing wrong?

Thanks

tim_cs

I believe you need to add WITH REPLACE.

restore database simon
from disk ='c:\folder\mydatabase.bak'
with norecovery,
move 'c:\simon\weddingplanner.bak' to 'c:\folder\mydatabasebackup.bak'
WITH REPLACE

scm0sml

ASKER

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

?

tim_cs

Try it this way.

restore database simon
from disk ='c:\folder\mydatabase.bak'
WITH REPLACE,with norecovery,
move 'c:\simon\weddingplanner.bak' to 'c:\folder\mydatabasebackup.bak'

GD_GRAY

Dont know how to delete this comment.

scm0sml

ASKER

Incorrect syntax near the keyword 'with'

tim_cs

restore database simon
from disk ='c:\folder\mydatabase.bak'
WITH REPLACE, norecovery,
move 'c:\simon\weddingplanner.bak' to 'c:\folder\mydatabasebackup.bak'

scm0sml

ASKER

Logical file 'c:\simon\weddingplanner.bak' is not part of database 'simon'. Use RESTORE FILELISTONLY to list the logical file names.

??

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

You only need to specify NORECOVERY if you intend to apply differential backups and/or log files after the main db is restored. That doesn't seem to apply here, so I removed the NORECOVERY.

If you leave the NORECOVERY in the restore, then apply a differential, if you want, and one or more log files, if you want.

Then as the final step you must do this:

RESTORE DATABASE <db_name>
WITH RECOVERY

scm0sml

ASKER

and just to confirm...

when i'm doing move file to file... this won't affect the live database will it?

Scott Pletcher

Not IF AND ONLY IF ou restore to a different db name and different data files.

That's why I changed the restore to "simon_test", and used "_test" in all the file names.

To be a little safer, get rid of the "REPLACE" option:

RESTORE DATABASE simon_test
FROM DISK = 'c:\folder\mydatabase.bak'
WITH
MOVE '<logical_name_primary_fg> TO 'c:\folder\data\simon_test.mdf',
MOVE '<logical_name_log> TO 'c:\folder\data\simon_test_log.ldf'