I used to get really nervous about renaming database files, which I think is an appropriate response. Changing the attributes of the database’s physical objects does sound a bit scary on the surface, but once you’ve done it a few times you’ll realize it’s easy to do, and easy to fix if you make a mistake. This simple script template shows the basic steps of the process.
-- USE [master];
--Change logical file names.
ALTER DATABASE [My_Database]
MODIFY FILE(NAME = [old_name_data], NEWNAME = [new_name_data]);
ALTER DATABASE [My_Database]
MODIFY FILE(NAME = [old_name_log], NEWNAME = [new_name_log]);
--Disconnect all existing sessions.
ALTER DATABASE [My_Database] SET SINGLE_-- USER WITH ROLLBACK IMMEDIATE;
--Change database to OFFLINE mode.
ALTER DATABASE [My_Database] SET OFFLINE;
STOP! NOW RENAME THE PHYSICAL FILES ON DISK
--Point databases at new phyisical files.
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_data',
FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_data.mdf');
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_log',
FILENAME = 'L:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_log.ldf');
--Change database to ONLINE mode.
ALTER DATABASE [My_Database] SET ONLINE;
--Reopen connections to all users
ALTER DATABASE [My_Database] SET MULTI_-- USER;
I was trying to test an install of R in SQL Server 2016 and when running a script I received this error: Fatal error: cannot create ‘R_TempDir’
Following the instructions here, I enabled external scripts, restarted the sql server service, and then tried to run the following test script:
exec sp_execute_external_script @language =N'R',
@input_data_1 =N'select 1 as hello'
with result sets (([hello] int not null));
This is when the fatal error occurred. As the error suggests, R is having some issues creating a temporary directory. After some internet searching and trial and error I got past the issue.
Enable 8dot3 File Names
R configuration uses the 8dot3 file name convention, also known as “short names”. To enable this on windows 10, run the following command in CMD (command prompt):
fsutil.exe behavior set disable8dot3 0
For more options and information look here: https://support.microsoft.com/en-us/kb/121007
Give access to the working directory to R
Locate and open “rlauncher.config” file in a text editor. This file will be under the “<sqlserver_instance>\binn” directory. Take a look at the location of WORKING_DIRECTORY. This should have a “short name” file path. The path should be something like “<sqlserver_instance>\EXTENS~1”, and “\EXTENS~1” is equivalent to “\ExtensibilityData”. We need to give access to R to this folder. I did this by changing the permissions to full control to everyone. You may want to be more restrictive here, but in my case this did not matter.
- Right click folder > Properties > Security tab > Advanced > Add
- Select a principal (I entered “Everyone”)
- Tick “Full control” under basic permissions and click “OK”
- Tick “Replace all child object and permissions entries with inheritable permissions entries from this object” and click “OK”
Now if you rerun the script above you should get a result of “hello, 1”.
If you are building some code that would be different depending on the version of SQL Server then it may be useful to check the SQL server version using T-SQL instead of going through the object explorer in SSMS. For example, I have some code that checks for SSIS package execution status across multiple servers and the database design is slightly different between server versions so I need to check the version and then execute the proper block of code.
--CHECK SQL SERVER VERSION
'SQL Server Product Version' =
CASE LEFT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(128))) - 1)
WHEN '7' THEN 'SQL Server 7'
WHEN '8' THEN 'SQL Server 2000'
WHEN '9' THEN 'SQL Server 2005'
WHEN '10' THEN 'SQL Server 2008/2008 R2'
WHEN '11' THEN 'SQL Server 2012'
WHEN '12' THEN 'SQL Server 2014'
ELSE 'Unsupported SQL Server Version'
A list of servers can be obtained using windows command prompt (cmd) or powershell (ps). CMD and PS both provide Server Name and Instance Name, however, powershell provides two additional fields: IsClustered, and SQL Server Version. One challenge I have noticed is that running these commands from my local machine only returns a short list with names that look like computer names and not server names. I had to run the commands from a production server, a QA server, and a development server to obtain the server lists from those environments. There must be some network separation between them, which I am still investigating.
CMD: OSQL -L or SQLCMD -L