Error Restoring MSDB to another machine

Question

Error Restoring MSDB to another machine

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 20, 2012 at 6:09 am

#249526

I tried restoring the MSDB to another machine with the following code"
RESTORE DATABASE MSDB
FROM DISK = 'msdb_backup_201201181321.bak'
WITH REPLACE,
MOVE 'MSDB' TO 'D:\SQLServer\Data\MSDB.mdf',
MOVE 'MSDB_log' TO 'D:\SQLServer\Log\MSDB.ldf'
I get the following error:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_backup_201201181321.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Any ideas?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Zarko Jovanovic Right there with Babe Points: 732 More actions · Answer 1

Welsh Corgi (1/20/2012)
I tried restoring the MSDB to another machine with the following code"
RESTORE DATABASE MSDB
FROM DISK = 'msdb_backup_201201181321.bak'
WITH REPLACE,
MOVE 'MSDB' TO 'D:\SQLServer\Data\MSDB.mdf',
MOVE 'MSDB_log' TO 'D:\SQLServer\Log\MSDB.ldf'
I get the following error:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\msdb_backup_201201181321.bak'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Any ideas?

it's 1 or 2:

1: file msdb_backup_201201181321.bak is NOT in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ directory

or

2: SQL Server Service account does not have rights on the file system to read it

george sibbald SSC Guru Points: 104210 More actions · Answer 2

you haven't specified the full path to the backup so SQL is presuming its in the default directory. I presume its somewhere else.

Are you trying to move the database files to where the msdb currently is or to somewhere new?

---------------------------------------------------------------------

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

george sibbald (1/20/2012)
you haven't specified the full path to the backup so SQL is presuming its in the default directory. I presume its somewhere else.
Are you trying to move the database files to where the msdb currently is or to somewhere new?

Stupid mistake.

I have been multitasking and I goofed...:blush:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I fixed my typo and attempted to restore but because the machine that I made the backup was not at SP4 and the target machine was I could not restore.:w00t:

I'm trying to remember how I scripted out maintenance plans.:unsure:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

george sibbald SSC Guru Points: 104210 More actions · Answer 5

Unless you have a lot of maint plans I wouldn't bother scripting them out, they are very server specific.

IF you have a DR setup for a server supporting multiple databases your best bet would be to move away from maintenance plans and use scripts via SQL agent jobs, they can be easily scripted out.

---------------------------------------------------------------------

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

ok sound good. I will not worry about scripting out the maintenance plans.

I create them last week to perform backups, etc and it did not take me very long to create them.

There is this one job however that I did not set it up but it runs a SSIS Package with different configurations and information on just about every tab.

I'm not responsible for it but it would be nice to script out and deploy to the new server.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

george sibbald SSC Guru Points: 104210 More actions · Answer 7

You can script that job out and edit server details. you just need to separately deploy the SSIS package itself

---------------------------------------------------------------------

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

george sibbald (1/20/2012)
You can script that job out and edit server details. you just need to separately deploy the SSIS package itself

Thanks, what are the steps?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

george sibbald SSC Guru Points: 104210 More actions · Answer 9

you store your packages in the filesystem don't you?

---------------------------------------------------------------------

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

george sibbald (1/20/2012)
you store your packages in the filesystem don't you?

I take it that you are going to tell me that it can't be done if the SSIS PAckages are stored in the File System?

The Packages are dynamically generated nightly from two databases.

Computer Science Corporation owns then we have a license.

Of course this is going away as we are pulling the plug on tier system.

I'm not going to worry about it for it is tier responsibility.

I store mine in MSDB but not on this Server.

Thanks!

The

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

george sibbald SSC Guru Points: 104210 More actions · Answer 11

Welsh Corgi (1/20/2012)
george sibbald (1/20/2012)
you store your packages in the filesystem don't you?
I take it that you are going to tell me that it can't be done if the SSIS PAckages are stored in the File System?

No, not at all, just something I remember from another thread with you. As they are stored in the file system you just need to ensure they are copied to an identical location on the other server.

If you have a script to script out jobs use that for the SQLagent job, otherwise do it vis SSMS, then run it into the DR server having edited any hardcoded server references.

---------------------------------------------------------------------

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

The SSIS Packages are dynamically generated every night based on the values in two Databases.

I really don't have to worry about it because CSC owns this process but thanks for the tips,:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/