Can you restore database to a different name on SQL Server? 3

[scc]

Is there a way to restore a database to the same SQL Server 7, but use a different database name?

Need to restore to a temporary database to grab about 1000 records from one table that got damage.

Is this possible?

TIA

 

[SonOfEmidec1100]

Yes, you need to use the RESTORE T-SQL command, if you look in MSSQL BOL it explains it with examples far better than I can. Specifically refer to Example E Make a copy of a database using BACKUP and RESTORE,

 

[ecobb]

I'm not sure about SQL Server 7, but in 2000 you can do it from Enterprise Manager. When you right click on the database and tell it to restore, you can specify what database to restore to. If the database you specify isn't there, SQL Server creates it.

Hope this Helps.

 

[MaffewW]

yes, SQL7 to, through EM change the name in the 'restore as' field. Then change the filenames of the data and log files in the 'restore as' fields uner the options tab

Matt

Brighton, UK

 

[scc]

For what ever reason, would get an error message doing it through EM.

Finally had to get Microsoft on the phone and they walked us through doing it in Query Analyzer.

Next question, is there a way to restore just a table, or do you have to always restore the entire database?

Thanks, and stars for all of you!

 

[ecobb]

To my knowledge, there's really no way to restore a single table. I think it pretty much has to be the whole database. You can restore your transactions since your last database backup. But, there again, I'm not sure if this can be table specific or not. I don't believe so.

 

[scc]

So is it better to have several databases instead of one huge one?

Programming across databases doesn't seem very efficient to me when the data all relates to each other.

Would appreciate hearing what others do. Fortunately we don't have to recover very often, but when we do, need to be able to do it as quickly as possible so we can minimize down time.

 

[ecobb]

A single database is definitely quicker to recover than several databases. If all of the data relates to each other, and will constantly be interacting with each other, then I would suggest putting it in one database. Especially if you're talking about taking each table and creating it's own database just for the sole purpose of recovery. The best thing to do is have 1 database and set up a good backup schedule for database and transaction logs. I have databases with 200-300 tables in them, and if it becomes necessary I can restore them to the exact point of failure. (But I hope I never have to!!)

 

[scc]

So if you just need to restore one table, not the whole database, and you don't want to overwrite the other tables, the only option you have is to restore it to a different database name.. therefore taking much time and space to do this and then pulling out the table that you want?

Is this correct?

 

[MaffewW]

thats correct. I believe in SQL Server 6.5 you could restore a single table though since then it is as you say. shame as theres times I cold have done with recovering a single table as you do

Matt

Brighton, UK