Backup Database to UNC

Mainly to remind me how to backup from SQL Server to a UNC, but maybe this will provide you with a quick step by step and save you some time. Details, performing a Full Database Backup of a SQL Server 2000 database to a UNC. Both the source and destination servers are in the same domain. The SQL Server Service and the SQL Server Agent Service are both started using a domain account.

  1. Set the SQL Server Server service to start as a domain account that has appropriate permissions (Read/Write) to your UNC path
  2. Run the setspn tool to register your domain user servicePrincipalName field in AD. You can use ADSIEDIT.msc console to check the status of the command. setspn MSSQLSvc/ServerName.domain.com Domain\User [see my Epic quest article for more details]
  3. Create the SQL backup job to backup the database to the UNC path: BACKUP DATABASE [dbname] TO  DISK = N’\\servername\share_name\filename.bak’ WITH  INIT ,  NOUNLOAD ,  NAME = N’backup name’,  NOSKIP ,  STATS = 10,  NOFORMAT
  4. Celebrate a job well done, perhaps with a Mt. Dew or your choice of work approved beverage.
Advertisements

SQL Server Deadlock

Only about a million articles exist on deadlocks, tracing deadlocks,etc. I thought I would add to the number and make the count million one.

From Books Online:
“A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. The SQL Server Database Engine automatically detects deadlock cycles within SQL Server…The Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.”

Man I am only a paragraph in and I am sleepy. Must be because it is Monday. Anyway I went ahead and fired up some trace flags on my instance:

DBCC TRACEON (1204,1205,3605, -1)
GO
DBCC TRACESTATUS(-1)
GO

  • Trace 1204 reports deadlock information formatted by each node involved in the deadlock.
  • Trace 1222 formats deadlock information, first by process and then by resources.
  • Trace 1205 returns more detailed information about he command being executed at the time of the deadlock. This flag is now undocumented.
  • Trace 3605 sends trace output to the error log.

You can set these trace events as start up parameters if you desire, however you will need to bounce the service to make it happen.

After I turned on the traces listed above, I fired up a SQL Server Profiler and started a profile using these Events: Deadlock Graph, Lock:Deadlock, and Lock:Deadlock Chain.

And now we wait….and wait…Wondering if a deadlock will happen. My next step is to setup my deadlock profiler trace as a server side trace so I do not have to leave profiler up and running.

UPDATE: Converted the profiler trace into a server side trace. Just hop into profiler, configure your trace, start the trace, stop the trace. Then use the Export/Script Trace Definition…under the file menu. This will generate a .SQL script to execute to fire up the trace. You will need to insert a trace file path and name. Then once you execute the script the server side trace will start.

Example of the trace definition for the above Deadlock trace:

/****************************************************/
/* Created by: SQL Server Profiler 2005             */
/* Date: 04/26/2010  08:19:16 AM         */
/****************************************************/

— Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

— Please replace the text InsertFileNameHere, with an appropriate
— filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
— will be appended to the filename automatically. If you are writing from
— remote server to local drive, please use UNC path and make sure server has
— write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N’e:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\Deadlock_Trace.trc’, @maxfilesize, NULL
if (@rc != 0) goto error

— Client side File and Table cannot be scripted

— Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 148, 11, @on
exec sp_trace_setevent @TraceID, 148, 51, @on
exec sp_trace_setevent @TraceID, 148, 4, @on
exec sp_trace_setevent @TraceID, 148, 12, @on
exec sp_trace_setevent @TraceID, 148, 14, @on
exec sp_trace_setevent @TraceID, 148, 26, @on
exec sp_trace_setevent @TraceID, 148, 60, @on
exec sp_trace_setevent @TraceID, 148, 64, @on
exec sp_trace_setevent @TraceID, 148, 1, @on
exec sp_trace_setevent @TraceID, 148, 41, @on
exec sp_trace_setevent @TraceID, 25, 7, @on
exec sp_trace_setevent @TraceID, 25, 15, @on
exec sp_trace_setevent @TraceID, 25, 55, @on
exec sp_trace_setevent @TraceID, 25, 8, @on
exec sp_trace_setevent @TraceID, 25, 32, @on
exec sp_trace_setevent @TraceID, 25, 56, @on
exec sp_trace_setevent @TraceID, 25, 64, @on
exec sp_trace_setevent @TraceID, 25, 1, @on
exec sp_trace_setevent @TraceID, 25, 9, @on
exec sp_trace_setevent @TraceID, 25, 25, @on
exec sp_trace_setevent @TraceID, 25, 41, @on
exec sp_trace_setevent @TraceID, 25, 49, @on
exec sp_trace_setevent @TraceID, 25, 57, @on
exec sp_trace_setevent @TraceID, 25, 2, @on
exec sp_trace_setevent @TraceID, 25, 10, @on
exec sp_trace_setevent @TraceID, 25, 26, @on
exec sp_trace_setevent @TraceID, 25, 58, @on
exec sp_trace_setevent @TraceID, 25, 3, @on
exec sp_trace_setevent @TraceID, 25, 11, @on
exec sp_trace_setevent @TraceID, 25, 35, @on
exec sp_trace_setevent @TraceID, 25, 51, @on
exec sp_trace_setevent @TraceID, 25, 4, @on
exec sp_trace_setevent @TraceID, 25, 12, @on
exec sp_trace_setevent @TraceID, 25, 52, @on
exec sp_trace_setevent @TraceID, 25, 60, @on
exec sp_trace_setevent @TraceID, 25, 13, @on
exec sp_trace_setevent @TraceID, 25, 6, @on
exec sp_trace_setevent @TraceID, 25, 14, @on
exec sp_trace_setevent @TraceID, 25, 22, @on
exec sp_trace_setevent @TraceID, 59, 55, @on
exec sp_trace_setevent @TraceID, 59, 32, @on
exec sp_trace_setevent @TraceID, 59, 56, @on
exec sp_trace_setevent @TraceID, 59, 64, @on
exec sp_trace_setevent @TraceID, 59, 1, @on
exec sp_trace_setevent @TraceID, 59, 21, @on
exec sp_trace_setevent @TraceID, 59, 25, @on
exec sp_trace_setevent @TraceID, 59, 41, @on
exec sp_trace_setevent @TraceID, 59, 49, @on
exec sp_trace_setevent @TraceID, 59, 57, @on
exec sp_trace_setevent @TraceID, 59, 2, @on
exec sp_trace_setevent @TraceID, 59, 14, @on
exec sp_trace_setevent @TraceID, 59, 22, @on
exec sp_trace_setevent @TraceID, 59, 26, @on
exec sp_trace_setevent @TraceID, 59, 58, @on
exec sp_trace_setevent @TraceID, 59, 3, @on
exec sp_trace_setevent @TraceID, 59, 35, @on
exec sp_trace_setevent @TraceID, 59, 51, @on
exec sp_trace_setevent @TraceID, 59, 4, @on
exec sp_trace_setevent @TraceID, 59, 12, @on
exec sp_trace_setevent @TraceID, 59, 52, @on
exec sp_trace_setevent @TraceID, 59, 60, @on

— Set the Filters
declare @intfilter int
declare @bigintfilter bigint

— Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

— display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go