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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VijayAuthor Commented:
Thank you very much Nakul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.