Attach DB without LDF file

We are planning to move 100 DB to another servers.

The procedure is, just detach and attach method. But the thing attaching without LDF.
How to attach all these 100 Db's with out LDF (Generating new LDF dueing attach).

Moving from SQL Server 2008 to SQL Server 2016
VijayAsked:
Who is Participating?
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Whatever the environment is, you should only attach databases without the .ldf file if somehow you lost it. Having it, then it's recommended to attach them.

I echo Vitor's statement. Just because it is QA doesn't justify forgetting about the log file(s). The recommendation would be that you attach them.

If you are seeing issues in attaching the log file, my suggestion would be to spend the time fixing those issues (maybe you have too many VLFs in the log file and it needs to be backed up and then resized properly or that a log backup has never been taken because of which a lot of time is being spent in recovery/redo phase).

Nakul what you provided is only for one DB.

Well, multiple databases would simply need that the CREATE DATABASE statement be repeated multiple times with the batch separator "GO" in between.
1
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
It's possible, but please do take a step back and ensure that you have a very good reason to recreate the log files. Recreating the log files will change the recovery model of the database and also break the log chain, thereby destroying your ability to do a point-in-time restore.

It's possible to attach without the log files using the CREATE DATABASE command with the ATTACH_REBUILD_LOG option (https://nakulvachhrajani.com/2013/03/04/0245-sql-server-create-databasehellipattachrebuildlog-resets-database-recovery-mode-to-simple/).

Also note that because the transaction log file is being rebuilt, we do not have control over the location, initial size and file growth increment values. The new log file will be created at the default log path location and will have a size of 1MB.
1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm 200% with Nakul.
What's the reason for not attaching the .ldf files?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
VijayAuthor Commented:
Hi Vitro and nakul,

This is not PROD , just we are testing in QA.
I am looking for T-SQL to attached multipe databases. Nakul what you provided is only for one DB.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Whatever the environment is, you should only attach databases without the .ldf file if somehow you lost it. Having it, then it's recommended to attach them.
1
 
VijayAuthor Commented:
Thank you very much Nakul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.