I have 2 databases called UC_MAIN and UC_DEV. They are exactly the same except for the name of course. i wanna backup the data/logs from the uc_main and restore it to uc_dev. Im having all sort of problems. I tried restore database with replace, MOve..it didnt work..
anybody has the step by step tsql that do this.
i know i can do DTS. but i wanna do it using tsql first.
BACKUP DATABASE uc_main TO DISK = 'F:\MSSQL\BACKUP\uc_main.BAK; WITH INIT
RESTORE DATABASE uc_dev FROM DISK = 'F:\MSSQL\BACKUP\uc_main.BAK' WITH MOVE 'uc_main_data' TO 'F:\MSSQL\BACKUP\uc_dev_data.MDF', MOVE 'uc_main_log' TO 'F:\MSSQL\BACKUP\uc_dev_log.ldf', REPLACE
You just need to figure out which logical names to use in the WITH MOVE option. Use RESTORE FILELISTONLY for this.
--This will give you the name of the logical files on the backup file. RESTORE FILELISTONLY FROM DISK = 'file.bak'
--You then need to restore using both replace and move. RESTORE database_name FROM DISK = file.bak WITH MOVE 'logical_file_data' TO 'file.mdf', --logical_file_data is the data file from RESTORE FILELISTONLY. --file.mdf is the .mdf file for the database. MOVE 'logical_file_log' TO 'file.ldf', --logical_file_log is the log file from RESTORE FILELISTONLY. --file.mdf is the .ldf file for the database. REPLACE
BACKUP/RESTORE to different database name
Posted - 06/18/2004 : 19:01:09
