Re: SQL database restore question
Moses, You need to name each file shown in the metadata in the relocate parameter only if you don't want the location listed in the metadata for that file to be used by the restore command. For example, several of the files listed a location on the E: drive (sorry, don't have the details in front of me any more), meaning they were on the source server's E: drive someplace. If you want those files to be restored to the *same* location on the *target* server, then you can omit them from the /RELOCATE and /TO clauses. Another way of putting this is that, for each file, the metadata supplies the file location that will be used by a restore unless you override that specific file's location with the /RELOCATE and /TO clauses. Make sense? Hope so. :o) -Chris _ From: Moses Show [mailto:[EMAIL PROTECTED] Sent: Friday, August 27, 2004 9:23 AM To: Leonard, Christopher Subject: Re: SQL database restore question Does this mean I have to include each of the files shown in meta data in the relocate parameter and then for each of these files specify a location for them individually on the to parameter. (i.e have the locations I would like the files to be restored to tbe set for each of the files which showed up under meta data listing so the relocate and to parameters should look something like this /relocate=DWH_Data,DWH_Log,DWH_1_Data,DHWPROD_Log,DWH_2_Data, DWHPROD_Log1 /to=E:MSSQL\Data\DWHPROD.mdf,E:\MSSQL\Data\DWHPROD_Log.ldf,E:\MSSQL\Data\DWH PROD1.mdf,E:\MSSQL\Data\DHWPROD_Log.ldf,E:\MSSQL\Data\DWHPROD_Log1_Log.ldf "Leonard, Christopher" <[EMAIL PROTECTED]> Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> 27/08/2004 15:05 Please respond to "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]> To [EMAIL PROTECTED] cc Subject Re: SQL database restore question Moses, Your metadata shows the following 6 files: 3 data files named DWH_Data, DWH_1_Data, and DWH_2_Data and 3 log files named DWH_Log, DWHPROD_Log, and DWHPROD_Log1. However, your TDPSQLC RESTORE command is only relocating two of them, DWH_Data and DWH_Log. Therefore, the other 4 files are trying to restore themselves to locations that are determined from the metadata stored with the backup set. In the case of DHWPROD_Log, the file was located on the F: drive on the original SQL Server, as you can see from your /FILEINFO query. Therefore, since your TDPSQLC RESTORE command does not RELOCATE the file, TDPSQLC is trying to restore it on the F: drive on the target server as well. The solution is that you need to RELOCATE _all_ the files that should have different physical names on the target server. Hope this helps, Chris _ Chris Leonard MCSE, MCDBA, OCP, CIW _ The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com _ == This communication, together with any attachments hereto or links contained herein, is for the sole use of the intended recipient(s) and may contain information that is confidential or legally protected. If you are not the intended recipient, you are hereby notified that any review, disclosure, copying, dissemination, distribution or use of this communication is STRICTLY PROHIBITED. If you have received this communication in error, please notify the sender immediately by return e-mail message and delete the original and all copies of the communication, along with any attachments hereto or links herein, from your system. == The St. Paul Travelers e-mail system made this annotation on 08/27/2004, 10:24:50 AM. smime.p7s Description: S/MIME cryptographic signature
Re: SQL database restore question
Moses, Your metadata shows the following 6 files: 3 data files named DWH_Data, DWH_1_Data, and DWH_2_Data and 3 log files named DWH_Log, DWHPROD_Log, and DWHPROD_Log1. However, your TDPSQLC RESTORE command is only relocating two of them, DWH_Data and DWH_Log. Therefore, the other 4 files are trying to restore themselves to locations that are determined from the metadata stored with the backup set. In the case of DHWPROD_Log, the file was located on the F: drive on the original SQL Server, as you can see from your /FILEINFO query. Therefore, since your TDPSQLC RESTORE command does not RELOCATE the file, TDPSQLC is trying to restore it on the F: drive on the target server as well. The solution is that you need to RELOCATE _all_ the files that should have different physical names on the target server. Hope this helps, Chris _ Chris Leonard MCSE, MCDBA, OCP, CIW _ The Database Guy http://www.databaseguy.com Brainbench MVP for Oracle Admin http://www.brainbench.com _ smime.p7s Description: S/MIME cryptographic signature
SQL database restore question
Hi people, Have been trying to restore a database to a different location on a different server but am having problems. Was wondering if anyone can shed any light as to where I am going wrong. First I queried the TSM server for the fileinfo of the database I want to restore from the new intended location as follows: C:\Program Files\Tivoli\TSM\TDPSql>tdpsqlc query TSM DWHPROD full /fileinfo /fro msqlserver=GBSTPF54 /tsmnode=GBSTPF54_SQL /tsmpassword=ntbackup /mountwait=yes IBM Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server Version 5, Release 2, Level 1.0 (C) Copyright IBM Corporation 1997, 2003. All rights reserved. Restoring metadata ... Waiting for TSM server.. ..Backup Object Information - SQL Server Name GBSTPF54 SQL Database Name DWHPROD Backup Object Type ... Full Backup Object State .. Active Backup Creation Date / Time .. 25-08-2004 22:17:55 Backup Size .. 104,785,560,064 Database Object Name . 20040825221755\0998 Number of stripes in backup object ... 1 SQL Group Logical Name ... PRIMARY SQL Group Space Allocated 114,512,822,272 SQL Group Space Used . 104,743,239,680 SQL File Logical Name ... DWH_Data SQL File Physical Name .. E:\Microsoft SQL Server\MSSQL\Data\DW HPROD.mdf SQL File Space Allocated 701,956,096 SQL File Space Used . 648,478,720 SQL File Logical Name ... DWH_1_Data SQL File Physical Name .. E:\Microsoft SQL Server\MSSQL\Data\DW HPROD_1.mdf SQL File Space Allocated 668,860,416 SQL File Space Used . 618,528,768 SQL File Logical Name ... DWH_2_Data SQL File Physical Name .. E:\Microsoft SQL Server\MSSQL\Data\DW HPROD_2.mdf SQL File Space Allocated 113,142,005,760 SQL File Space Used . 103,476,232,192 SQL Group Logical Name ... TRANSACTION LOG SQL Group Space Allocated 17,093,885,952 SQL Group Space Used . 16,693,812,224 SQL File Logical Name ... DWH_Log SQL File Physical Name .. E:\Microsoft SQL Server\MSSQL\Data\DW HPROD_LOG.LDF SQL File Space Allocated 105,906,176 SQL File Logical Name ... DHWPROD_Log SQL File Physical Name .. F:\Microsoft SQL Server\MSSQL\DATA\DH WPROD_Log.LDF SQL File Space Allocated 8,493,465,600 SQL File Logical Name ... DWHPROD_Log1 SQL File Physical Name .. E:\Microsoft SQL Server\MSSQL\Data\DW HPROD_Log1_Log.LDF SQL File Space Allocated 8,494,514,176 Using the information fro here I attempted to restore using the following command and this is what happens C:\Program Files\Tivoli\TSM\TDPSql>tdpsqlc restore DWHPROD full /relocate=DWH_Da ta,DWH_Log /to=E:\Restore\DWHPROD.mdf,E:\Restore\DHWPROD_Log.LDF /fromsqlserver= GBSTPF54 /sqlserver=GBSTPT55 /tsmnode=GBSTPF54_SQL /tsmpassword=ntbackup /mountw ait=yes IBM Tivoli Storage Manager for Databases: Data Protection for Microsoft SQL Server Version 5, Release 2, Level 1.0 (C) Copyright IBM Corporation 1997, 2003. All rights reserved. Starting Sql database restore... Querying Tivoli Storage Manager server for a list of database backups, please wa it... Restoring metadata ... Waiting for TSM server.. . Beginning full restore of backup object DWHPROD, 1 of 1, to database DWHPROD Full: 0 Read: 0 Written: 0 Rate: 0.00 Kb/Sec Waiting for TSM server... Full: 1 Read: 3145728 Written: 6656 Rate: 0.22 Kb/Sec Restore of DWHPROD failed. [Microsoft][ODBC SQL Server Driver][SQL Server]File 'F:\Microsoft SQL Server\MSS QL\DATA\DHWPROD_Log.LDF' is on a network device not supported for database files . [Microsoft][ODBC SQL Server Driver][SQL Server]File 'DHWPROD_Log' cannot be rest ored to 'F:\Microsoft SQL Server\MSSQL\DATA\DHWPROD_Log.LDF'. Use WITH MOVE to i dentify a valid location for the file. [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE DATABASE is terminating a bnormally. Total database backups inspected: 1 Total database backups requested for restore: 1 Total database backups restored:0 Total database skipped: 0 Throughput rate:0.22 Kb/Sec Total bytes transferred:6,656 Elapsed processing time:30.00 Sec
