June 15, 2004 at 12:14 pm
Does anyone know how to restore SQL Server without having the Data files (*.mdf).
We currently use Veritas to backup the system but we do not have an Open File Agent to capture the open mdf and ldf files.
We have a good nightly backup of master and all of the system DB's as well as the application Db's.
Any Ideas?
Thanks
June 15, 2004 at 12:19 pm
With Veritas, the Open File Option won't work with SQL Server .mdf and .ldf files. You need the Agent for SQL Server.
What exactly are you trying to do? What happened?
Are you trying to restore your database? If so, you need to have a valid backup. If you didn't use the builtin SQL Server BACKUP DATABASE and BACKUP LOG commands (or the SQL Server maintenance plans) and you don't have Veritas' Agent for SQL Server to use with Backup Exec - you are probably out of luck.
If you just want to know the best way to backup your database(s) so you can restore if you lose the files, then use the SQL Server BACKUP commands, backup the database(s) to hard drive and then use Backup Exec to copy those files to tape - then you don't need the Agent for SQL Server.
-SQLBill
June 15, 2004 at 12:30 pm
Nightly using Veritas we backup the Windows server which contains SQL Server.
We also backup each database nightly using a SQL Server maintenance plan.
We have restored the Windows backup to another machine on a separate disaster recovery network. But since we do not have an Open File Agent for SQL Server/Veritas, the Windows backup does not capture the data files (.mdf and .ldf).
So After a successful Windows restoration SQL Server will not start because it can’t find the data files.
Do you know how to restore SQL Server without having these Data files (*.mdf).
Thanks for the quick response
June 15, 2004 at 12:35 pm
Without the datafiles (or a backup of them) you will have lost all of the data on that server. The mdf files contain all of the database data.
June 15, 2004 at 12:45 pm
I do have the backup files (master_db_200406142332.BAK…)
I just can’t start SQL Server because SQL Server can’t find the actual data files (*.mdf)
Is there some way to start SQL Server using the master database for The install CD and then restore from the backup(master_db_200406142332.BAK…) I have.
Thanks for the quick response
June 15, 2004 at 1:38 pm
You will need to re-install SQL Server on your DR box, then restore the backups.
You will also have to repair permissions, as all of your db users will be orphaned. See sp_change_users_login in BOL.
Steve
June 15, 2004 at 9:11 pm
Have a look in book online at article "Restoring the master Database from a Current Backup". I think it will answer your question
June 16, 2004 at 7:48 am
Let me see if I have this correct...
You have a SQL Server up and running. You used Veritas to backup the Operating System and SQL Server maint plan to backup SQL Server.
You now want to restore all of that to ANOTHER server. You have already restored the Operating System, but need to restore the SQL Server files.
Correct?
If so, INSTALL SQL Server on the new server (From CD). Then copy the SQL Server backup files to the new server. RESTORE the user databases to the new SQL Server instance. You will need to use the WITH MOVE option. You can replace the System databases also, do a search on the articles - there's a couple on moving System databases (master, model, msdb). There is NO need to replace the TEMPDB.
If I'm not correct on what is going on, let me know.
-SQLBill
Refer to the BOL for more information.
June 16, 2004 at 11:39 am
Another possibility....did your Veritas backup of the operating system backup ALL the files? If so, then the files the maintenance plan created when it did the backups should have been copied by Veritas.
If that's the case, you should have the files restored when you restored the OS. Install SQL Server using the CD, then use RESTORE the databases.
-SQLBill
June 16, 2004 at 12:12 pm
SQLBill,
You are correct I have the backup files (.bak) for all of my databases, when Veritas completed its backup I had all the files on the server except for the actual SQL Server data files held in the Microsoft SQL Server\MSSQL\Data directory. These files were skipped by Veritas.
Because I do not have these data files after the restore to a new machine, I can not start SQL server.
I was really trying to get around a new installation of SQL Server, so I have use the Rebuildm.exe to rebuild the master database and SQL is now up and running. But I am unable to restore the Master database from the backup I have. Yes I have started SQL Server in single user mode (sqlservr.exe). The error message is non descriptive at least to me. The message simply states “Microsoft SQL DMO (ODBC SQLState 42000).
At that point everything is hosed up and I have to stop the single user mode.
When I rebuilt the master DB it obviously reset the SA accounts password, I thought maybe since the SA account passwords being out of sync caused the problem so I reset password to match the original. I then tried again to restore the backup without any luck.
I’m in the process of rebuilding the Domain Controller because of different errors. I mention this because the SQL Server service is started with a domain account. This maybe causing issues as well. I’m not sure how sqlservr.exe starts SQL that is if it uses the SQL Server service account?
I’ll update once the Domain Controller is up.
Thanks for you response
June 16, 2004 at 2:43 pm
Also, as I said search the articles on this site for information on restoring the Master database.
-SQLBill
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply