July 13, 2007 at 10:05 am
Does anyone know if it is a good/bad idea to restore the msdb database to another server.
Reason i ask is that i am doing a server move soon and think this will speed things up as in keeping all the jobs, DTS packages, custom alerts and operators and wondered if anyone has had any experience of doing this.
thanks in advance.
July 13, 2007 at 10:49 am
Yes, I have done this but do not recommend using this approach.
Use Enterprise Manager to create SQL scripts for jobs, alerts and operator by just right clicking the appropriate icon in the folder view (it is under "all tasks")
For DTS packages, DTSBackup 2000 works well and is available at http://www.sqldts.com/242.aspx. Often you will find that the SQL Server name has been hardcoded, so a mass save to a *.dts file and then manually open, change, and then resave to the file will be needed.
Maintenance plans cannot be transfered and if you use the backup/restore method, the maintenance plans will be a problem especially with clusters.
The backup/restore method has the advantage of retaining the job, alert, backup, and restore history but you will need to update sysjobs.dbo.originating_server to the new server name.
SQL = Scarcely Qualifies as a Language
July 13, 2007 at 10:53 am
Thanks Carl, that's the kind of advice i was after.
July 14, 2007 at 11:21 pm
Yes as Carl suggested its better to scripts jobs, schedules, etc and then run against the server you want rather than restoring the msdb database. You will need to take few precautionary steps before you do one of that kind.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 17, 2007 at 4:37 am
Transferring DTS packages can be done from the source server from sysdtspackages table taking only the max of createdate column which will avoid the older version of the packages and transfering the data (resultset) to the destination sysdtspackages table.
Which will copy all your DTS packages from the source easily. Can query the below.
Just create a Linked server (destination) on the source.
insert into sysdtspackages
SELECT rtrim(T1.name), T1.id, T1.versionid, T1.description , T1.categoryid, T1.createdate, T1.owner, T1.packagedata, T1.owner_sid, T1.packagetype
FROM <SERVERNAME>.msdb.dbo.sysdtspackages AS T1
INNER JOIN (SELECT [name], [id], MAX([createdate]) AS [createdate]
FROM <SERVERNAME>.msdb.dbo.sysdtspackages GROUP BY [name], [id]) AS T2
ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate].
Murali.a
July 17, 2007 at 5:37 am
If you move DTS by this method make sure that you go and change the connection properties of the each DTS package else they will be pointing to the older server and either may fail or will lead to data corruption. If you have lot of DTS packages see if there is a better option.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply