Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2004-03-19 : 07:14:18
|
| I've just discovered a problem in our system that occured in July 2001!! Fortunately I have backups from that long ago, what I want to do is a restore to a different database name so that the live system is unaffected. I don't have much experience of restores but on the few occasions I have done them have not had a problem, (and the SQL server admin course was over 4 years ago now). When I try to do a restore on this database to a different database name(using the Wizard!!) I get the message :-The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY, restart the restore sequence, specifying WITH NORECOVERY or WITH STANDBY for all but the final step...In addition to this I can't make use of the point in time recovery. I want to restore the data to how it was on 1st July 2001. My server is SQL server 7.What am I doing wrong? I presume that using T-SQL is going to be the way to do this as then I can specify WITH NORECOVERY or WITH STANDBY and I can't see where to put these into the wizard. What is the difference between restore and recovery?thanks in advancesteveSteve no function beer well without |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-19 : 08:16:53
|
| Don't use the wizard, open up Query Analyzer and use RESTORE DATABASE. Books Online has the syntax.Trust me, once you start using BACKUP/RESTORE in QA you will NEVER use EM for these again. MUCH MUCH easier. |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-19 : 09:33:05
|
| Like Rob said, use Query Analyzer for this. Books Online has an example you can copy under "How to restore a database with a new name".What is the difference between restore and recovery?Restore refers to the actual restoration of a database from a backup file.Recovery puts the database in a state that it can be used. If you use no recovery, you can still apply transaction logs to the restored database. The with recovery option makes the database a live database ready for use.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-03-19 : 09:57:03
|
| >> I don't have much experience of restoresUntil you restore a database you don't know whether you have a backup or not.You should be running test restores on a regular basis (I always try to automate them and run dbcc checks on the restored copy).Amazing how many companies happily backup databases then find that they have never been restorable.I have a feeling you are backing up to a device rather than a file - it's much easier (and safer) to use distinct files for each backup then you don't lose a number of backups when you lose the file and can see from the directory what you have.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-19 : 12:27:10
|
| Steve,What options did you select in EM? I would recommend using QA as the others here mentioned, but I'm curious as to what you selected. Here is an example QA command:RESTORE DATABASE NewDBNameFROM DISK = 'F:\MSSQL\BACKUP\OldDBName.BAK'WITH REPLACE, MOVE 'OldDBName_Data' TO 'E:\MSSQL\DATA\NewDBName_Data.MDF',MOVE 'OldDBName_Log' TO 'E:\MSSQL\DATA\NewDBName_Data.LDF',RECOVERYTara |
|
|
|
|
|
|
|
|
The Final Restoration