January 19, 2006 at 6:33 am
SQL2K - WinNT. We currently take a backup of the whole server, rather than SQL Server backups. Can I use the backups of the .mdf and .ldf files to restore to another server? The reason I ask is that I beleive you can only use sp_attach_db if you have previously used sp_detach_db.
thanks
January 19, 2006 at 6:46 am
Regardless of whether you have used sp_detach_db you can attach the MDF and LDF files.
As ever, backup your MASTER database on your target server before and after the attach.
You will need to fix your orphaned users as these will be keyed to logins on your source server which may not exist on your target.
January 19, 2006 at 7:19 am
No. If your databases were online when you made copies of the .mdf and .ldf files, they won't be usable. To be able to copy the .mdf and .ldf files and have them usable, they must be offline. This can be done by detaching them or stopping the SQL Server service for the instance.
-SQLBill
January 19, 2006 at 7:21 am
BTW-David and I answered two different parts of your question.
1. Yes you can attach .mdf and .ldf files if you have not detached them (either using sp_detach or Enterprise Manager). (David's answer)
2. No you can not use backups of the .mdf and .ldf files if the database was online when the backups were made. (my answer)
-SQLBill
January 19, 2006 at 7:40 am
Thanks Gents,
What I omitted to say was that SQL Server is stopped whilst we backup the data and log folders (.mdf and .ldf files). Therefore from your response SQLBill, I assume I am O.K. to use sp_attache_db on backup copies of the .mdf and .ldf files on my new server
January 19, 2006 at 11:22 am
Yes you can attach those files to the new server.
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply