How to move SQL database files to a different location

Sometimes when SQL database files (.mdf and .ldf files) grow too large, the easiest solution might be to move them to a different location so we can free up disk space quickly.

Here are the steps to move these files:

1. Turn off all services or processes that are using the database

2. Note down the file locations of the .mdf and ldf files (by default, they are located at SQL Server application directory e.g., \\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA

3. Launch SQL Management Studio

4. Login and access the SQL server instance has the database

5. Click and highlight the database

6. Open Query Editor

7. Run following SQL script (please change the database name and location to suit)

1 2 3 4 5 6 7 8 9

ALTER DATABASE AdventureWorks2014       MODIFY FILE ( NAME = AdventureWorks2014_Data,                     FILENAME = 'E:\New_location\AdventureWorks2014_Data.mdf');   GO   ALTER DATABASE AdventureWorks2014       MODIFY FILE ( NAME = AdventureWorks2014_Log,                     FILENAME = 'E:\New_location\AdventureWorks2014_Log.ldf');   GO

8. Run following SQL script to set the database to be offline

1 2	ALTER DATABASE AdventureWorks2014 SET OFFLINE;   GO

Note: if the database is too large and this statement may take a while to run. Or you can use the following statement instead.

1 2	ALTER DATABASE AdventureWorks2014 SET OFFLINE WITH ROLLBACK IMMEDIATE;   GO

9. Browse to the files location (we noted down in Step 2) and move the files to the new location

10. In Query Editor, run following SQL script to bring the database online again

1 2	ALTER DATABASE AdventureWorks2014 SET ONLINE;   GO

Note: if it fails with permission error, please change the user who runs the SQL services. You may do that by following the steps below via Services application.

1. Right click on the SQL Server service e.g., like the one highlighted in the screen shot below

2. Choose Properties option

3. Click Log On tab to change to the ideal user as required

OR

You may also grant access permission to the SQL Server service user on the new folder, where the database files are.

11. You may verify if the database is made online successfully by running the following statement

1 2 3 4

SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus FROM sys.master_files   WHERE database_id = DB_ID(N'AdventureWorks2014')   GO

This article is originally sourced from this one and you may find more details there. Thank you. 

A safe way to convert string to date time in SQL

Over the years while working with SQL Server, I found a safe way of converting string to date time value to be used in SQL. It will have zero risk of being parsed in WHERE clause or cause any performance issues.

Examples:

Convert to DATE type value

SELECT {d '2021-03-01'}

Convert to DATETIME type value

SELECT {ts '2021-03-01 06:10:00'}

SQL server database collation explained

When we installing a new SQL server instance, we have to choose a database collation. This will be the default collation for all databases that going to be created on this instance later on.

However, this default database collation can be overridden in following ways.

1. When creating a new database, we can specify a different collation for the database.

For example

CREATE DATABASE DbWithNewLanguage COLLATE SQL_Latin1_General_CP1_CI_AS

2. When creating a new table, we can specify different collations for individual column.

For example

CREAT TABLE MixLanguageTable (
ID INT IDENTITY,
EnglishName nvarchar(150) COLLATE Latin1_General_CS_AS,
GreekName nvarchar(150) COLLATE Greek_CS_AS_KS,
JapaneseName nvarhcar(150) COLLATE Japanese_90_CI_AS_KS_WS
)

3. When querying the tables, we can specify different collations.

For example

--EnglishName column in the example above is case sensitive. But we can change the collation so when we query the table on this column, we can set it to be case insensitive.

SELECT * FROM dbo.MixLanguageTable WHERE EnglishName = ‘test name’ COLLATE Latin1_General_CI_AS

Things to know about TRY...CATCH in T-SQL

With TRY…CATCH statement in T-SQL, I attempted to use it for a table name resolution the other day. However, I found the statement will not handle following errors, so it will not work if we are using this statement for checking the table name if it exists or not.

1. Compile errors, such as syntax errors, that prevent a batch from running.
2. Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Meanwhile, when using SET XACT_ABORT ON setting, if a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. However, this will not help if we want to resolve the “table name does not exist” error.

Reference:

http://stackoverflow.com/questions/15015051/sql-try-catch-doesnt-catch-an-error-when-attempting-to-access-a-table-that-it

String type parameter in Crystal Report imposes a critical security threat

Recently, we discovered the string type parameter used in Crystal Report is open for script injection i.e., a regular expression can be sent via these string type parameters and ran against the system database directly. But this only happens when the string is used with LIKE keyword in the SQL query.
For example, the query may look like this:
SELECT ComanyName
FROM Customers
WHERE CompanName LIKE {?CompanyParameter}
We have solved this issue by eliminating the special characters in the string which may impose threat before we send the string to Crystal Report. Hope this can be done in the future in Crystal Report itself and to prevent this major security threat from happening to any of the Crystal Report users.

Daylight saving offset is not available in SQL scripting

We discovered a drawback by using SQL script and Crystal Report where the conversion against all the GMT date time fields in our database to local time does not cater for the daylight saving offset.

Unfortunately, the only way we found to overcome this issue is to create a new table which will record all the daylight saving information at different locations. Therefore, when we build Crystal Report, we can use the SQL query to check the daylight saving offsets and apply them correctly on different dates.

A google search link indicates that Crystal Report may have this function in place in version 2011.