All posts by Sam


Constantly using ‘Save a Copy As..’ in BIDS to save a SSIS package to the server annoys me to no end. Does MSFT actually use this crap?

DTUTIL.exe is a tool that comes in your 90\DTS\binn folder. Check this out:

C:\Program Files\Microsoft SQL Server\90\DTS\Binn>

dtutil /En SQL;subfolder\packagename;5 /Fi “C:\
Documents and Settings\me\My Documents\Visual Studio 2005\Projects\ProjectName\blah\
PackageName.dtsx” /DestServer devserver

note: For the first option (/En), the subfolder does not need to include the MSDB folder which you will see when browsing SSIS.

Paste that into your cmd window and you’ll be saving with two keystrokes. Your options may change, but with the help of the online docs, you’ll be happier.

If dtutil isn’t recognized you aren’t in the proper directory. You can also add the binn directory to your PATH variable. Find the PATH variable and add “;C:\Program Files\Microsoft SQL Server\90\DTS\Binn” to the end of the string. Note the semicolon at the start.

Rebuilding a Maintenance Plan

Today I needed to rebuild a maintenance plan with specially selected databases. Anyone who has done it know that the interface for selecting databases lacks in one key area: the window cannot be resized and shows about 7 databases. My solution was to store the selected dbs for the plan in a temp table and then update the new plan with that info. Even the Green DBA knows a bit about the system tables, and isn’t afraid to play with them. That said, it’s a good idea to take a backup of the msdb database before doing anything to it.

SQL Server 2000 stores info related to dts and maintplans in the msdb. The two tables I’m concerned with are sysdbmaintplans and sysdbmaintplans_databases. There is an entry in the former for each maintenace plan, and an entry for each database used for the maintenance plan in the latter. They are associated by a guid. After knowing this, the rest is pretty easy. Definitely better than checking 80 boxes for me.

–insert database and plan id from plan we need to rebuild into a temp table
select my_db_name, plan_id into user_myDBsforOtherPlanfrom
(SELECT sysdbmaintplan_databases.database_name AS my_db_name, sysdbmaintplan_databases.plan_id as plan_id
FROM sysdbmaintplans
INNER JOIN sysdbmaintplan_databases ON sysdbmaintplans.plan_id = sysdbmaintplan_databases.plan_id
WHERE (sysdbmaintplans.plan_id = ‘{699A6A76-2BC3-4EA6-BAF1-0E268F82B095}’)) myDBsforOther

— go create your new maintplan and just select 1 db
–find the new guid and substitute in the queries below
select * from dbo.sysdbmaintplan_databases

–we need to find the plan id for the new plan and update our temp table, so they match. This could just go in the third step as a constant i suppose.

update user_myDBsforOtherPlanset plan_id = ‘{B9051EA2-F303-4E4F-BB17-FBF26E9B0C48}’

–insert the rows into the sysdbmainplan_databases table – except the one we added to the newly created plan

insert into dbo.sysdbmaintplan_databases (plan_id, database_name) select my.plan_id, my.my_db_name from dbo.user_myDBsforOtherPlan as my , dbo.sysdbmaintplan_databases notmine
where my.plan_id = notmine.plan_idand my.my_db_name <> notmine.database_name

Copying a database from production to development – a refresh

Todays challenge was to move a production database down to development nightly. It needed to happen after 4pm each weekday. Easy right?

This was all to be done in sql 2005, but the same ideas could be applied to 2000.

My plan was to create a backup at 4pm, copy it to the destination server and then do a restore.

1. Create the backup.

Normally sql will try to append date and time on the end of the name. I needed the same name all the time, so I made that happen. Saved to a local drive with the name of backup_export.bak .

2. Copy the backup.

This was a foray into unexplored territory for me. DOS seemed like a good solution – since sql server only seems to be able to back up to local drives (am I right about this?). I found the xcopy command and created a .bat file which looked like this:

cd backup\inventory
xcopy inventory_export.bak file://myserver/e$/BACKUPs/Inventory

First i change to the t: drive, where my backup is and then move to the inventory directory. The xcopy command is next which says xcopy myfile tohere
/Y means to not confirm overwriting, which is what I want. Being inexperienced I tried a few ridiculous things before using the UNC path to get straight to my server.
I scheduled this .bat file to run with windows scheduled tasks shortly after my database backup is created. The backup was small and only took 30 seconds to create.
3. Set up the restore
Created a maintenance plan with a restore task on the development server to restore the file. This is the trickyest part. We go in and modify the job that the maintenace plan creates and add steps before and after to do prep and cleanup.
First we disconnect users who are connected – otherwise it won’t restore.
Then we restore the database
Next we need to fix the sql logins which have been messed up by copying a database from one server to another:
SELECT @SQLString = ”
SELECT @SQLString = @SQLString + ‘EXEC sp_Change_Users_Login ”UPDATE_ONE”,’+QUOTENAME([Name],””)+’,’+QUOTENAME([Name],””)+’; ‘FROM SysUsersWHERE IsSQLUser = 1AND (SId IS NOT NULL AND SId <> 0x0)AND SUSER_SNAME(SId) IS NULLORDER BY [Name]

EXEC (@SQLString)
Next you need to fix any windows users which were there by granting them access. I didn’t have this problem, but you just need to do a grant for each windows user in the db.
Here is some info on doing that in 2005 sp2:
ALTER USER – addition of WITH LOGIN clause
This new syntax for ALTER USER allows remapping a user to another login, by changing the user’s SID value to match the login’s SID. This can be used to repair orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins. This should become the preferred command for fixing orphaned users. If the user is a Windows user and has a Windows user name (domain\user), then the user will be automatically renamed to the login name as part of the remapping operation. from laurentiu
As Laurentiu says, beware using sp_change_users_logon ‘report’ to find orphaned users – it only returns sql users, not windows.