Backing Up Data Transformation Packages 1

[Skittle]

I have an SQL server box on which I have created a large data transformation package. I want to make sure it is backed up in case of disaster.

I have looked through a few manuals and it looks as if I need to backup the msdb database to achieve this.

I have set up a scheduled job to backup the msdb database and have verified it using 'RESTORE VERIFYONLY'.
I would now like to test restoring the msdb.

The problem I have is that when I execute the restore from the Query Analyser, I get the error:-

Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.


This is a test server and I am the only one on it trying to execute the restore. Do I have to put the server in a special state or something to be able to restore the msdb database?


The backup command:-

BACKUP DATABASE [msdb] TO DISK ='TEST_BU1.BAK' 
    WITH INIT,
    NOUNLOAD, 
    NAME='msdb test backup' 
    NOFORMAT;

The restore command:-

RESTORE DATABASE [msdb] FROM DISK ='TEST_BU1.BAK' 
    WITH NOUNLOAD, NOREWIND;

Dazed and confused
(N+, MCP, MCAD)

 

[JayKusch]

Maybe another way to save your DTS packages is to save them out as a Structured Storage File. This backs up your DTS package to an OS file, (i.e. a file on your hard drive). Then you can save it for tape, CD, thumb Drive ect.

As for the backup, WATCH WHAT YOU ARE DOING!!! The MSDB not only holds your DTS package but other objects like jobs and such. Restoring an older backup file will over-write/drop any new job or DTS package that is created.

If you still want to restore it, open Enterprise Manager and drill down to Management..Current Activity..Process Info. IN there you can see what SPIDs(connections) are attached to the MSDB database. Kill these SPIDs "carefully" by right-clicking on them and choosing "Kill Process"

Thanks

J. Kusch

 

[Skittle]

Hi,

yuop don't worry. I understand the implications of restoring the msdb. I'm just doing it on a test box to prove to myself I know how to do it in an emergency.

I spotted your other posting on saving to a structured storage file just after posting this problem ( doh! ). I am fairly new to SQL server. Once you have a structured storage file, how do you load it again to be a usable data transfomation package definition? I assume it's an XML file or something?


Dazed and confused
(N+, MCP, MCAD)

 

[JayKusch]

Not sure of the exact internal structure of a SSF but I am sure on how you bring them back in.

Same process as opening a .doc, .txt or .xls file. In this case you are opening a .dts file within the DTS designer tool. You can the resave it in the MSDB by saving it once again in a SQL Server format.

This is the preferred manner to move DTS packages from test to QA to Production systems.

Thanks

J. Kusch

 

[Skittle]

Thanks dude. I understand the process now.

Much appreciated.



Dazed and confused
(N+, MCP, MCAD)

 

[Skittle]

Hmm. Still haven't got past my msdb restore error message though.

Any other ideas what might be preventing the restore?
I've killed all spiv's but one that is using msdb won't go away. It's some sort of performance counter thingy:-

EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters




Dazed and confused
(N+, MCP, MCAD)