Restoring a database

[jammanter]

Thank you the help in advance. My problem is that I am testing database recovery for an upgrade. I want my programmers to prove to me that they can restore this database in case something happens. The database is 98Gb, so moving it is tough in itself. They tried restoring a copy of the specific database itself to a different machine. They then installed SQL 7.0 and created a new table with the same name as the restored table. Then they shut the SQL down and copied over the restored database and log file, and restarted the SQL. They are receiving multiple access denied errors, as well as a suspect database for the restored one, and it will not open. I believe there is a little more involved with this, but I am not sure. If any can tell me what they are doing wrong or missing, I would appreciate it.

 

[misgirlny]

Follow the recommendations in the article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q304692

I used those recommendations when moved SQL Server DBs across the network.

 

[funcrecall12]

Hello.

Moving Database.... 98 GB. Just copy the 2 file(ldf and mdf) and attached it.

 

[jammanter]

Thank you for the replies. Problem with the second reply is there is no attaching into a SQL 7.0 database. Unless there is something I missed on that one. I was able to attach it to a SQL 2000 database with no problems. However, some of the front end work and references are written in FoxPro and do not seem to like 2000 as of yet. I'm still working on this one. The only database backed up is the production one, not the master, temp, or any other. I believe if they want to do what they say, then they will need to back up all of the databases and their log files, especially the system databases. This is what my next step will be for them to try. This would be in a disaster situation, and the only database available on the tape is the production database.

 

[tlbroadbent]

SQL 7 allows attaching of SQL 7 databases. SQL 2000 databases cannot be attached in SQL 7. Neither can you copy SQL 2000 database files over SQL 7 databases and restart SQL 7. The files are of a different format and cannot be loaded.

Were the original files created in SQL 2000? If so, that would explain the errors and the suspect database. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[funcrecall12]

Hello.

Terry I have Copy/Paste your reply =).

"SQL 7 allows attaching of SQL 7 databases. SQL 2000 databases cannot be attached in SQL 7. Neither can you copy SQL 2000 database files over SQL 7 databases and restart SQL 7. The files are of a different format and cannot be loaded.

Were the original files created in SQL 2000? If so, that would explain the errors and the suspect database.
"



Just in case the systax on how to attach in SQL 7.0

EXEC sp_attach_db @dbname = N'DB',
@filename1 = N'c:\Datapath\Data.mdf',
@filename2 = N'c:\Datapath\Data_log.ldf'

Jerry