restore master database: user

Showing posts with label user. Show all posts

Friday, March 30, 2012

Restored Database User Issue

I restored a SQl database from one server to another and everything was exac
t
from one server to another besides the users. I had 2 users that would not
update correctly. I have tried to drop and recreate and had errors. Any
tips on getting users from one database to another?
TIAHi,
See the system procedure sp_change_users_login in the sql server books
online
Thanks
Hari
SQL Server MVP
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:444BE209-AA86-4D3A-B912-97529E0753EA@.microsoft.com...
>I restored a SQl database from one server to another and everything was
>exact
> from one server to another besides the users. I had 2 users that would
> not
> update correctly. I have tried to drop and recreate and had errors. Any
> tips on getting users from one database to another?
> TIA|||Hi
Look at sp_change_users_login with the Auto_Fix option.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:444BE209-AA86-4D3A-B912-97529E0753EA@.microsoft.com...
>I restored a SQl database from one server to another and everything was
>exact
> from one server to another besides the users. I had 2 users that would
> not
> update correctly. I have tried to drop and recreate and had errors. Any
> tips on getting users from one database to another?
> TIAsql

Wednesday, March 28, 2012

Restore using backup device

We currently have a nightly full backup of a user database (call it UserDB) going to a backup device, call it Device_A. Every 2 hours we perform a transaction log backup to the same device, Device_A. To simplify, lets say the backup device file is called
device_a.bak.
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb transaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there were a failure, and at the time of the failure we had the full database backup, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would not work to restore to a point in time. How would it be done?
Message posted via http://www.sqlmonster.com
since you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQLMonster.c om...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME =
> N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.sqlmonster.com
|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too boneheaded to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILELISTONLY seems to lack the full information I need to identify the the files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements) needed to restore to a poin
t in time. It seems like I need to also utilize the RESTORE HEADERONLY also, but that still does not give me the filenumber. How can I retrieve the specific filenumbers needed to encompass my most recent full backup and the subsequent log backups? I must
be missing something and cannot see or understand it.
Message posted via http://www.sqlmonster.com
|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.c om...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com
|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.c om...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com

Restore using backup device

We currently have a nightly full backup of a user database (call it UserDB)
going to a backup device, call it Device_A. Every 2 hours we perform a trans
action log backup to the same device, Device_A. To simplify, lets say the ba
ckup device file is called
device_a.bak.
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'user
db backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb tr
ansaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there
were a failure, and at the time of the failure we had the full database back
up, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace
, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would n
ot work to restore to a point in time. How would it be done?
Message posted via http://www.droptable.comsince you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQ
droptable.com...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME =
> N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.droptable.com|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too bone
headed to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILE
LISTONLY seems to lack the full information I need to identify the the files
(the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements)
needed to restore to a poin
t in time. It seems like I need to also utilize the RESTORE HEADERONLY also,
but that still does not give me the filenumber. How can I retrieve the spec
ific filenumbers needed to encompass my most recent full backup and the subs
equent log backups? I must
be missing something and cannot see or understand it.
Message posted via http://www.droptable.com|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQ
droptable.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.droptable.com|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQ
droptable.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.droptable.com

Restore using backup device

We currently have a nightly full backup of a user database (call it UserDB) going to a backup device, call it Device_A. Every 2 hours we perform a transaction log backup to the same device, Device_A. To simplify, lets say the backup device file is called device_a.bak.
Our backup commands are:
BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb transaction log', NOSKIP , STATS = 10, NOFORMAT
What would be the restore syntax to get it back to a point in time if there were a failure, and at the time of the failure we had the full database backup, followed by two transaction log backups?
Would it be something like this:
1. Restore database userdb from disk = 'e:\backup\device_a.bak' with replace, norecovery
2. Restore log userdb from disk = 'e:\backup\device_a.bak'
It seems, if there are multiple transaction logs to apply, that this would not work to restore to a point in time. How would it be done?
--
Message posted via http://www.sqlmonster.comsince you do both full bk and log bk with NOINIT, SQL with keep adding new
backup sets into the backup device. Do a RESTORE FILELISTONLY to find out
the full bk set of the day you want to start with. Once you got it let's
say it = x you will run this:
restore database userdb from disk = 'e:\backup\device_a.bak' with replace,
file = x, standby= @.undo_userdb.bak
restore log userdb from disk = 'e:\backup\device_a.bak' with file = x + 1,
standby= @.undo_userdb.bak
I think you can pickup from here. Read "How to restore to a point in time"
in BOL for more details.
hth,
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:77008f35de2d430f818697e0a4d2a00d@.SQLMonster.com...
> We currently have a nightly full backup of a user database (call it
> UserDB) going to a backup device, call it Device_A. Every 2 hours we
> perform a transaction log backup to the same device, Device_A. To
> simplify, lets say the backup device file is called device_a.bak.
> Our backup commands are:
> BACKUP DATABASE userdb TO device_a WITH NOINIT , NOUNLOAD , NAME => N'userdb backup', NOSKIP , STATS = 10, NOFORMAT
> BACKUP LOG userdb TO device_a WITH NOINIT , NOUNLOAD , NAME = N'userdb
> transaction log', NOSKIP , STATS = 10, NOFORMAT
> What would be the restore syntax to get it back to a point in time if
> there were a failure, and at the time of the failure we had the full
> database backup, followed by two transaction log backups?
> Would it be something like this:
> 1. Restore database userdb from disk = 'e:\backup\device_a.bak' with
> replace, norecovery
> 2. Restore log userdb from disk = 'e:\backup\device_a.bak'
> It seems, if there are multiple transaction logs to apply, that this would
> not work to restore to a point in time. How would it be done?
> --
> Message posted via http://www.sqlmonster.com|||Thanks, that is what I was looking for.
I need further clarification that BOL did not provide, or else I am too boneheaded to understand.
Since I am adding new backup sets to the same backup device the RESTORE FILELISTONLY seems to lack the full information I need to identify the the files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG statements) needed to restore to a point in time. It seems like I need to also utilize the RESTORE HEADERONLY also, but that still does not give me the filenumber. How can I retrieve the specific filenumbers needed to encompass my most recent full backup and the subsequent log backups? I must be missing something and cannot see or understand it.
--
Message posted via http://www.sqlmonster.com|||> It seems like I need to also utilize the RESTORE HEADERONLY also, but that
> still does not give me the filenumber.
The Position column of the RESTORE HEADERONLY results is the file number of
the backup. This is the value you need to specify as the FILE parameter in
your restore commands.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.com|||I didn't double check when I wrote RESTORE FILELISTONLY. I just remmember
it is either one to get the file number. It should be in RESTORE HEADERONLY
as Dan pointed out.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:95c034fd30aa48d1a13d01d1b50fade2@.SQLMonster.com...
> Thanks, that is what I was looking for.
> I need further clarification that BOL did not provide, or else I am too
> boneheaded to understand.
> Since I am adding new backup sets to the same backup device the RESTORE
> FILELISTONLY seems to lack the full information I need to identify the the
> files (the filenumber needed in the RESTORE DATABASE and RESTORE LOG
> statements) needed to restore to a point in time. It seems like I need to
> also utilize the RESTORE HEADERONLY also, but that still does not give me
> the filenumber. How can I retrieve the specific filenumbers needed to
> encompass my most recent full backup and the subsequent log backups? I
> must be missing something and cannot see or understand it.
> --
> Message posted via http://www.sqlmonster.comsql

Restore user db'd on servers with different service pack levels

Is there any documentation on comaptibility related to restoring
databases backed up from server A to server B as it pertains to
editions and service pack levels?
For example, can you restore a SQL2k sp3 database onto a SQL2ksp4
database, and vice versa?take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-
48ebca266659.htm
in your SQL 2005 books online
or
search sp_dbcmptlevel for SQL 2000
SQL Server is forward Compatable as a rule of thumb. Meaning you can
usually go from an older version of 2000 to a 2005. It does not support
backwards compatablity moving from 2005 to 2000.
So to answer your question. Yes you can move forward and restore onto the
newest service pack as long as these are USER databases and not system.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Jeff McAhren" <mcahren@.gmail.com> wrote in message
news:1158004530.422905.146510@.i3g2000cwc.googlegroups.com...
> Is there any documentation on comaptibility related to restoring
> databases backed up from server A to server B as it pertains to
> editions and service pack levels?
> For example, can you restore a SQL2k sp3 database onto a SQL2ksp4
> database, and vice versa?
>

Monday, March 26, 2012

restore to new server causes problems

SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.

> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might wan
t to check out
http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & use
r db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text report
s no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its st
eps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaroun
d is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sys
servers? There are 3
> columns, srvname, datasource & srvnetname that all have the original serve
r name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select
enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue
to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.sql

restore to new server causes problems

SQL 2000 sp4 on both servers
I backed up all databases from server A.
Installed SQL Server on Server B then restored all databases (system &
user db's) to server B
Shut down SQL Server on server A
(on server B from here on)
When I try to change maintenance plan properties (the path for text
reports no longer exists), I get the error
"Error 14274: Cannot add, update, or delete a job (or its steps or
schedules) that originated from an MSX server."
Looking that up, I find article # 281642. That article says the
workaround is:
1. Rename the server back to original name
2. script out all of the jobs and then delete them
3. rename the server to the new name.
4. Add back the jobs by running the script generated in step 2.
1st question: Can I just
Update sysjobs
Set originating_server = 'server B'
where originating_server = 'server A'
2nd question:
Do I need to be concerned about the server name being wrong in
master..sysservers?
There are 3 columns, srvname, datasource & srvnetname that all have the
original server name.
3rd question:
All sql server agent jobs on server B have enabled = no (in SEM).
Select enabled from sysjobs and sp_help_job confirm this. The jobs
(trans log and full backups) continue to run and appear to be successful.
Why do all the jobs continue to run when they are disabled?
Thanks
Tom
--
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
This sounds right. I did something like this several years ago with no ill
effect. Id say try it out on just 1 job and see what happens, but thats
sounds like it to me.
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
I've never been worried about it when I took your same actions, but perhaps
I just got lucky.
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system &
> user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text
> reports no longer exists), I get the error
> "Error 14274: Cannot add, update, or delete a job (or its steps or
> schedules) that originated from an MSX server."
> Looking that up, I find article # 281642. That article says the
> workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in
> master..sysservers?
> There are 3 columns, srvname, datasource & srvnetname that all have the
> original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM).
> Select enabled from sysjobs and sp_help_job confirm this. The jobs
> (trans log and full backups) continue to run and appear to be successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.|||What you did pretty much resembles a rename of the machine, so you might want to check out
http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tom W" <Tom.Williams@.DontSpamMencmail.net> wrote in message
news:%23M%23C6WvEHHA.3660@.TK2MSFTNGP06.phx.gbl...
> SQL 2000 sp4 on both servers
> I backed up all databases from server A.
> Installed SQL Server on Server B then restored all databases (system & user db's) to server B
> Shut down SQL Server on server A
> (on server B from here on)
> When I try to change maintenance plan properties (the path for text reports no longer exists), I
> get the error "Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that
> originated from an MSX server."
> Looking that up, I find article # 281642. That article says the workaround is:
> 1. Rename the server back to original name
> 2. script out all of the jobs and then delete them
> 3. rename the server to the new name.
> 4. Add back the jobs by running the script generated in step 2.
> 1st question: Can I just
> Update sysjobs
> Set originating_server = 'server B'
> where originating_server = 'server A'
>
> 2nd question:
> Do I need to be concerned about the server name being wrong in master..sysservers? There are 3
> columns, srvname, datasource & srvnetname that all have the original server name.
>
> 3rd question:
> All sql server agent jobs on server B have enabled = no (in SEM). Select enabled from sysjobs and
> sp_help_job confirm this. The jobs (trans log and full backups) continue to run and appear to be
> successful.
> Why do all the jobs continue to run when they are disabled?
>
> Thanks
> Tom
> --
>
> E-mail correspondence to and from this address may be subject to the
> North Carolina Public Records Law and may be disclosed to third parties.

Wednesday, March 21, 2012

Restore sp_grantdbaccess

I need to know how to restore a stored procedure that was deleted. I created
a new one, but it is set to user type and doesn't work. I don't know how to
pull it out of a master db backup. Any help would be GREAT.
Thanks,
FrankI assume this is 2000. I'd check the installation scripts in the install folder of your SQL Server
installation. You will fine one or a few which installs the system procedures and based on that code
within that script you will see how to add it as a system procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
>I need to know how to restore a stored procedure that was deleted. I created
> a new one, but it is set to user type and doesn't work. I don't know how to
> pull it out of a master db backup. Any help would be GREAT.
> Thanks,
> Frank|||Yes, 2000. I have the SQL out of the the SP, but when I recreate it it has a
user type and not system. Tells me i don't have access to active directory.
"Tibor Karaszi" wrote:
> I assume this is 2000. I'd check the installation scripts in the install folder of your SQL Server
> installation. You will fine one or a few which installs the system procedures and based on that code
> within that script you will see how to add it as a system procedures.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Frank" <Frank@.discussions.microsoft.com> wrote in message
> news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
> >I need to know how to restore a stored procedure that was deleted. I created
> > a new one, but it is set to user type and doesn't work. I don't know how to
> > pull it out of a master db backup. Any help would be GREAT.
> > Thanks,
> > Frank
>|||You need to dig further into that script file. In the beginning of the script, it should execute a
system stored procedure which result in every following procedures created are classified as system
stored procedures.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:26C50283-2EC2-4A5D-8436-1027E5B79D08@.microsoft.com...
> Yes, 2000. I have the SQL out of the the SP, but when I recreate it it has a
> user type and not system. Tells me i don't have access to active directory.
> "Tibor Karaszi" wrote:
>> I assume this is 2000. I'd check the installation scripts in the install folder of your SQL
>> Server
>> installation. You will fine one or a few which installs the system procedures and based on that
>> code
>> within that script you will see how to add it as a system procedures.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Frank" <Frank@.discussions.microsoft.com> wrote in message
>> news:5848753F-E15B-486C-BC27-4EBD24ECC960@.microsoft.com...
>> >I need to know how to restore a stored procedure that was deleted. I created
>> > a new one, but it is set to user type and doesn't work. I don't know how to
>> > pull it out of a master db backup. Any help would be GREAT.
>> > Thanks,
>> > Frank
>>

Restore single user mode

I have the following code to restore a database (it was not written by
me)
Dim ServerName As String
Dim DBName As String
Dim BackupToRestore As String
ServerName = "PENTIUM4\VIPER"
DBName = "master"
BackupToRestore = "ViperData\Bmaster"
Dim oServer As SQLDMO.SQLServer
Dim oRestore As SQLDMO.Restore
On Error GoTo Handler
'simple err checking
If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
MsgBox "You MUST provide server name, database name, and the name of
the bak file you want to restore", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
'open connection to server
Set oServer = New SQLDMO.SQLServer
With oServer
.LoginSecure = True
.Connect ServerName
End With
'also need a restore object
Set oRestore = New SQLDMO.Restore
'use the 'with' construct to minimize property lookups
With oRestore
'this is where your backup will be restored to
.Database = DBName
'same as EM or TSQL, you can restore database, file, or log, here
we're going to
'use database
.Action = SQLDMORestore_Database
'this is the "force restore over existing database" option
.ReplaceDatabase = True
'this does a restore from a file instead of a device - note that we're
still
'restoring a database, NOT a file group
.Files = BackupToRestore
'do it
.SQLRestore oServer
End With
'standard clean up
Set oRestore = Nothing
oServer.DisConnect
Set oServer = Nothing
Exit Sub
Handler:
If MsgBox(Err.Description & ". Would you like to continue?",
vbInformation + vbYesNo) = vbYes Then
Resume Next
End If
I keep getting the error message that the sql server must be in single
use mode
The serve comes with a program I purchased and is controlled by
Microsoft service manager 8.00.760
How do i run it in single user mode?It sounds like you are trying to restore the database "Master" To =restore master the entire SQl server must be in single user mode. This =means starting SQl server from a command prompt or adjusting the startup =parameters for running it a a service. From a command line is simplest - =sqlservr -m.
Full detail;s of starting in single user mode are in BOL.
Mike John
<NoNothing@.aol.com> wrote in message =news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
> I have the following code to restore a database (it was not written by
> me)
> > Dim ServerName As String
> Dim DBName As String
> Dim BackupToRestore As String
> ServerName =3D "PENTIUM4\VIPER"
> DBName =3D "master"
> BackupToRestore =3D "ViperData\Bmaster"
> > Dim oServer As SQLDMO.SQLServer
> Dim oRestore As SQLDMO.Restore
> On Error GoTo Handler
> > 'simple err checking
> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" Then
> MsgBox "You MUST provide server name, database name, and the name of
> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
> Exit Sub
> End If
> > 'open connection to server
> Set oServer =3D New SQLDMO.SQLServer
> With oServer
> .LoginSecure =3D True
> .Connect ServerName
> End With
> > 'also need a restore object
> Set oRestore =3D New SQLDMO.Restore
> > 'use the 'with' construct to minimize property lookups
> With oRestore
> 'this is where your backup will be restored to
> .Database =3D DBName
> 'same as EM or TSQL, you can restore database, file, or log, here
> we're going to
> 'use database
> .Action =3D SQLDMORestore_Database
> 'this is the "force restore over existing database" option
> .ReplaceDatabase =3D True
> 'this does a restore from a file instead of a device - note that we're
> still
> 'restoring a database, NOT a file group
> .Files =3D BackupToRestore
> 'do it
> .SQLRestore oServer
> End With
> > 'standard clean up
> Set oRestore =3D Nothing
> oServer.DisConnect
> Set oServer =3D Nothing
> > Exit Sub
> > Handler:
> If MsgBox(Err.Description & ". Would you like to continue?",
> vbInformation + vbYesNo) =3D vbYes Then
> Resume Next
> End If
> > > > I keep getting the error message that the sql server must be in single
> use mode
> > The serve comes with a program I purchased and is controlled by
> Microsoft service manager 8.00.760
> > How do i run it in single user mode?
>|||Yes I had read apbout that however two things. First to start my
server I need to type sqlservr.exe NOT sqlmangr.exe and when I type
sqlservr.exe -m I still get the error message
Am I using a different version of MS sqlmanager?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:
>It sounds like you are trying to restore the database "Master" To restore master the entire SQl server must be in single user mode. This means starting SQl server from a command prompt or adjusting the startup parameters for running it a a service. From a command line is simplest - sqlservr -m.
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
>> I have the following code to restore a database (it was not written by
>> me)
>> Dim ServerName As String
>> Dim DBName As String
>> Dim BackupToRestore As String
>> ServerName = "PENTIUM4\VIPER"
>> DBName = "master"
>> BackupToRestore = "ViperData\Bmaster"
>> Dim oServer As SQLDMO.SQLServer
>> Dim oRestore As SQLDMO.Restore
>> On Error GoTo Handler
>> 'simple err checking
>> If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
>> MsgBox "You MUST provide server name, database name, and the name of
>> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
>> Exit Sub
>> End If
>> 'open connection to server
>> Set oServer = New SQLDMO.SQLServer
>> With oServer
>> .LoginSecure = True
>> .Connect ServerName
>> End With
>> 'also need a restore object
>> Set oRestore = New SQLDMO.Restore
>> 'use the 'with' construct to minimize property lookups
>> With oRestore
>> 'this is where your backup will be restored to
>> .Database = DBName
>> 'same as EM or TSQL, you can restore database, file, or log, here
>> we're going to
>> 'use database
>> .Action = SQLDMORestore_Database
>> 'this is the "force restore over existing database" option
>> .ReplaceDatabase = True
>> 'this does a restore from a file instead of a device - note that we're
>> still
>> 'restoring a database, NOT a file group
>> .Files = BackupToRestore
>> 'do it
>> .SQLRestore oServer
>> End With
>> 'standard clean up
>> Set oRestore = Nothing
>> oServer.DisConnect
>> Set oServer = Nothing
>> Exit Sub
>> Handler:
>> If MsgBox(Err.Description & ". Would you like to continue?",
>> vbInformation + vbYesNo) = vbYes Then
>> Resume Next
>> End If
>>
>> I keep getting the error message that the sql server must be in single
>> use mode
>> The serve comes with a program I purchased and is controlled by
>> Microsoft service manager 8.00.760
>> How do i run it in single user mode?|||Soory for my ignorance what is BOL?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:
>It sounds like you are trying to restore the database "Master" To restore master the entire SQl server must be in single user mode. This means starting SQl server from a command prompt or adjusting the startup parameters for running it a a service. From a command line is simplest - sqlservr -m.
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
>> I have the following code to restore a database (it was not written by
>> me)
>> Dim ServerName As String
>> Dim DBName As String
>> Dim BackupToRestore As String
>> ServerName = "PENTIUM4\VIPER"
>> DBName = "master"
>> BackupToRestore = "ViperData\Bmaster"
>> Dim oServer As SQLDMO.SQLServer
>> Dim oRestore As SQLDMO.Restore
>> On Error GoTo Handler
>> 'simple err checking
>> If ServerName = "" Or DBName = "" Or BackupToRestore = "" Then
>> MsgBox "You MUST provide server name, database name, and the name of
>> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
>> Exit Sub
>> End If
>> 'open connection to server
>> Set oServer = New SQLDMO.SQLServer
>> With oServer
>> .LoginSecure = True
>> .Connect ServerName
>> End With
>> 'also need a restore object
>> Set oRestore = New SQLDMO.Restore
>> 'use the 'with' construct to minimize property lookups
>> With oRestore
>> 'this is where your backup will be restored to
>> .Database = DBName
>> 'same as EM or TSQL, you can restore database, file, or log, here
>> we're going to
>> 'use database
>> .Action = SQLDMORestore_Database
>> 'this is the "force restore over existing database" option
>> .ReplaceDatabase = True
>> 'this does a restore from a file instead of a device - note that we're
>> still
>> 'restoring a database, NOT a file group
>> .Files = BackupToRestore
>> 'do it
>> .SQLRestore oServer
>> End With
>> 'standard clean up
>> Set oRestore = Nothing
>> oServer.DisConnect
>> Set oServer = Nothing
>> Exit Sub
>> Handler:
>> If MsgBox(Err.Description & ". Would you like to continue?",
>> vbInformation + vbYesNo) = vbYes Then
>> Resume Next
>> End If
>>
>> I keep getting the error message that the sql server must be in single
>> use mode
>> The serve comes with a program I purchased and is controlled by
>> Microsoft service manager 8.00.760
>> How do i run it in single user mode?|||BOL is Books online. Comes with SQlmserver and is the full product =manual. Should read by everyone!
SQLSERVR.EXE is the command you want, sqlmangr is the little app that =sits in the system tray tellimng you its running.
Look up restoring the master database in Books Online and you will find =all the steps, but I would question WHY your VB code is trying to =restore master if you were not aware of it. Do you really want to =restore master?
Mike John
<NoNothing@.aol.com> wrote in message =news:0lg710lh55q4pc37jqotvp0khl0c8ob838@.4ax.com...
> Soory for my ignorance what is BOL?
> > On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
> <Mike.John@.knowledgepool.spamtrap.com> wrote:
> > >It sounds like you are trying to restore the database "Master" To =restore master the entire SQl server must be in single user mode. This =means starting SQl server from a command prompt or adjusting the startup =parameters for running it a a service. From a command line is simplest - =sqlservr -m.
> >
> >Full detail;s of starting in single user mode are in BOL.
> >
> >Mike John
> >
> ><NoNothing@.aol.com> wrote in message =news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.4ax.com...
> >> I have the following code to restore a database (it was not written =by
> >> me)
> >> > >> Dim ServerName As String
> >> Dim DBName As String
> >> Dim BackupToRestore As String
> >> ServerName =3D "PENTIUM4\VIPER"
> >> DBName =3D "master"
> >> BackupToRestore =3D "ViperData\Bmaster"
> >> > >> Dim oServer As SQLDMO.SQLServer
> >> Dim oRestore As SQLDMO.Restore
> >> On Error GoTo Handler
> >> > >> 'simple err checking
> >> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" =Then
> >> MsgBox "You MUST provide server name, database name, and the name =of
> >> the bak file you want to restore", vbInformation + vbOKOnly, ="Error"
> >> Exit Sub
> >> End If
> >> > >> 'open connection to server
> >> Set oServer =3D New SQLDMO.SQLServer
> >> With oServer
> >> .LoginSecure =3D True
> >> .Connect ServerName
> >> End With
> >> > >> 'also need a restore object
> >> Set oRestore =3D New SQLDMO.Restore
> >> > >> 'use the 'with' construct to minimize property lookups
> >> With oRestore
> >> 'this is where your backup will be restored to
> >> .Database =3D DBName
> >> 'same as EM or TSQL, you can restore database, file, or log, here
> >> we're going to
> >> 'use database
> >> .Action =3D SQLDMORestore_Database
> >> 'this is the "force restore over existing database" option
> >> .ReplaceDatabase =3D True
> >> 'this does a restore from a file instead of a device - note that =we're
> >> still
> >> 'restoring a database, NOT a file group
> >> .Files =3D BackupToRestore
> >> 'do it
> >> .SQLRestore oServer
> >> End With
> >> > >> 'standard clean up
> >> Set oRestore =3D Nothing
> >> oServer.DisConnect
> >> Set oServer =3D Nothing
> >> > >> Exit Sub
> >> > >> Handler:
> >> If MsgBox(Err.Description & ". Would you like to continue?",
> >> vbInformation + vbYesNo) =3D vbYes Then
> >> Resume Next
> >> End If
> >> > >> > >> > >> I keep getting the error message that the sql server must be in =single
> >> use mode
> >> > >> The serve comes with a program I purchased and is controlled by
> >> Microsoft service manager 8.00.760
> >> > >> How do i run it in single user mode?
> >> >

Restore single user mode

quote:

> I have the following code to restore a database (it was not written by
> me)
>=20
> Dim ServerName As String
> Dim DBName As String
> Dim BackupToRestore As String
> ServerName =3D "PENTIUM4\VIPER"
> DBName =3D "master"
> BackupToRestore =3D "ViperData\Bmaster"
>=20
> Dim oServer As SQLDMO.SQLServer
> Dim oRestore As SQLDMO.Restore
> On Error GoTo Handler
> =20
> 'simple err checking
> If ServerName =3D "" Or DBName =3D "" Or BackupToRestore =3D "" Then
> MsgBox "You MUST provide server name, database name, and the name of
> the bak file you want to restore", vbInformation + vbOKOnly, "Error"
> Exit Sub
> End If
> =20
> 'open connection to server
> Set oServer =3D New SQLDMO.SQLServer
> With oServer
> .LoginSecure =3D True
> .Connect ServerName
> End With
> =20
> 'also need a restore object
> Set oRestore =3D New SQLDMO.Restore
> =20
> 'use the 'with' construct to minimize property lookups
> With oRestore
> 'this is where your backup will be restored to
> .Database =3D DBName
> 'same as EM or TSQL, you can restore database, file, or log, here
> we're going to
> 'use database
> .Action =3D SQLDMORestore_Database
> 'this is the "force restore over existing database" option
> .ReplaceDatabase =3D True
> 'this does a restore from a file instead of a device - note that we're
> still
> 'restoring a database, NOT a file group
> .Files =3D BackupToRestore
> 'do it
> .SQLRestore oServer
> End With
> =20
> 'standard clean up
> Set oRestore =3D Nothing
> oServer.DisConnect
> Set oServer =3D Nothing
> =20
> Exit Sub
> =20
> Handler:
> If MsgBox(Err.Description & ". Would you like to continue?",
> vbInformation + vbYesNo) =3D vbYes Then
> Resume Next
> End If
>=20
>=20
>=20
> I keep getting the error message that the sql server must be in single
> use mode
>=20
> The serve comes with a program I purchased and is controlled by
> Microsoft service manager 8.00.760
>=20
> How do i run it in single user mode?
>

|||Yes I had read apbout that however two things. First to start my
server I need to type sqlservr.exe NOT sqlmangr.exe and when I type
sqlservr.exe -m I still get the error message
Am I using a different version of MS sqlmanager?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:

quote:

>It sounds like you are trying to restore the database "Master" To restore master the entire
SQl server must be in single user mode. This means starting SQl server from a command promp
t or adjusting the startup parameters for running it a a service. From

a command line is simplest - sqlservr -m.[QUOTE]
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviu
f@.4ax.com...|||Soory for my ignorance what is BOL?
On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
<Mike.John@.knowledgepool.spamtrap.com> wrote:

quote:

>It sounds like you are trying to restore the database "Master" To restore master the entire
SQl server must be in single user mode. This means starting SQl server from a command promp
t or adjusting the startup parameters for running it a a service. From

a command line is simplest - sqlservr -m.[QUOTE]
>Full detail;s of starting in single user mode are in BOL.
>Mike John
><NoNothing@.aol.com> wrote in message news:3kt3101tic672lvlp4fj78tepkqg4pviu
f@.4ax.com...|||BOL is Books online. Comes with SQlmserver and is the full product =
manual. Should read by everyone!
SQLSERVR.EXE is the command you want, sqlmangr is the little app that =
sits in the system tray tellimng you its running.
Look up restoring the master database in Books Online and you will find =
all the steps, but I would question WHY your VB code is trying to =
restore master if you were not aware of it. Do you really want to =
restore master?
Mike John
<NoNothing@.aol.com> wrote in message =
news:0lg710lh55q4pc37jqotvp0khl0c8ob838@.
4ax.com...

quote:

> Soory for my ignorance what is BOL?
>=20
> On Sat, 24 Jan 2004 08:23:04 -0000, "Mike John"
> <Mike.John@.knowledgepool.spamtrap.com> wrote:
>=20
restore master the entire SQl server must be in single user mode. This =
means starting SQl server from a command prompt or adjusting the startup =
parameters for running it a a service. From a command line is simplest - =
sqlservr -m.[QUOTE]
news:3kt3101tic672lvlp4fj78tepkqg4pviuf@.
4ax.com...[QUOTE]
by[QUOTE]
Then[QUOTE]
of[QUOTE]
"Error"[QUOTE]
we're[QUOTE]
single[QUOTE]
>

Tuesday, March 20, 2012

Restore puts copy of user tables in master

Environment is SQL Server 2000 64 bit.
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?

Can you show us the script?

Monday, March 12, 2012

Restore puts copy of user tables in master

Can you show us the script?|||"Greg D. Moore $Strider$" <mooregr_deleteth1s@.greenms.com> wrote in message news:<%fgmc.160405$M3.149305@.twister.nyroc.rr.com>...
> "Emille378" <dishonty@.seidata.com> wrote in message
> news:d25b2692.0405050650.854b85c@.posting.google.co m...
> > Environment is SQL Server 2000 64 bit.
> > I restore from a script 'my' database, this works fine. However, all
> > the tables are also found in master, no data though.
> > Anyone experienced this?
> Can you show us the script?

RESTORE DATABASE XX
FROM DISK = 'g:\XX_db.BAK'
WITH STATS = 10, REPLACE,
MOVE 'XX_data' TO 'h:\Program Files\Microsoft SQL
Server\MSSQL\Data\XX_Data.mdf',
MOVE 'XX_log' TO 'g:\logs\XX_Log.ldf',
MOVE 'XX_Indx' TO 'h:\Program Files\Microsoft SQL
Server\MSSQL\Data\XX_Indx_Data.NDF'

Restore problems.

Hi,

I need to restore a database from a server to another, but all tables are not dbo's tables.

I first create the user on the destination instance, and after restoring datas, I try to change rights for this user.

I can't delete or create new rights for this user because it own tables.

How can I restore my users rights database ?If you don't mind to change the owner to DBO (which is how it should have been) you can use sp_changeobjectowner procedure, without having to create the user.|||At this point, I'd probably use sp_change_users_login (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp).

-PatP|||sp_change_users_login will map SID's, but will not allow to do what the poster asked for.|||At least as I read the question, the poster created a login named "foo" because there was a user in the database they were restoring that was already named "foo". If that is the case, and they were expecting their login to have access to the database, then sp_change_users_login would fix the SID values, giving the "foo" login on this server equivalent permissions in this database to what the "foo" login on the original server had.

Once the poster fixes the underlying SID problem, they can then grant or revoke user permissions as they see fit.

Do you think that I missed something?

-PatP|||As I said earlier, if you use sp_changeobjectowner, you don't even need to create the "foo" login to begin with.|||Thanks for all opf us.
I succeed in my problem with sp_changeobjectowner procedure.|||RDJabarov is the man

Friday, March 9, 2012

Restore only few tables of the database

HI
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.

> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the">
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using
triggers to maintain
referential integrity, but I'd opt for option 3 below instead.

> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to
separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a fi
legroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as
you probably do
structural changes for these tables and structural changes affects the syste
m tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let
you restore a database
so different parts of the database are from different points in time. This w
ould be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make
sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clie
nts execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and w
ill give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.g
bl...
> HI
> I'm designing a database. This database will be installed in a few servers
.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity betwee
n
> installations (Replication isn't relevant). What we thougth to do is one o
f
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only th
e
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael

Restore only few tables of the database

HI
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a filegroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as you probably do
structural changes for these tables and structural changes affects the system tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let you restore a database
so different parts of the database are from different points in time. This would be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clients execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and will give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity between
> installations (Replication isn't relevant). What we thougth to do is one of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael

Wednesday, March 7, 2012

Restore of master

quote:

> Tomorrow we are updating a server from 7.0 to 2000 w/
> these steps:
> 1. I am copying the user databases off onto another
> server.
> 2. We'll uninstall 7.0.
> 3. We'll install Sql Server 2000.
> 4. We'll copy the user databases back and let the upgrade
> happen "on the fly".
> My question: how do I restore the master and/or msdb
> database? Of course I can make a backup but it will be a
> 7.0 backup. Is it as simple as doing a restore of the
> master from the masterxxx.bak file that is 7.0?

|||What you outlined below was exactly what I was planning
on, because I have a certain amount of concern over
letting sql server do the upgrade. I've seen small
problems with upgrades on the client tools and so I've
never felt comfortable with an upgrade of sql server
itself.
Do you agree with my assessment? Isn't it always better
to do a fresh install if at all possible?

quote:

>--Original Message--
>You cannot restore system databases across version, most

time not even

quote:

>across service packs. To automate the info in the system

databases, you need

quote:

>to let the install program update the 7.0 install to 2000.
>So, you have to consider each piece of information in the

system databases

quote:

>and what you want to bring over and what you can do

automatically. One way

quote:

>is to study the system tables and from there create a

list of what is stored

quote:

>in the system databases and how to bring that over (and

whether you can

quote:

>"re-type" of want to automate). The main things are

usually (I probably

quote:

>forget some stuff, been away for a month):
>Master:
>Logins
>Linked servers
>Backupdevices
>Msdb:
>Jobs
>Alerts
>And don't forget model, if you've done any changes to it.
>Post back if you want to know more about a certain topic.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?

oi=djq&as_ugroup=microsoft.public.sqlserver

quote:

>
>"SSB" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:292501c3e113$52350320$a501280a@.phx.gbl...
upgrade[QUOTE]
a[QUOTE]
>
>.
>

|||Yes, I generally prefer fresh installations over upgrades. However, if you
have a more complex installation (again, regarding the stuff in your system
databases), then the effort to bring that over is an issue to weigh in, of
course. Also, another factor is how familiar you are with what is in the
system databases and how to copy or re-cerate that info.
In many cases, I prefer fresh install over upgrades for SQL Server as well.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"SSB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a8b01c3e12b$6bf07570$a401280a@.phx.gbl...[QUOTE]
> What you outlined below was exactly what I was planning
> on, because I have a certain amount of concern over
> letting sql server do the upgrade. I've seen small
> problems with upgrades on the client tools and so I've
> never felt comfortable with an upgrade of sql server
> itself.
> Do you agree with my assessment? Isn't it always better
> to do a fresh install if at all possible?
>
> time not even
> databases, you need
> system databases
> automatically. One way
> list of what is stored
> whether you can
> usually (I probably
> oi=djq&as_ugroup=microsoft.public.sqlserver
> message
> upgrade
> a

Restore of master

Tomorrow we are updating a server from 7.0 to 2000 w/
these steps:
1. I am copying the user databases off onto another
server.
2. We'll uninstall 7.0.
3. We'll install Sql Server 2000.
4. We'll copy the user databases back and let the upgrade
happen "on the fly".
My question: how do I restore the master and/or msdb
database? Of course I can make a backup but it will be a
7.0 backup. Is it as simple as doing a restore of the
master from the masterxxx.bak file that is 7.0?You cannot restore system databases across version, most time not even
across service packs. To automate the info in the system databases, you need
to let the install program update the 7.0 install to 2000.
So, you have to consider each piece of information in the system databases
and what you want to bring over and what you can do automatically. One way
is to study the system tables and from there create a list of what is stored
in the system databases and how to bring that over (and whether you can
"re-type" of want to automate). The main things are usually (I probably
forget some stuff, been away for a month):
Master:
Logins
Linked servers
Backupdevices
Msdb:
Jobs
Alerts
And don't forget model, if you've done any changes to it.
Post back if you want to know more about a certain topic.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SSB" <anonymous@.discussions.microsoft.com> wrote in message
news:292501c3e113$52350320$a501280a@.phx.gbl...
> Tomorrow we are updating a server from 7.0 to 2000 w/
> these steps:
> 1. I am copying the user databases off onto another
> server.
> 2. We'll uninstall 7.0.
> 3. We'll install Sql Server 2000.
> 4. We'll copy the user databases back and let the upgrade
> happen "on the fly".
> My question: how do I restore the master and/or msdb
> database? Of course I can make a backup but it will be a
> 7.0 backup. Is it as simple as doing a restore of the
> master from the masterxxx.bak file that is 7.0?|||What you outlined below was exactly what I was planning
on, because I have a certain amount of concern over
letting sql server do the upgrade. I've seen small
problems with upgrades on the client tools and so I've
never felt comfortable with an upgrade of sql server
itself.
Do you agree with my assessment? Isn't it always better
to do a fresh install if at all possible?
>--Original Message--
>You cannot restore system databases across version, most
time not even
>across service packs. To automate the info in the system
databases, you need
>to let the install program update the 7.0 install to 2000.
>So, you have to consider each piece of information in the
system databases
>and what you want to bring over and what you can do
automatically. One way
>is to study the system tables and from there create a
list of what is stored
>in the system databases and how to bring that over (and
whether you can
>"re-type" of want to automate). The main things are
usually (I probably
>forget some stuff, been away for a month):
>Master:
>Logins
>Linked servers
>Backupdevices
>Msdb:
>Jobs
>Alerts
>And don't forget model, if you've done any changes to it.
>Post back if you want to know more about a certain topic.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"SSB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:292501c3e113$52350320$a501280a@.phx.gbl...
>> Tomorrow we are updating a server from 7.0 to 2000 w/
>> these steps:
>> 1. I am copying the user databases off onto another
>> server.
>> 2. We'll uninstall 7.0.
>> 3. We'll install Sql Server 2000.
>> 4. We'll copy the user databases back and let the
upgrade
>> happen "on the fly".
>> My question: how do I restore the master and/or msdb
>> database? Of course I can make a backup but it will be
a
>> 7.0 backup. Is it as simple as doing a restore of the
>> master from the masterxxx.bak file that is 7.0?
>
>.
>|||Yes, I generally prefer fresh installations over upgrades. However, if you
have a more complex installation (again, regarding the stuff in your system
databases), then the effort to bring that over is an issue to weigh in, of
course. Also, another factor is how familiar you are with what is in the
system databases and how to copy or re-cerate that info.
In many cases, I prefer fresh install over upgrades for SQL Server as well.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SSB" <anonymous@.discussions.microsoft.com> wrote in message
news:2a8b01c3e12b$6bf07570$a401280a@.phx.gbl...
> What you outlined below was exactly what I was planning
> on, because I have a certain amount of concern over
> letting sql server do the upgrade. I've seen small
> problems with upgrades on the client tools and so I've
> never felt comfortable with an upgrade of sql server
> itself.
> Do you agree with my assessment? Isn't it always better
> to do a fresh install if at all possible?
> >--Original Message--
> >You cannot restore system databases across version, most
> time not even
> >across service packs. To automate the info in the system
> databases, you need
> >to let the install program update the 7.0 install to 2000.
> >
> >So, you have to consider each piece of information in the
> system databases
> >and what you want to bring over and what you can do
> automatically. One way
> >is to study the system tables and from there create a
> list of what is stored
> >in the system databases and how to bring that over (and
> whether you can
> >"re-type" of want to automate). The main things are
> usually (I probably
> >forget some stuff, been away for a month):
> >
> >Master:
> >Logins
> >Linked servers
> >Backupdevices
> >
> >Msdb:
> >Jobs
> >Alerts
> >
> >And don't forget model, if you've done any changes to it.
> >
> >Post back if you want to know more about a certain topic.
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"SSB" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:292501c3e113$52350320$a501280a@.phx.gbl...
> >> Tomorrow we are updating a server from 7.0 to 2000 w/
> >> these steps:
> >> 1. I am copying the user databases off onto another
> >> server.
> >> 2. We'll uninstall 7.0.
> >> 3. We'll install Sql Server 2000.
> >> 4. We'll copy the user databases back and let the
> upgrade
> >> happen "on the fly".
> >>
> >> My question: how do I restore the master and/or msdb
> >> database? Of course I can make a backup but it will be
> a
> >> 7.0 backup. Is it as simple as doing a restore of the
> >> master from the masterxxx.bak file that is 7.0?
> >
> >
> >.
> >

Restore of database via Veritas 9.1 fails with communication error

We are running SQL 2000 sp4, only one user database (SAP) the database is 63 GB.
The ldf files were trashed so I went to tape to restore.
We are using veritas backup exec 9.1 sp4. Veritas is on the same server. I can restore the master db and the msdb fine. However when we attempt to restore the DEV db it runs for 2 hours and then fails with a communication error from veritas.
When we restore the master the DEV db comes up suspect as there are no files for it to attach to) so we cannot attach to it to restore, so I have deleted it and attempted to restore - same communication error. I have recreated a db named DEV with the mdf etc created to the size needed plus some to be sure, no good same behavior (behavior described below).
The job starts and it creates all the folders for the database (there are 1 MDF, 5 NDF, and 4 LDF files each in its own folder). Then it begins to create teh individual files it gets ~half way through at about an hour-hour and fifteen. During this time there are a large number of writes being performed by SQL (I assume it is creating the structures). Then it switches over to reading from tape a large number of read by beengine for another 45-1.5 hours then the job fails with the error unable to communicate with the device. I ahve noticed that once it starts reading from tape the db is gone from enterprise manager, and the mdf etc. files that were being created are now gone.

Help this has gone on for three days!!!I advise my clients NEVER to use Veritas to backup databases. I have seen many occasions where the clients thought they were getting backups, but in fact were not.

My standard procedure is to use SQL Server tools to backup the databases and logs (that's what they were designed for...), and then use Veritas to backup the resulting files.

Call Veritas and see if they can help.|||I have been on the phone for two days off and on with them. We will be changing our procedure when we get past this. For 6 years we have had no problems backing up and restoring our databases...always a first time.:eek:|||SQL Server backups are so easy and reliable when scheduled through SQL Server Agent, I honestly don't know why anybody would use 3rd party software to do this.|||Point taken, however that is no help with where I am at now.|||Veritas sold you on using their backup tools, and the errors you are getting appear to be coming from Veritas, so I would keep tossing the ball back into their court.

When you get through this, I and others on the forum would be happy to advise you on setting up a more robust data recovery system.|||Thanks, we will be proceeding in that direction shortly after we get done...first some sleep though...hmmm and maybe a beer or two!!|||We do use LiteSpeed here blindman. :) It actually has worked wonders here. It still creates backup files, but they are 30% of the size and are produced twice as quick. Plus,the backups are encrypted when they are shipped off-site.

Veritas has burned me more times than I care to remember. I would NEVER use it to backup SQL Server. Also, I wouldn't use any third-party service that tries to backup the actual .mdf and .ldf files. As you stated, it's better to back them up using the Agent or something like LiteSpeed. Then, move those backup files to tape with Veritas, etc.|||Issue resolved, thanks for the comments. We will be looking into SQL backups starting next week.

Just in case anyone else hits this issue:

The problem was veritas was timing out attempting create the structures for the database. It uses VDI as the default connection to a db. The VDI is set to 25 minutes for a time out by default. CHange to named pipes and it works fine. HKEY Local machine\software\veritas\backupexec\engine\SQL Server\Force Named Pipe Backup/restore set this to 1 and restart services.

Follow on question:

THis was a 63 GB db and took 6 hours to create structures and restore data; ok it is a development system so not a huge issue. However our production SAP database is over 180 GB! What is the best way IYO to backup and restore a DB of this size - or should we be going with some sort of mirroring?|||mirrorring with some kind of raided drive is usually good but does not solve the problem of what happens when the office burns down.

without knowing more about your situation, I would say you should use a combination of full, differential and transaction log backups to another peice of hardware on your network and not directly to tape. This will be a little faster and will not be such a resource burden. Then you move these backups on this seperate machine to a tape drive connected to it.

It really all depends on your business needs and you should do a little reading on disaster recovery, but I would do a full backup on the weekends, maybe a differential every night during the witching hour and T-Log backups during the day to ensure full recovery.|||I would say make an investment in SQL LiteSpeed by Quest. It will allow your backups to run much faster and they will be much smaller. We have loved it since we purchased it.

Also, you might want to look at buying a faster disk set to backup on. If you had a seperate drive array that was 2 RAID 0 or 4 RAID 10 disks, you would find your backup speed go to minutes instead of hours. We backup hundreds of GB in less time than you take for the one database. We have a backup server with four RAID-10 drives. All backups on the network go to that share. Even with pushing them over the network, we are much faster than your solution.|||nanny nanny boo boo to you too

Friday, March 30, 2012

Restored Database User Issue

Wednesday, March 28, 2012

Restore using backup device

Restore using backup device

Restore using backup device

Restore user db'd on servers with different service pack levels

Monday, March 26, 2012

restore to new server causes problems

restore to new server causes problems

restore to new server causes problems

Wednesday, March 21, 2012

Restore sp_grantdbaccess

Restore single user mode

Restore single user mode

Tuesday, March 20, 2012

Restore puts copy of user tables in master

Monday, March 12, 2012

Restore puts copy of user tables in master

Restore problems.

Friday, March 9, 2012

Restore only few tables of the database

Restore only few tables of the database

Wednesday, March 7, 2012

Restore of master

Restore of master

Restore of database via Veritas 9.1 fails with communication error

restore master database

Blog Archive

About Me