January 7, 2010 at 9:42 am
Hi,
I have a database and want to copy its tables, etc, to another database, so I can use one of these databases as a "staging" DB.
What is the best/most foolproof way to do this?
I tried the backup database/restore to another database (which obv has a diff name) but got this error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'DEV1'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1352.12+((KJ_PreRelease).091030-1757+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\maximumbhp.mdf' cannot be overwritten. It is being used by database 'maximumbhp'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1352.12+((KJ_PreRelease).091030-1757+)&LinkId=20476
BUTTONS:
OK
I would add this to a comment but the comments section is not big enough.
January 7, 2010 at 9:52 am
presuming you do want to overwrite the target database backup and restore is best method but you will need the replace and move clauses
i.e,
restore database targetname from disk = 'path' with replace,
move 'logical filename' to 'new physical path',
move 'logical filename' to 'new physical path'
you will need a move for each file in the database, so there will be at least two (data and log)
see restore in BOL for full explanation
make sure you get the right database name!
---------------------------------------------------------------------
January 7, 2010 at 10:08 am
Two things, when you restore in SSMS
- Change the target db name
- Change the file names/locations.
As George mentioned the WITH MOVE clause does this in T-SQL. In SSMS, you need to change these things in the dialog.
January 7, 2010 at 10:13 am
yes of course the op would have been using the gui, thanks steve.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply