I Can’t Get My SQL Database to Backup to Another Server on the Network

We ran into a problem while trying to backup our development and user testing databases to a data storage share on another server in the network. We checked and double checked location information, the backup plan, user roles and permissions, and so forth and couldn’t find the solution. The answer has to do with shared folders.
Because the location for the backup files in on a remote server, the following must be completed for the maintenance plans to run correctly:
1. The File storage folder on the remote servers must be a shared folder.
2. To create the file storage folder, you must log on remotely to storage share and create the shared folder there. You cannot create the shared folder from another box.
3. You must add both a logon for backup job and a logon for the hardware box the data is coming from to the shared folder permissions.
4. Obtain the log on for the hardware box the data is coming from by remote logging onto the box and checking the Administrative Tools>Services > SQL Server Agent>Properties>Log On for the instance of SQL that is being backed up. CTRL+C this to the clipboard.
5. Remotely log on to the server receiving the backup and navigate to the shared folder created in step 2. Open the Properties on the shared folder and go to Sharing tab>Permissions button. Add the log on for the box obtained in step 4 and the log on used by the Maintenance Plan job (the sql admin account). Save and log off the storage share.
Run a test backup and check the shared folder to see if the BAK, TRN or other file is there.

*I take absolutely no credit for finding this solution. Thanks to the team.

Backups Quick Info From MSDB

I needed to find out if a backup was done, but didn't have the full name of the database because they all have similar names on that server. Here's script to figure that out fast.

select * from msdb.dbo.backupfile
Where logical_name like'%DATABASESEARCHTERM%'


--Where it says DATABASESEARCHTERM just put in something you think is close.

Transact-SQL to Create, Add Entries To, and Update a Table

Today we realized we need to change the entries in a reference table because the .ASPnet developers need to call one of the categories in the reference table something else. You can make entries right in the tables in SQL but its a better practice to script out such changes.

Well, I had to figure out how to do that because I've never had to change just one record column entry string into something else. Here's my practice on that.

--first I created a sample table to test against--

create table Managers
(FirstName varchar (100) not null,
LastName varchar (100) not null,
OfficeLocation varchar (50) null)

--then I inserted some content--

insert into Managers (FirstName, LastName, OfficeLocation)
values ('Jason', 'Peabody', 'New York')

insert into Managers (FirstName, LastName, OfficeLocation)
values ('Heather', 'Ace', 'Burbank')

insert into Managers (FirstName, LastName, OfficeLocation)
values ('John', 'Grass', 'Burbank')

select * from Managers


--now I want to go back and change all entries for Burbank to Beverly Hills because we've leased a new building and everyone moved into it this week. First I run a select all to see how many records there are to change.--

SELECT *
FROM Managers
WHERE OfficeLocation = 'Beverly Hills'

UPDATE Managers--the tablename
SET OfficeLocation = 'Beverly Hills'--the new entry
WHERE OfficeLocation = 'Burbank'--the old entry

select * from Managers --check it

XML Assembly Won’t Work – Possible Fix

One thing I forgot to mention on my entry about the XML assembly is that CLR integration must be turned on the SQL server. To turn on CLR:
Connect to the SQL Server

Go to SQL Server 2005, Configuration Tools.

Go to the Surface Area Configuration/Surface Area Configuration for Features.

Click on the box to Enable CLR

(And while you are in there also enable the xp_cmdshell. You will probably need it later.)

How to Find Which Service Pack SQL Server is Running

This is pretty simple:

SELECT @@VERSION
GO

Hey, that was easy.

Connection Failure When Setting Up Named Instances on Remote Servers

Here's an interesting thing I learned today.

We set up a named instance of SQL on our development box. The install was accomplished easily however, afterward we could only connect to the new instance NEWSQL directly from the hardware box. We could not get a connection to NEWSQL via SQL Server Management Studio. The NEWSQL instance didn't appear in SSMS through browsing for it etc. This problem persisted even though we were logged in as a system admin.

Here's is what caused the problem:

When a default instance of SQL is created on a box, the port is automatically set to static port 1433. But when named instances are created, the installation assigns the instance to dynamic ports.

In order for the instance to be "seen" and by SSMS and other applications you must go into the server box, open Adminstrative Tools, then Services.

Then click on the SQL Browser Agent and change the status to enabled. The Browser is the component that will find and track SQL instances on dynamic ports.

Adding Extended Properties Using the System Stored Proc

I'm working on adding extended properties to our database project. There are so many tables with huge numbers of columns and very few descriptions.

I found some code on the web for using the sp_addextendedproperty to extended properties without having to open each table or objectt. Its still tedious but is saving me about 30% on the time.

Here's the code snippet:

USE [NameOfDatabase]
GO

--Script to add an Extended Property to the Table
EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPERTY1',
@value=N'Used by CRM to support Account, Contact, Opportunity, Lead Entity Model, and Service Entity Model.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPRTY2',
@value=N'Created by CRM application.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPERTY3',
@value=N'Used by multiple entities.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

--COLUMN SECTION STARTS HERE
--Script to add an Extended Property to a columnEXEC EXEC sys.sp_addextendedproperty
@name=N'EXPROPERTY_Column',
@value=N'Uses as a FOREIGN KEY constraint.',
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase',--Table Name
@level2type=N'COLUMN',
@level2name=N'OwningBusinessUnit'--Column Name
GO