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.

Scripting SQL Agent Jobs 2005 and 2000

So I started working on a web site that would list out all the SQL Server Agent jobs for easy access. After several revisions I came up with a set of DTS packages that would hit each SQL server to store the Job list (sysjobs), Job Step Information (sysjobsteps), and Job History Information (sysjobhistory) into a single central server. From there I just created an ASP web page that allows me to see all jobs from a single source. I keep the last 10 days of history in my central repository.

So far it has been pretty handy. One cool thing about the web site is that it contains the actual command of the job step. So if I am in a pinch and I need to recreate a job for some reason, I can quickly pull the job up and check it out. Good for database restores where I do not bring the msdb database over in the restore.

I was also thinking that it would be handy to have the actual .sql script stored off in a location so I could actually just run a script to recreate the jobs. Handy for those servers that have two dozen complex jobs. Not such a big deal for servers that have a single backup job, but to recreate two dozen jobs on a server would take some time.

SQL Server 2000: Just browse down the the SQL Agent in Enterprise Manager and right click on Jobs…Choose the All Tasks option and Generate SQL Script. This will let you generate a single SQL Script for all the jobs. I just store this SQL off on a separate server. I run this periodically, especially when jobs are added/deleted, etc.

SQL Server 2005: Thanks to the helpful tip here: http://www.sqlservercentral.com/Forums/Topic452319-146-1.aspx from Adam. You can just click on the jobs folder under SQL Server Agent, then on the right hand side in the Object Explorer Details you can highlight all the jobs and then Right Click, Script Job as…Create To…File, or New Query Window.

So now I have the web site to see a quick view of all my jobs and I also have the .sql Script to actually build the jobs if necessary. All of this to ease the recovery steps.

OSQL dump to Text

At times you need to dump the results of a stored procedure call to a text file. To me the simplest way to achieve this is a call to the OSQL command line utility.

osql -E /Q “exec master..sp_help_revlogins” -o c:\GenLogins.sql

This little gem will use the sp_help_revlogins (More info here) to dump the SQL logins to a text file. Handy to run for recovery purposes. The resulting script can be used to quickly recreate logins after a restore.

Backup script with datestamp

Below is a script you can use to backup a database and include a date and time stamp on the file name.

DECLARE @backdate varchar(20)
set @backdate = (select REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 100),’:’,’_’),’ ‘,’_’))
exec (‘BACKUP DATABASE [DatabaseName] TO DISK = N”c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\databaseName_’ + @backdate + ‘.bak” WITH INIT , NOUNLOAD , NAME = N”Database Name backup”, SKIP , STATS = 10, FORMAT’)

The date and time stamp will look like this: Dec_14_2009_12_00PM
You can use this to append on the file if you need to keep multiple copies of your .bak file.