SQL Server Performance Forum – Threads Archive

SQL Server 6.5 backup issue and database transfer

Dear all In my company, it is still using MS SQL Server 6.5 as the primary internal database.
Every night, it uses the Archserve as the backup tool for backup. However, I have the following questions. 1. If the SQL server is down (either OS or media failure). How can I restore the backup to the existing machine or another machine. 2. As I know that there is a database transfer function in SQL server 6.5. Can I transfer the whole databases into another machine weekly as the secondary backup. It yes, how to do it ? 3. What about the standard procedures to restore the backup data files to another machine. For example, setup a new machine with the same OS and new SQL Server,
copy the backup data files and so on … Thanks for advance!!! Regards Kwan
19 May 2004

Well, its a suprise that shops out there are still using SQL 6.5 as no more support availabel from MS. In the case of (1) you need to create another database with similar sizes and use previous good known backup to restore by using LOAD STATEMENT. For (2) yes you can schedule the transfer of objects by using that ‘Database/object transfer’ option in EM and ensure both the SQLExecutive accounts has required privileges to complete the task. For (3) also you can use DUMP & LOAD statements to backup and restore the database and recreate a similar database with similar sizes and restore them. For more information SQL Server books online for 6.5 is the best bet. Any other issues let us know. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Dear Satya SKJ Thanks for your help. For 3, by using ‘dump’ and ‘load’ method. Can I dump both the data and other objects (e.g. trigger, stored procedures, index) and load them to the new database. And what are the correct synatx for dump and load command. Is these are the inline command? Meanwhile, can I dump the database during the production time? Regards Kwan
20 May 2004

DUMP is nothing but BACKUP of database in ver 6.5 as it includes the complete database, the syntax is :
Dumping a database:
DUMP DATABASE {dbname | @dbname_var}
TO dump_device [, dump_device2 […, dump_device32]]
[WITH options
[[,] STATS [ = percentage]]] Refer to the books online for 6.5 for more information syntax. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>