restore to new db restores less data than what is in the source backup file

I am trying to create sql code that restores a backup of a master database to a new database on the same server. It seems to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!



Any and all help is sincerely appreciated.


The master databases from which the backups are made start with MODTRNMaster

The databases which are created from the restores start with M1_ and M2_. (We call them training room databases.)



My script for backing up the master databases:



-- Backup the master training database

backup database MODTRNMaster
to disk = 'f:\bkup\MODTRNMaster.bak'

backup database MODTRNMaster_IMG
to disk = 'f:\bkup\MODTRNMaster_IMG.bak'

backup database MODTRNMaster_MNC
to disk = 'f:\bkup\MODTRNMaster_MNC.bak'

backup database MODTRNMaster_VM
to disk = 'f:\bkup\MODTRNMaster_VM.bak'

go


This is the restore script for restoring the first training room databases. Im hoping that there is just something simple that Im overlooking in these restore statements! J



-- Restore the backup of the master training database into the

-- training room #1 database.

use master
go

drop database M1_MSLH
go

restore database M1_MSLH
from disk = 'f:\bkup\MODTRNMaster.bak'
with move 'DEV5_Data' to 'f:\mssql\data\M1_MLSH.mdf',
move 'MM' to 'f:\mssql\data\M1_MLSH_1.mdf',
move 'AMB' to 'f:\mssql\data\M1_MLSH_2.mdf',
move 'DM' to 'f:\mssql\data\M1_MLSH_3.mdf',
move 'IMM' to 'f:\mssql\data\M1_MLSH_4.mdf',
move 'ED' to 'f:\mssql\data\M1_MLSH_5.mdf',
move 'DEV5_Log' to 'f:\mssql\log\M1_MLSH_log.ldf',
recovery

go



Thanks in advance

:eek:

:eek:that surely sounds like some magic. I do not know off the the top of my head what the problem could be but you should only have one mdf file and the rest of your data files should be ndfs.|||I am trying to create sql code that restores a backup of a master database to a new database on the same server. It seems to run correctly as no errors are produced. However, the most recent updates to the master database are not present in the new databases. All databases are using the Full recovery model. What is really strange is that if I do (what I think is) the same function in Enterprise Administrator, the restore works fine! For both methods I used the same backup file!

Are you sure that the master database is in full recovery mode? I did not think that was possible and/or mattered.

Also, even if it is in full recovery, your restore script indicated that you had restored only the last full backup (.BAK) file and had not restored any transaction logs (.TRN). Without restoring the transaction logs, you will only restore the database to the point in time when the last full backup completed; any transacations performed after that will not get restored.

Finally, I have to ask: why are you restoring the master database? I'm not sure that doing so gets you anything (except maybe practice).

Regards,

hmscott|||sorry. I should have been a little clearer. :o It is not actually THE master database that I'm backing up. We are having some training sessions for users of a new system. The trainer makes his updates in a database named MODTRNMaster (the master database for our purposes). At night I am restoring into 2 individual training databases M1_MLSH and M2_MLSH that the users are accessing.

No transaction log backups are being done. These databases are only being accessed during the day. At night there is no activity in those databases. At night I backup MODTRNMaster and then immediately (attempt to) restore the backup file as M1_MLSH and then again as M2_MLSH.|||You need to add with init to your backup commands, or with file = # to the restore commands. By default, SQL Server will append your backup to the exisiting file (you may have noticed them getting bigger), and restore from the first backup found. Do this and get back to us with the results:

restore headeronly from disk = 'f:\bkup\MODTRNMaster.bak'|||Thanks So much for your suggestion! Adding the "init" parameter to the backup database statement was exactly what was needed!! :D

Restore Replicated Database

I seem to have run into a problem within an environment I have set up with
replication.
There is an environment called STAGING where releases and changes are tested
before being moved into production. This database is an exact copy of
production. We also have replication set up in this environment. It should be
known they are different servers.
Here's the problem: STAGING is updated by taking a backup of production and
doing a restore in the environment. I know that restoring a non replicated
database over a replicated database will break replication because of the
loss of the metadata.
Is it possible to restore both the distribution AND the replicated database
in order to preseerver replication in STAGING? Or does the fact there are two
different server names preclude us from being a ble to do a restore at all.
I've also considered generating SQL Scripts of our staging environment's
replication topology and just running those scripts after each restore.
Thank You!
Is
This is correct - the different server names preclude a successful restore
of the replication settings. The easiest solution is to use the replication
script generated on the origional server and search and replace on the
servername. You might also have to change the job owners if they have not
been set to sa.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'm going to create a job that will run after the restore has completed to
recreate the publicaion and subscriptions.
Will the fact that the distribution database is "different" than the
database in production, will recreating the publication on the other server
still work? The articles and everything will still be thae same.
"Paul Ibison" wrote:

> This is correct - the different server names preclude a successful restore
> of the replication settings. The easiest solution is to use the replication
> script generated on the origional server and search and replace on the
> servername. You might also have to change the job owners if they have not
> been set to sa.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||As long as you change references to the server (eg @.publisher, @.subscriber),
job owners if necessary, the repldata path if you are setting up the
distributor, the distribution database path also if it is to be on a
different disk etc then the rest of the script is constant. This sounds a
lot but it is pretty straightforward really. In some circumstances you might
want different agent profiles on each server and this is also something to
be aware of.
HTH,
Paul Ibison, SQL MVP
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...[vbcol=seagreen]
> I'm going to create a job that will run after the restore has completed to
> recreate the publicaion and subscriptions.
> Will the fact that the distribution database is "different" than the
> database in production, will recreating the publication on the other
> server
> still work? The articles and everything will still be thae same.
> "Paul Ibison" wrote:
|||Paul:
Here's kind of what I've come up with - it stinks but it seems to be the
only way to guarantee everything will come back up with as little fuss as
possible.
I'm going to generate the DROP / CREATE SQL for both the publications and
the subscription. For the subscription script, I don't think I'll need to
generte the SQL for the jobs - or will I?
Once the restore of the database has taken place, I'll then run a script to
DROP the publication and subscription. I'll then run the script to CREATE the
publication, create the subscription, then start the snapshot agent.
Essentially I'm recreating the entire replication topology from scratch,
which kinda sucks.
Have I missed anything?
Thanks!
"Paul Ibison" wrote:

> As long as you change references to the server (eg @.publisher, @.subscriber),
> job owners if necessary, the repldata path if you are setting up the
> distributor, the distribution database path also if it is to be on a
> different disk etc then the rest of the script is constant. This sounds a
> lot but it is pretty straightforward really. In some circumstances you might
> want different agent profiles on each server and this is also something to
> be aware of.
> HTH,
> Paul Ibison, SQL MVP
>
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:B09551B0-3147-470C-9A8C-4A157D1D3A98@.microsoft.com...
>
>
|||Hi there,
We do this backup/restore process all the time from our production
enviroment to our test enviroment. We are running SQL server 2000 with
merge replication with 3 puplications...
each time we restore the database and use the scripts from the
production envrionment to set up the publications again.
The problem we are having is with identity ranges. When we republish the
restored database in the test environment and try doing any inserts on
the server we get the identity ranges full message and run the
sp_adjustpublisheridentityrange sp to fix the problem. When we do this
sometimes it works in that we can do inserts at the server and other
times it doesnt. I've come across references to restarting the merge
agent.. does this mean after i run the stored procedure we need to
stop/re start the SQL agent on the server to get the identity ranges
back in sync or does it mean syncing one of the subscriber databases?
i guess what we're looking for is a consistent way of checking the
identity ranges - and then a consistent way of setting them... on re
published databases. This is causing us serious grief as one of our
app's in test is to merge duplicate entries on our system and it keeps
falling over in test due to the indentity ranges filling up...
Any help would be greatly appreciated -
Cheers,
Michael
*** Sent via Developersdex http://www.codecomments.com ***
|||Michael:
Would it be an imposition if I were to ask you to outline your procedure for
doing your restore from a production environment?
I'm just kind of looking for a template to work from. I've tried a couple
different ways and still run into the occasional issue.
What we need to implement is a "sure fire" way to perform the refresh. The
process will need to be automated, so it has to be fairly bullet proof.
Any insight would be greatly appreciated!!
Thanks in advance...
"Michael McGoldrick" wrote:

> Hi there,
> We do this backup/restore process all the time from our production
> enviroment to our test enviroment. We are running SQL server 2000 with
> merge replication with 3 puplications...
> each time we restore the database and use the scripts from the
> production envrionment to set up the publications again.
> The problem we are having is with identity ranges. When we republish the
> restored database in the test environment and try doing any inserts on
> the server we get the identity ranges full message and run the
> sp_adjustpublisheridentityrange sp to fix the problem. When we do this
> sometimes it works in that we can do inserts at the server and other
> times it doesnt. I've come across references to restarting the merge
> agent.. does this mean after i run the stored procedure we need to
> stop/re start the SQL agent on the server to get the identity ranges
> back in sync or does it mean syncing one of the subscriber databases?
> i guess what we're looking for is a consistent way of checking the
> identity ranges - and then a consistent way of setting them... on re
> published databases. This is causing us serious grief as one of our
> app's in test is to merge duplicate entries on our system and it keeps
> falling over in test due to the indentity ranges filling up...
> Any help would be greatly appreciated -
> Cheers,
> Michael
>
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
>

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirlTechnically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>|||Thank You all.. I have in fact successfully backuped and restored my
database with the roles I assigned to the sql login and I did not need
sa privilege.
Thank is why I am stumped that the 3rd party tool would require the sa
privilege to do its job.
sqlgirl
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirlDo the new logins have permission to access the files on
the server ?
J
>--Original Message--
>Hello All,
>I have run into a situation that a 3rd party backup and
restore solution is
>requiring sa account to backup and restore databases in
my SQL 2K/SP3a
>server.
>I have created a login that is a member of
db_backupoperator and db_onwer
>database roles and database_creators server role. Isn't
that enough? does
>it absolutely need sa privilege? Please enlighten me if
you have run into
>the same scenario.
>Thanks,
>sqlgirl
>
>.
>|||Technically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
--
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirl
Technically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>
|||Thank You all.. I have in fact successfully backuped and restored my
database with the roles I assigned to the sql login and I did not need
sa privilege.
Thank is why I am stumped that the 3rd party tool would require the sa
privilege to do its job.
sqlgirl
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restore Newbie

I am trying to install and run a prototype VB.net application for review
I have desktop edition of SQL2K installed and running.
I have a .bak file that I need to load for the applications data
How do I do restore?
I've seen posts where one runs the restore command, but I cannot find an executable called restore.exe?Allan,
It's a T-SQL command.
RESTORE DATABASE.
Or, you can do it from Enterprise Manager.
Either way, look at RESTORE DATABASE in Books Online.
James Hokes
"Allan" <anonymous@.discussions.microsoft.com> wrote in message
news:E467A676-703B-4636-BAE0-19B4BB6DD55B@.microsoft.com...
> I am trying to install and run a prototype VB.net application for review
> I have desktop edition of SQL2K installed and running.
> I have a .bak file that I need to load for the applications data
> How do I do restore?
> I've seen posts where one runs the restore command, but I cannot find an
executable called restore.exe?
>
>

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
SQL2K SP3
TIA, ChrisR
I would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO
|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegr oups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>
|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert
|||Have a look here: http://www.sqldts.com/default.aspx?204
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegr oups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>
|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
If[vbcol=seagreen]
a[vbcol=seagreen]
simple
>

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
--
SQL2K SP3
TIA, ChrisRI would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegroups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert|||Have a look here: http://www.sqldts.com/default.aspx?204
--
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegroups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>> I restored MSDB onto a new box and my jobs won't run now:
>> Unable to connect to SQL Server '(local)'. The step failed.
>> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
>> answer to this? If so, what about moving the DTS Packages?
>> --
>> SQL2K SP3
>> TIA, ChrisR
>>
>|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> > Thanks Andrew.
> >
> > The "Save as" option suits me just fine. But assuming I do that in place
> > of
> > Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> > Packages
> > and those that don't. I know the other options are to script them or to
> > use
> > DTS to transfer them. Is anyone aware of the pros/ cons for either one?
If
> > I
> > do either of these methods, will I be missing something vital from NOT
> > Restoring MSDB? I don't think so based on past experience, but its been
a
> > while.
> >
> > TIA, ChrisR
> >
> >
> >
> > "ChrisR" <bla@.noemail.com> wrote in message
> > news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> >> I restored MSDB onto a new box and my jobs won't run now:
> >>
> >> Unable to connect to SQL Server '(local)'. The step failed.
> >>
> >> Am I better off to use the Transfer Jobs Task in DTS or is there a
simple
> >> answer to this? If so, what about moving the DTS Packages?
> >>
> >> --
> >> SQL2K SP3
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
SQL2K SP3
TIA, ChrisRI would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegroups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert|||Have a look here: http://www.sqldts.com/default.aspx?204
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegroups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
If[vbcol=seagreen]
a[vbcol=seagreen]
simple[vbcol=seagreen]
>