Link to home
Create AccountLog in
Avatar of Vijay
Vijay

asked on

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
Avatar of Nakul Vachhrajani
Nakul Vachhrajani
Flag of India image

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.
I'm 200% with Nakul.
What's the reason for not attaching the .ldf files?
Avatar of Vijay
Vijay

ASKER

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of Nakul Vachhrajani
Nakul Vachhrajani
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Vijay

ASKER

Thank you very much Nakul