Can I connect the SQL Database file from a pendrive ?

Question

Hai,
In my personal accounting software project done in c# am using sql database and sql authentication, currently my database placed in sql sever location (C:\Program Files\Microsoft SQL Server\MSSQL\Data\)
I want to change my database location to my pen drive or external hard disk for easy use in multiple systems.
I tried to attach the database file from pendrive and works perfect during first connectivity and I can not connect after just re-plug the pendrive

How can I resolve this ?,
and is there any risk if we put the database in pendrive ?


Thanking you in advance

Answer 1

Gaurav,

Would like to understand few things.

Whether you want to delete the records in a table based on the count of rows (parameter)? irrespective of which 10 or 40 records to be deleted.

Below link has the sample stored procedure to delete the records (result of search query) from the table.

https://technet.microsoft.com/en-us/library/ms189634(v=sql.105).aspx

Hope it will solve your issue.

Answer 2

Another approach you can take is schedule the deletion of rows during a time when database is not used.

Example during Night.

Then you would not need to implement any extra logic.

But use this, only if deletion can be done later.

Answer 3

CREATE PROCEDURE spDeleteRecord
@DeleteBatchSize INT,
@DelayTime DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DeleteRowCount INT
SET @DeleteRowCount = 1

WHILE (@DeleteRowCount > 0)
BEGIN
BEGIN TRANSACTION
DELETE TOP(@DeleteBatchSize) SchemaTableName;
SET @DeleteRowCount = @@ROWCOUNT;
PRINT @DeleteRowCount;
COMMIT
WAITFOR DELAY @DelayTime
END
END
GO

Answer 4

https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes