Tag: MSSQL

  • MSSQL Backup all Non System Databases Scripted

    Microsoft SQL Server backups, MSSQL Backup, process Note: You may need to go to Tools > Options > Query Results > SQL Server > Results to Text in the Studio Manager and change the “Maximum number of characters displayed in each column to 1024” (default is 256). Also make sure you are outputting the results…

  • Find the Default File Paths for a Given SQL Instance

    Method 1 of 1? — Check if temp database exists — Tempdatabase is used for determining the default database path –if the zztempDefaultPath db exists drop IF EXISTS(SELECT 1 FROM [master].[sys].[databases] WHERE [name] = ‘zzTempDBForDefaultPath’) BEGIN DROP DATABASE zzTempDBForDefaultPath END; — Create temp database. Because no options are given, the default data and — log…

  • Create the alias with cliconfg.exe

    Option 2: Create the alias with cliconfg.exe Log into the Application and/or front-end Web servers Got to Start > cliconfg.exe (Note: it’s c-l-i-c-o-n-f-g-dot-e-x-e) Then click on “Alias”: If you already have an alias set up, it will show up there. Otherwise… Click “Add” Specify the port number and give it a name. That’s it! Test…

  • Exporting/Importing (Migrating) All Non System MSSQL Databases

    I want all DB’s moved, from SQL Server/Instance A to SQL Server/Instance B.  The easiest way to do this of course, is scripted :D.  Oh how I love the idea of automation. Generate export script: use master GO select ‘ BACKUP DATABASE [‘ + [name] + ‘] TO DISK = N”C:\RESTORE\PATH\’ + [name] + ‘.bak”…

  • SQL Server FAQ

    I wanted a place to throw a lot of the high level SQL FAQ and answers for issues I come to. v=sql.70 7 v=sql.80 2000 v=sql.90 2005 v=sql.100 2008 v=sql.105 2008R2 v=sql.110 2012 v=sql.120 2014 v=sql.130 2016 This site lists build versions, this is really useful for matching version to version by running the updates…

  • Easily test connectivity to MSSQL Server

    I have run into a number of cases where I want to test a connection to a SQL (MSSQL) Server without having to resort to code. To do so is rather straight forward: Create a new file, a simple txt file is fine, and rename the document to “TestDBConnection.udl” sans quotes and spaces. Be sure…

  • Adding the Administrators group within SQL Server

    If you want to add the group Administrators to SQL so that any user of the Administrators Group can use SQL server, the below code, once executed, will perform this for you: CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’ GO

  • Determining Microsoft SQL Server Version

    Open up SQL Server Management Studio and connect to the Database Engine (connection not pictured) Right click on the instance and choose Properties from the menu. Here you can see the Product and the Version, as both are important details. If you are noting this for future reference  I recommend noting the entire line of each.…

  • Calculating Disk Space in Use by All Databases within a Given MSSQL Instance

    If you are trying to calculate total disk space in use by all databases within a given MSSQL instance, you can use the following script to determine this space. SELECT CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) As UsedSpace FROM master.sys.master_files It has been said that “Sys.Master_files is a server-wide view that lists every file in…

  • Update the Schema on a Stored Procedure

    To update the schema of a single stored procedure, once on the database in question, you would need to run the following line: ALTER SCHEMA destinationSchema TRANSFER sourceSchema.storedProcedure;