July 18, 2005 at 2:03 pm
I am looking for any one that want to "chat",email, discuss this subject.
Short story on problem. Rela
Server A is restore do Server B. They don't have the same drive configuration. Installed same SQL Server on server B, patched, ect.
1) Restore master : complete
2) Start sql instance (named) : failed. The model database is not in the same location as the old one. PLus it is not the same because the master was restored.
With certain combo of parameters, you also get errors that the user databases are not available and can not be found. Of course they cant' be found, I did not get to resotre them?
Am I doing this backwords? Resotre userdatabases then ....????
I have played wit -c -f -m -e, all parameters. Very frustated.
Thanks,
Joseph
July 19, 2005 at 11:12 am
Here is a copy of part of my recover plan:
2. Stop SQL Server and SQL Server Agent from the SQL Server Service Manager
3. Start SQL Server in single user mode by command ‘sqlservr.exe -c –m’
4. Restore master database using Enterprise Manager
a. Right Click master database and choose restore database from ‘All Tasks’
b. Choose to Restore From device and click Select Devices
c. Choose Add and browse for a File
d. Choose the most recent full database backup (.BAK) in the MSSQL\BACKUP\master directory
e. When the restore is complete it will shut down SQL Server
5. If installed on a drive that is a different location that the original database, do steps a - j
a. Restart the Server in single user mode that allows detaching system database by the command ‘sqlservr.exe -c –m -T3608’
b. Detach the databases (in order) using Query Analyzer:
1. sp_detach_db 'Northwind'
2. sp_detach_db 'pubs'
3. sp_detach_db 'msdb'
4. sp_detach_db 'model'
c. Reattach the databases (in order) using Query Analyzer:
1. sp_attach_db 'model', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\model.mdf', 'C:\ Program Files\Microsoft SQL Server\MSSQL\Data\modellog.ldf'
2. sp_attach_db 'msdb', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\msdbdata.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\msdblog.ldf'
3. sp_attach_db 'pubs', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'
4. sp_attach_db 'Northwind', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\northwnd.mdf', 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\northwnd.ldf'
d. Move tempdb using Query Analyzer:
1. Alter database tempdb modify file (name = tempdev, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf')
2. Alter database tempdb modify file (name = templog, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\templog.ldf')
e. Close command window for previous start
f. Restart the Server in a mode that ignores some errors and allows changing system tables by the command ‘sqlservr.exe -c -T3608 -T4022’
g. From Enterprise Manager open the table sysdatabases (return all rows) in the master database
h. Edit the filename field and change E:\SQLDATA\MSSQL\Data\<database> to C:\Program Files\Microsoft SQL Server\MSSQL\Data\<database> for all rows
i. Close command window for previous start
j. Restart SQL Server (not SQL Server Agent)
6. Restore databases msdb and model using Enterprise Mangager
a. Right Click the database and choose restore database from ‘All Tasks’
b. Choose to Restore From device and click Select Devices
c. Choose Add and browse for a File
d. Choose the most recent full database backup (.BAK) in the ‘MSSQL\BACKUP\<database>’ directory
7. Add Local Administrator (if different than the machine being recovered) to the Logins with a System Administrator role
8. If the server name is changed, edit the sysjobs table in msdb database and change the Originating Server to the new one.
9. Restore the user databases using Enterprise Manager
a. Right Click the database and choose restore database from ‘All Tasks’
b. Choose to Restore From device and click Select Devices
c. Choose Add and browse for a File
d. Choose the most recent full database backup (.BAK) in the MSSQL\BACKUP\<database>’ directory
e. If installed on a drive that is a different location that the original database, under Options, change the ‘Move to physical file name’ to the new location for the log and data files
f. If there are transaction logs backups, be sure to choose the option “Leave database nonoperational but able to restore additional transaction logs”
g. After the full restore, for each transaction log:
i. For ‘Restore backup set’ choose ‘Transaction log’
ii. Choose to Restore From device and click Select Devices
iii. Choose Add and browse for a File
iv. Choose the each transaction log backup (.TRN) in the same directory as the full database backup, starting with the first one created after the full backup and in order
v. If installed on a drive that is a different location that the original database, under Options, change the ‘Move to physical file name’ to the new location for the log and data files
vi. If it is not the last transaction log, choose the option “Leave database nonoperational but able to restore additional transaction logs” otherwise, choose the option “Leave database operational. No additional transaction logs can be restored”.
10. Restart SQL Server Agent
11. Change maintenance plans to Perform full backups (if not currently done by maintenance plan) and run appropriate jobs. For example:
a. DB Backup Job for DB Maintenance Plan 'DB Maintenance – System'
b. DB Backup Job for DB Maintenance Plan 'DB Maintenance - Production'
c. DB Backup Job for DB Maintenance Plan 'DB Maintenance - Test'
12. If maintenance plan did not perform full backups, change them back.
If the same IP address was not used, update ODBC DSN’s on user machines to point to the new machine.
Hope this helps.
Steve
July 19, 2005 at 12:09 pm
May want to read http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
Tim S
July 20, 2005 at 5:46 am
I have searched Microsoft, but have not come across that article. Looks like I need to improve my search skills as well.
Thank you both, I will read and try and post my results.
Joseph
July 22, 2005 at 5:39 am
Thank you for your help. I will post my solution shortly.
There is an undocumentd parameter -Q that I had to use. With out it my restore does not work with it. I even contacted Microsoft Support and the teir1 guy and his super could not find out what it is for.
The short story is, despite all that is posted, with the varations of hardware and software out there, you have to plow through this yourself for the company you work for. I would say that the general steps and documentation are accurate.
Thanks for you help, I will try to have the results posted by Monday afternoon if you want to take a lootk.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply