Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Data Recovery from MDF and LDF files
- Thread starter Ross1664
- Start date
tlbroadbent
MIS
While I would agree that this backup method is not ideal, it certainly is workable. MDF and LDF files can attached as databases.
1) Detach the current database that will restored. Use SQL Entrprise Manager (SEM) or Query Analyzer (QA). See sp_detach_db in SQL Books Online (BOL).
exec sp_detach_db 'databasename'
2) Rename the files if you want to preserve them. Copy the backed up files to the SQL Data folder.
3) Attach the database using the backed up files. You can do this in SEM or QA. See sp_attach_db in BOL.
Alternatively, you can rename the backed up files, copy to the data folder and attach as a new database with a different name. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
1) Detach the current database that will restored. Use SQL Entrprise Manager (SEM) or Query Analyzer (QA). See sp_detach_db in SQL Books Online (BOL).
exec sp_detach_db 'databasename'
2) Rename the files if you want to preserve them. Copy the backed up files to the SQL Data folder.
3) Attach the database using the backed up files. You can do this in SEM or QA. See sp_attach_db in BOL.
Alternatively, you can rename the backed up files, copy to the data folder and attach as a new database with a different name. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
re: sp_attach_db
I notice there's a related SP called:
sp_attach_single_file_db
I gather this is used if you don't have (or don't want to attach) the saved LDF file. This command will apparently build you a new log, when attaching the MDF file.
I notice there's a related SP called:
sp_attach_single_file_db
I gather this is used if you don't have (or don't want to attach) the saved LDF file. This command will apparently build you a new log, when attaching the MDF file.
tlbroadbent
MIS
Brian,
That's correct. sp_attach_single_file_db is especially useful when you need to quickly shrink an out of control transaction log. There are a couple of FAQs in this forum that discuss this technique. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
That's correct. sp_attach_single_file_db is especially useful when you need to quickly shrink an out of control transaction log. There are a couple of FAQs in this forum that discuss this technique. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Thread starter
- #6
AnalystDeveloper
IS-IT--Management
I'm applying this method by different way. I did these:
- I created a batch file called CopyMyDb.BAT
- This Batch is running through Windows Schedule in a certain time.
- This file contains these command:
NET STOP MSSQLSERVER AGENT
NET STOP MICROSOFT SQL SERVER
COPY *.MDF D:\MYBACKUP
COPY *.LDF D:\MYBACKUP
NET START MICROSOFT SQL SERVER
NET START MSSQLSERVER AGENT
I may typed the NET commands wrong as I remember now and it works with me.
- I created a batch file called CopyMyDb.BAT
- This Batch is running through Windows Schedule in a certain time.
- This file contains these command:
NET STOP MSSQLSERVER AGENT
NET STOP MICROSOFT SQL SERVER
COPY *.MDF D:\MYBACKUP
COPY *.LDF D:\MYBACKUP
NET START MICROSOFT SQL SERVER
NET START MSSQLSERVER AGENT
I may typed the NET commands wrong as I remember now and it works with me.
- Status
