How to create database on external hard drive with local SQL Server instance [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 7 years ago.

Improve this question

I have a database which gets generated from my C# code which has a connection string to my local SQL Server instance on my C drive.

The database is 150GBs in size and wont fit on my HDD. How do I create the db directly on my external HDD with my local SQL server instance?

What do you mean by install the database? This is both unclear and extremely broad. You need to add a lot of clarity for this question to be answerable. — Sean Lange
– Sean Lange, Commented May 14, 2018 at 14:45
You can specify where you want the .ldf and .mdf files on the CREATE DATABASE command. stackoverflow.com/questions/120917/… — EzLo
– EzLo, Commented May 14, 2018 at 14:49
Does seem like a bad idea, however, putting the database on an external device. If the device is missing, for whatever reason, you could have unexpected behaviour. — Thom A
– Thom A ♦, Commented May 14, 2018 at 15:41
I see that, but really don't have a choice in my scenario. :\ — Addy
– Addy, Commented May 14, 2018 at 15:45

Addy · Accepted Answer · 2018-05-14 15:35:29Z

2

I ended up taking another approach to my answer. Rather than initializing it on the external hdd itself since that wasn't an option, generate the database files with little to no data with your local instance on your local storage.

Write a script for the db you want to move

USE master; 

ALTER DATABASE db
MODIFY FILE (name='db_name'
             ,filename='E:\dbfile.mdf');

If you have a log file attached with the db, modify that as well

ALTER DATABASE db
MODIFY FILE (name='db_name_log'
            ,filename='E:dbfile_log.ldf');

Set the database offline

ALTER DATABASE db SET OFFLINE WITH ROLLBACK IMMEDIATE;

Copy the .mdf and .ldf file to the external drive and then

ALTER DATABASE db SET ONLINE;

Hope this helps somebody!

answered May 14, 2018 at 15:35

Addy

3832 gold badges5 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create database on external hard drive with local SQL Server instance [closed]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related