Tag Archives: SQL

SQL 2012 DPM Backup Failure – The SQL Server instance refused the connection

Posted by dave on May 6, 2013 No comments

Some of my DPM Backups on a new 2012 box were failing with the error The fix can be found here and is pretty straight forward. Add the account NT_AUTHORITY\System to have sysadmin priviledges within SSMS. Once added you should receive no more …

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Posted by dave on June 20, 2012 No comments

I had this today when getting syncing a foreign DB with a UK one. The fix is pretty simple, just temporarily allow identity inserts for that table:

SET IDENTITY_INSERT tablename ON
INSERT into mytable (x,x,x) VALUES (y,y,y)
SET IDENTITY_INSERT tablename OFF

SET IDENTITY_INSERT tablename ON

INSERT into mytable (x,x,x) VALUES (y,y,y)

SET IDENTITY_INSERT tablename OFF

IIS ODBC SQL Logging

Posted by dave on January 23, 2012 No comments

I was basically after a way to make better use of the IIS log files. We use Google Analytics on most of our sites but with all the information being stored in the IIS logs, it made sense to work …

web.config Connection String Monitoring with PRTG Network Monitor

Posted by dave on December 5, 2011 1 comment

I use PRTG Network monitor to check all our websites, and I generally just check the homepage for certain words to make sure it’s loaded ok. I also have checks on the SQL Server to make sure the service is …

External MS SQL Database User Authentication with Moodle

Posted by dave on November 25, 2011 No comments

We have a Moodle running which I’ve recently moved to a new server. The old server was running PHP 5.2.x and the new one is on 5.3.x. This of course now means that the PHP extension php_mssql.dll is no longer …

Modifying SQL Server Memory Usage

Posted by dave on October 1, 2011 No comments

This can either be done from the GUI (right click on server, properties, memory) or via the following commands: Advanced Mode: EXEC sp_configure ‘show advanced options’, 1 Limit to 2GB EXEC sp_configure ‘max server memory (MB)’, 1024 Make the change …

SQL Backup and Restore Commands

Posted by dave on July 22, 2011 No comments

Some useful code snippets for backing up and restoring data in MS SQL. Create full backup: (also output times for reference) select {fn Now()} as ‘Start Time’ BACKUP DATABASE DBNAME TO DISK = ‘d:\DBNAME.bak’ WITH INIT select {fn Now()} as …

Enabling xp_cmdshell in SQL Server 2008

Posted by dave on July 19, 2011 No comments

Another snippet (Originally here):

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

-- To update the currently configured value for advanced options.

RECONFIGURE

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

-- To update the currently configured value for this feature.

RECONFIGURE

Migrating Reporting Services from SQL 2005 to 2008

Posted by dave on July 13, 2011 3 comments

I’m right in the middle of quite a large migration from one of our 2003 boxes to a fresh 2008 box. The box in question is the main SQL backend and is currently running SQL 2005, the new 2008 box …

dave.harris.net

Sysadmin and part time Gadgeteer

Tag Archives: SQL

SQL 2012 DPM Backup Failure – The SQL Server instance refused the connection

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

IIS ODBC SQL Logging

web.config Connection String Monitoring with PRTG Network Monitor

External MS SQL Database User Authentication with Moodle

Modifying SQL Server Memory Usage

SQL Backup and Restore Commands

Enabling xp_cmdshell in SQL Server 2008

Migrating Reporting Services from SQL 2005 to 2008

Categories

Sysadmin and part time Gadgeteer

Tag Archives: SQL

Tag Cloud

Categories