October 24, 2006 at 6:52 am
i have a server with a db on it. I also have a back up of that db from 12 hours ago. an update on a table went wrong, and with no rollback statement i will have to restore teh data from the backup. but i dont want to restore the whole database. so my plan is to create a 2nd db on teh server, and restore my backup to that. how do you do that without disturbing log files and without affecting the live database on the server?
October 24, 2006 at 7:35 am
Hello,
You can restore the database with an alternate name to it and direct the specific data and log files to a different drive, if you have any. Then you can compare the data with respect to the affected table.
Hope this helps.
Thanks
Lucky
October 24, 2006 at 5:24 pm
you can also use the "move" parameter and specify new file names... putting the files on the same drive.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982
October 25, 2006 at 7:42 am
If you are restoring to a different database name OR a different server,:
1. In the Query Analyzer, review the contents of the backup file with the following command:
RESTORE FILELISTONLY
FROM Disk = 'BACKUPFILELOCATION’
where BACKUPFILELOCATION = the physical location of the backup file of the Source Database
2. Jot down the "Logical Name" values contained in the query results
3. To restore the database (to a different database name or a different database server) from a full backup use the following commands:
USE MASTER
RESTORE DATABASE DATABASENAME
FROM Disk = 'FULLBACKUPFILELOCATION'
WITH REPLACE,
MOVE 'DATA_LOGICALNAME' TO 'PHYSICAL_DATA_LOCATION',
MOVE 'LOG_LOGICALNAME' TO 'PHYSICAL_LOG_LOCATION'
where DATABASENAME = the name of the Destination Database
FULLBACKUPFILELOCATION = the physical location of the full backup file
DATA_LOGICALNAME = the "Logical Name" of the data portion on the backup file
PHYSICAL_DATA_LOCATION = the physical location on the server of the mdf file for the Destination Database (You can get this from the "Properties" screen for the database in the SQL Enterprise Manager)
LOG_LOGICALNAME = the "Logical Name" of the log portion on the backup file
PHYSICAL_LOG_LOCATION = the physical location on server of the ldf file for the Destination Database (You can get this from the "Properties" screen for the database in the SQL Enterprise Manager)
Norene Malaney
October 27, 2006 at 12:57 pm
Hello - I am new. We have SQL 2005. I used the instructions from Norene to work on a disaster recovery process. We are attempting to demonstrate we have the ability to recover from a significant disaster and we need to be able to take a backup file from one place and restore it to another server. Your instructions were really clear.
When I used the TSQL commands, it gave me an error on the statement shown below saying "syntax error near Move". Then it said "use WITH MOVE".
MOVE 'DATA_LOGICALNAME' TO 'PHYSICAL_DATA_LOCATION',
(My "data_logicalname etc DOES have the actual logical name and physical name - not the sample data in your statement.) Do you know if I need to change the command to meet some SQL 2005 requirement. Naturally, there might be some other issue involved.
By the way, in spite of the errors, it appeared to move the files over. But there was a "cannot open backup device" error as well, so I may also have a permissions issue which I thought I had already sorted out by acting on some of the other posted information and KB's in MS.
thanks for any ideas.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply