Copy DB to another DB on the same Server?

Question

Copy DB to another DB on the same Server?

Viewing 5 posts - 16 through 19 (of 19 total)

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

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

If you can afford a little downtime another really simple method is to shut down SQL Server, make a copy of the data and log files (using a different filename), and then after starting SQL Server back up use sp_attach making sure to give the database a new name if it's on the same server. This ensures that everything associated with the original DB gets copied and works well on the same server or when moving/copying databases between servers. Best of luck!

My hovercraft is full of eels.

mcneased SSC Enthusiast Points: 154 More actions · Answer 2

Sorry to resurrect this topic however I’d like to ask a follow-up question. I need to copy a database to another database on the same server (MSSQL 7) also; gfahrlander suggested changing the physical file name and location in the options tab. The original database is named PDIS and the copy will be called CATS.

Here are the steps I’ve taken:

I’ve created a db called CATS, backed up PDIS and performed a force over restore of PDIS on CATS. I’m restoring from “Device” using the backup file and changing the Logical file name from PDIS_Data, PDIS_Log to CATS_Data, CATS_Log and setting “Move to physical file name” to the corresponding CATS name convention. Upon clicking OK in enterprise manager, the following error is produced “File CATS_Data is not a database file for database CATS. Backup or restore operation terminating abnormally”. When I check the database properties for CATS, the physical file name is listed as CATS_Data, so I’m not sure what’s going on there. During restore, if I leave the file names PDIS_Data and PDIS_Log in the options tab and rename the “Move to physical file name” to the corresponding CATS name convention, the restore is successful however going back and checking the database properties of CATS, the physical file name is now listed as PDIS_Data with a location pointing to CATS_Data.mdf. The same is true for the transaction log.

My questions are:

1.Am I incorrectly restoring the database to cause these problems?

2.Since PDIS and CATS are on the same server, should I be concerned about the two databases file names being the same when their location paths are pointing to different MDF and LDF files?

Any insight about this will be appreciated.

Doug

msutter SSC Eights! Points: 990 More actions · Answer 3

Example of restore SQL.

Here is an example of the SQL that I figured out to do this. The new db is an exact copy of the old including users and collation, with the possible issues noted earlier in the thread.

Restore DATABASE JDCBudget

FROM DISK ='c:\Customer\DEERE\DBBACKUPS\JDCForecast.dat'

WITH RECOVERY,

MOVE 'JDCForecast' to 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\JDCBudget.mdf',

MOVE 'JDCForecast_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\DATA\JDCBudget_log.ldf'

mcneased SSC Enthusiast Points: 154 More actions · Answer 4

Thanks for the additional information. Posting your sql statement was helpful.

Best regards,

Doug

StephenMarais SSC-Addicted Points: 452 More actions · Answer 5

I would suggest that you create a new database on the same server. You then need to retrive info on the datbase you setup using sp_helpdb NewDatabase. This together with info from the original database can be used in the following query to restore the database into the new location.

restore database DatabaseName from disk = 'G:\..\DatabaseBackup.BAK'

with replace,

move 'OrigDatabaseDataDeviceName' to 'G:\..\NewDatabaseDataDevice.MDF',

move 'OrigDatabaseLogDeviceName' to 'F:\..\NewDatabaseLogDevice.LDF'

Stephen Marais
Integration Architect
Digiata Technologies
www.digiata.com