Restrict Logins

Hi All:
I'm running SQL Server 7 in an NT environment using SQL Server
Authentication for the logins.
I'm a developer who has recently accepted the responsibilities for SQL
Server maintenance. I'm not really qualified to do this, but I'm
learning. Lord knows I need all the help and suggestions I can get;
I'm readily maintaining the status quo and make any modifications only
after what I consider exhaustive testing on our "test server". I've
just been handed the task of restricting certain logins to a specific
machine.
I do know that when I look at the running processes in EM I can see
the machines the users are connecting from.
Is there some way I can trap this information, issue an alert that
will start a job to kill the offending process? Or, is there some way
I can use DMO to do this'
I had originally thought that maybe I could intercept the login and
make the appropriate checks and deny the login... But, I haven't
found anything that indicates this approach is "do-able".
Then I found the active processes information and I began to wonder if
there wasn't some way to trap this information and issue a process
kill all in unattended mode.
I'm a developer and am not "married" to the EM ( it does make life and
learning easier).
Any and all help and suggestions are definitely appreciated.
TIA,
cathyHi,
If it is SQL Server Login or Windows user connecting to SQL server then you
can DENY the user to access the tables, If it is Host name
then you cant deny or revoke directly , But you can follow the below steps
to kill the user.
1. Create a script which looks like:-
use master
go
declare @.x varchar(1000)
set @.x=''
select @.x = @.x + ' kill ' + convert(varchar(5), spid)
from master.dbo.sysprocesses
where hostname = 'host_name' -- replace this with the actual host name
exec (@.x)
go
2. Schedule this script every 1 minute daily in SQL Agent -- Jobs. This will
check and the process if it logs in from the host name you mentioned.
Thanks
Hari
MCDBA
"Catherine Finnegan" <remove_for_nospam_caeriel1@.cablespeed.com> wrote in
message news:erfa90tjrncj89pugk4qr1bp7trkegui5n@.
4ax.com...
> Hi All:
> I'm running SQL Server 7 in an NT environment using SQL Server
> Authentication for the logins.
> I'm a developer who has recently accepted the responsibilities for SQL
> Server maintenance. I'm not really qualified to do this, but I'm
> learning. Lord knows I need all the help and suggestions I can get;
> I'm readily maintaining the status quo and make any modifications only
> after what I consider exhaustive testing on our "test server". I've
> just been handed the task of restricting certain logins to a specific
> machine.
> I do know that when I look at the running processes in EM I can see
> the machines the users are connecting from.
> Is there some way I can trap this information, issue an alert that
> will start a job to kill the offending process? Or, is there some way
> I can use DMO to do this'
> I had originally thought that maybe I could intercept the login and
> make the appropriate checks and deny the login... But, I haven't
> found anything that indicates this approach is "do-able".
> Then I found the active processes information and I began to wonder if
> there wasn't some way to trap this information and issue a process
> kill all in unattended mode.
> I'm a developer and am not "married" to the EM ( it does make life and
> learning easier).
>
> Any and all help and suggestions are definitely appreciated.
> TIA,
> cathy

restrict acces to server from lan

Hello,
I got a new SQL 2000 server running. Now we have some clients with local
msde's who are not allowed to connect to that server with their client. Can
this be done? they need network acces and they need to be able op connect to
that server using another application. So it's strictly their local engine
what they are not allowed to use to open that database on their local system
Sincerely
evbHi
I am not quite sure what you are asking here. You don't say how they are
connecting to the database server. Usually the connection string will
specify which server you are going to connect to. Access to your database
server/database will depend on the security that you put in place and you
can restrict the access to your own application by using an application
role.
If the MSDE instance does not contain a copy of your database then there
will not be able to run your application.
John
"E van Bergen" <evanbergen.no.spam@.modderkolk.no.spam.nl> wrote in message
news:co7cmj$jj$1@.reader08.wxs.nl...
> Hello,
> I got a new SQL 2000 server running. Now we have some clients with local
> msde's who are not allowed to connect to that server with their client.
> Can
> this be done? they need network acces and they need to be able op connect
> to
> that server using another application. So it's strictly their local engine
> what they are not allowed to use to open that database on their local
> system
>
> Sincerely
> evb
>sql

Restoring to New server

I have created a new server running sql server 6.5 and I am trying to restore a databasefrm an existing 6.5 server to this new server . I got the master on but I cannot get any of the databases on. First the database was going in suspend mode, then after I resolved that I am getting size errors that I cannot seem to resolve even with expanding the devices.

What I'm I doing wrong? Did I miss a step? or is there a problem with the server setup?

Ans..Q1 What I'm I doing wrong?

A1 Maybe nothing (insufficient information).

Was the master backup used in the restore to the second server in fact current (assuming the restores (DB Loads) were performed to similar devices on the new server - using similar device orders, sizes and types, as those of the DB devices as they existed on the original server)? If not, some device data may be incorrect (most likely, incomplete). In that case it may be possible to use Disk ReInit for each device on the new server as necessary to address the issue (using the same logical and physical device names is easier if that is the case; however the 2k block size value entered for the size parameter must be the same as the production user devices at the times their backup dumps were taken.). Once complete, run Disk ReFit.

Q2 Did I miss a step?

A2 Maybe, maybe not (insufficient information). However, do make sure both servers have:
similar character set installs (collation),
similar service pack levels applied,
similar database device geometries.

Q3 or is there a problem with the server setup?

A3 To help rule out some potential issues with the server setup, do a dump and load of Pubs on the new server; if Pubs restores without issues, the basic server configuration is probably not the main issue.

Restoring the master to different drive letters

Can you restore the master database, running on windows server 2000, sql
server 2000 from a backup that was taken from c:\ to another server
restoring to d:\?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

See:
http://support.microsoft.com/defaul...ben-us%3b224071

John

"spec_server" <spec_9@.nwy.com> wrote in message
news:415c6810$0$26132$c397aba@.news.newsgroups.ws.. .
> Can you restore the master database, running on windows server 2000, sql
> server 2000 from a backup that was taken from c:\ to another server
> restoring to d:\?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Restoring state of database after running a Unit test

Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.

Restoring state of database after running a Unit test

Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.
Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.

Restoring SQLServer 7 backup to MSDE

I want to develop an app on my computer running SQLServer 7, then
backup the data and resrote it at the client's running MSDE. When
restoring the backup to MSDE I get this message from SQL-DMO:
Location:upgraddb.cpp:214
Instruction:tableIndex < ARRAY_LEN(upgradeMap)
I've tried it with two backups, one of a 40+ table database and one
with 3 tables, and get the same message both times.
Do you know what this means and how to work around it? Thanks.
hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:095do0da3qccc93i4c59mmc396akdd9tbh@.4ax.com
> I want to develop an app on my computer running SQLServer 7, then
> backup the data and resrote it at the client's running MSDE. When
> restoring the backup to MSDE I get this message from SQL-DMO:
> Location: upgraddb.cpp:214
> Instruction: tableIndex < ARRAY_LEN(upgradeMap)
> I've tried it with two backups, one of a 40+ table database and one
> with 3 tables, and get the same message both times.
> Do you know what this means and how to work around it? Thanks.
you should get that exception when restoring SQL Server/MSDE 2000 databases
on SQL Server 7.0 servers..
you can restore on version 2000 from version 7.0, but not the contrary
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You indicate I can restore a SQLServer 7.0 backup to an MSDE 2000
database, but that is where I'm getting the error. Am I reading your
reply wrong?
The bigger problem seems to be developing on my computer with
SQLServer 7.0 while my clients are using MSDE. I need to be able to
move the data back and forth easily but it sounds like that's not
really possible. Since I can't (I dont' think) run both SQLServer 7.0
and MSDE on the same machine, it looks like that development concept
won't work. Is that correct, or am I missing something?
On Mon, 1 Nov 2004 21:55:44 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:095do0da3qccc93i4c59mmc396akdd9tbh@.4ax.com
>you should get that exception when restoring SQL Server/MSDE 2000 databases
>on SQL Server 7.0 servers..
>you can restore on version 2000 from version 7.0, but not the contrary
|||hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:nabfo0l29tk7i9bhaa0ndsbktgki3nf9s0@.4ax.com
> ...
> The bigger problem seems to be developing on my computer with
> SQLServer 7.0 while my clients are using MSDE. I need to be able to
> move the data back and forth easily but it sounds like that's not
> really possible. Since I can't (I dont' think) run both SQLServer 7.0
> and MSDE on the same machine, it looks like that development concept
> won't work. Is that correct, or am I missing something?
actually you can... you can have a "default" SQL Server 7.0 instance and a
SQL Server 2000 named instance on the same pc..
keep in mind that, if you only install MSDE 2000 named instance side by side
a SQL Server 7.0 instance, you won't be able to use the SQL Server 7.0
client tools, as MSDE only installs "part of" them, that's to say the type
lybs, dlls, COM dependencies and so on, but not the tools (Enterprise
Manager, Profiler, Query Analyzer).. so you'll be stuck with no tool but
oSql.exe...
if you install SQL Server 2000, the newer tools (and realated dependencies)
will be able to manage SQL Server 7.0 ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I have to apologize...I am running SQLServer 2000. Too much going on.
What difference does that make?
On Tue, 2 Nov 2004 17:55:36 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:nabfo0l29tk7i9bhaa0ndsbktgki3nf9s0@.4ax.com
>actually you can... you can have a "default" SQL Server 7.0 instance and a
>SQL Server 2000 named instance on the same pc..
>keep in mind that, if you only install MSDE 2000 named instance side by side
>a SQL Server 7.0 instance, you won't be able to use the SQL Server 7.0
>client tools, as MSDE only installs "part of" them, that's to say the type
>lybs, dlls, COM dependencies and so on, but not the tools (Enterprise
>Manager, Profiler, Query Analyzer).. so you'll be stuck with no tool but
>oSql.exe...
>if you install SQL Server 2000, the newer tools (and realated dependencies)
>will be able to manage SQL Server 7.0 ...
|||hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:7phgo0dtkn0rppaa0ee4nhjrv329n47500@.4ax.com
> I have to apologize...I am running SQLServer 2000. Too much going on.
> What difference does that make?
>
:D
ok... this sometimes occur when restoring database across servers that do
not share the same service pack level too..
I'd suggest to upgrade both (or all) servers instances to service pack 3..
please have a look at http://www.microsoft.com/sql/downloads/2000/sp3.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks very much. I'll be able to work on it Thursday and will let you
know the results.
On Wed, 3 Nov 2004 12:24:08 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:7phgo0dtkn0rppaa0ee4nhjrv329n47500@.4ax.com
>:D
>ok... this sometimes occur when restoring database across servers that do
>not share the same service pack level too..
>I'd suggest to upgrade both (or all) servers instances to service pack 3..
>please have a look at http://www.microsoft.com/sql/downloads/2000/sp3.asp
|||Once I got the same versions installed the restore worked perfectly.
Thanks for the assistance.

Restoring sql server database from one server to another from a ba

Hi,
I got a backup of a sql server database. I need to restore it to a different server where few important databases are running. I need to make sure those are operationa at any timel. What should be the strategy to restore the backed up database in the new
server? Thanks .
Regards
1. Copy the backup files to the new server.
2. Backup the current databases on the new server. (Just in case something
bad happens. <wink>)
3. Using SEM (SQL Ent. Manager) to restore the files. Be careful about
where the files will be stored, they may be different than where they were
backed up from.
4. If the DBO is not the owner of all objects in the newly restored
databse, you must ensure that the Logins associated with those users exist
in the new SQL Server and that they map properly to the newly restored user
database. If they don't, then create the logins, then use
sp_change_users_login (Check the BOL for more info on that command.).
Once you have successfully installed the new database, mapped users etc.
Create a new backup of that database.
Enjoy
Rick Sawtell MCT, MCSD, MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:1F88E2DF-A16D-4462-B55B-15E04F63F812@.microsoft.com...
> Hi,
> I got a backup of a sql server database. I need to restore it to a
different server where few important databases are running. I need to make
sure those are operationa at any timel. What should be the strategy to
restore the backed up database in the new server? Thanks .
> Regards
|||Have a look at these as well.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:1F88E2DF-A16D-4462-B55B-15E04F63F812@.microsoft.com...
> Hi,
> I got a backup of a sql server database. I need to restore it to a
different server where few important databases are running. I need to make
sure those are operationa at any timel. What should be the strategy to
restore the backed up database in the new server? Thanks .
> Regards

Restoring sql server database from one server to another from a ba

Hi,
I got a backup of a sql server database. I need to restore it to a different server where few important databases are running. I need to make sure those are operationa at any timel. What should be the strategy to restore the backed up database in the new server? Thanks .
Regards1. Copy the backup files to the new server.
2. Backup the current databases on the new server. (Just in case something
bad happens. <wink>)
3. Using SEM (SQL Ent. Manager) to restore the files. Be careful about
where the files will be stored, they may be different than where they were
backed up from.
4. If the DBO is not the owner of all objects in the newly restored
databse, you must ensure that the Logins associated with those users exist
in the new SQL Server and that they map properly to the newly restored user
database. If they don't, then create the logins, then use
sp_change_users_login (Check the BOL for more info on that command.).
Once you have successfully installed the new database, mapped users etc.
Create a new backup of that database.
Enjoy
Rick Sawtell MCT, MCSD, MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:1F88E2DF-A16D-4462-B55B-15E04F63F812@.microsoft.com...
> Hi,
> I got a backup of a sql server database. I need to restore it to a
different server where few important databases are running. I need to make
sure those are operationa at any timel. What should be the strategy to
restore the backed up database in the new server? Thanks .
> Regards|||Thanks for the help, Rick. I appreciate it. I am going to try this now. Regards
"Rick Sawtell" wrote:
> 1. Copy the backup files to the new server.
> 2. Backup the current databases on the new server. (Just in case something
> bad happens. <wink>)
> 3. Using SEM (SQL Ent. Manager) to restore the files. Be careful about
> where the files will be stored, they may be different than where they were
> backed up from.
> 4. If the DBO is not the owner of all objects in the newly restored
> databse, you must ensure that the Logins associated with those users exist
> in the new SQL Server and that they map properly to the newly restored user
> database. If they don't, then create the logins, then use
> sp_change_users_login (Check the BOL for more info on that command.).
> Once you have successfully installed the new database, mapped users etc.
> Create a new backup of that database.
> Enjoy
> Rick Sawtell MCT, MCSD, MCDBA
>
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:1F88E2DF-A16D-4462-B55B-15E04F63F812@.microsoft.com...
> > Hi,
> > I got a backup of a sql server database. I need to restore it to a
> different server where few important databases are running. I need to make
> sure those are operationa at any timel. What should be the strategy to
> restore the backed up database in the new server? Thanks .
> > Regards
>
>|||Have a look at these as well.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:1F88E2DF-A16D-4462-B55B-15E04F63F812@.microsoft.com...
> Hi,
> I got a backup of a sql server database. I need to restore it to a
different server where few important databases are running. I need to make
sure those are operationa at any timel. What should be the strategy to
restore the backed up database in the new server? Thanks .
> Regards

Restoring sql server database from one server to another from a ba

Hi,
I got a backup of a sql server database. I need to restore it to a different
server where few important databases are running. I need to make sure those
are operationa at any timel. What should be the strategy to restore the bac
ked up database in the new
server? Thanks .
Regards1. Copy the backup files to the new server.
2. Backup the current databases on the new server. (Just in case something
bad happens. <wink> )
3. Using SEM (SQL Ent. Manager) to restore the files. Be careful about
where the files will be stored, they may be different than where they were
backed up from.
4. If the DBO is not the owner of all objects in the newly restored
databse, you must ensure that the Logins associated with those users exist
in the new SQL Server and that they map properly to the newly restored user
database. If they don't, then create the logins, then use
sp_change_users_login (Check the BOL for more info on that command.).
Once you have successfully installed the new database, mapped users etc.
Create a new backup of that database.
Enjoy
Rick Sawtell MCT, MCSD, MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:1F88E2DF-A16D-4462-B55B-15E04F63F812@.microsoft.com...
> Hi,
> I got a backup of a sql server database. I need to restore it to a
different server where few important databases are running. I need to make
sure those are operationa at any timel. What should be the strategy to
restore the backed up database in the new server? Thanks .
> Regards|||Have a look at these as well.
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues
Andrew J. Kelly SQL MVP
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:1F88E2DF-A16D-4462-B55B-15E04F63F812@.microsoft.com...
> Hi,
> I got a backup of a sql server database. I need to restore it to a
different server where few important databases are running. I need to make
sure those are operationa at any timel. What should be the strategy to
restore the backed up database in the new server? Thanks .
> Regards

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, HowardIf you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connect
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see the
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to connect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
> > Hi,
> >
> > I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> > backup database msdb to disk ='c:\msdb.dat'
> >
> > then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> > norecovery
> >
> > The restore seem to succeed. But I get the following messages:
> >
> > Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> > Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> > Server: Msg 927, Level 14, State 2, Line 1
> > Database 'msdb' cannot be opened. It is in the middle of a restore.
> > Server: Msg 3009, Level 16, State 3, Line 1
> > Could not insert a backup or restore history/detail record in the msdb
> > database. This may indicate a problem with the msdb database. The
> > backup/restore operation was still successful.
> > RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> > MB/sec).
> >
> > These messages aren't generated with SQL2005, nor do you see the messages if
> > you specify 'with recovery' on the restore statement.
> >
> > I would be appreciative if someone could explain these messages. If I ignore
> > them, it is still possible to recover the database (restore database msdb
> > with recovery).
> >
> > Thanks, Howard
> >
> >
> >
> >
> >
> >|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode.
So you may still need to restore the transaction log to a PIT after restoring
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and this
type of message makes them nervous.
Thanks again, Howard|||This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
possible. One could argue that SQL server would be smart enough to produce only a warning or similar
in these situations, of course. Perhaps you want to file an entry at
http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
enough to warrant any major effort... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> Hi Ben,
> Thanks for your quick reply. I did consider using 'with recovery' on the
> restore statement. The trouble is that msdb does permit 'full' recovery mode.
> So you may still need to restore the transaction log to a PIT after restoring
> the database. Also, I understand that the msdb was indeed restored despite
> the error message. What I really want to know is what to make of the error
> message as I work with a program that deals with other people's data and this
> type of message makes them nervous.
> Thanks again, Howard
>|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:
> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
> possible. One could argue that SQL server would be smart enough to produce only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> > Hi Ben,
> >
> > Thanks for your quick reply. I did consider using 'with recovery' on the
> > restore statement. The trouble is that msdb does permit 'full' recovery mode.
> > So you may still need to restore the transaction log to a PIT after restoring
> > the database. Also, I understand that the msdb was indeed restored despite
> > the error message. What I really want to know is what to make of the error
> > message as I work with a program that deals with other people's data and this
> > type of message makes them nervous.
> >
> > Thanks again, Howard
> >
> >
>
>|||> As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005.
Cool. I didn't know that. So MS did spend some time on this. Thanks for the update. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2FA4EC15-34D7-41FA-867F-8DC825A67414@.microsoft.com...
> Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
> to get an official response from Microsoft because at least on of my
> customers may be expecting it.
> "Tibor Karaszi" wrote:
>> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
>> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing
>> isn't
>> possible. One could argue that SQL server would be smart enough to produce only a warning or
>> similar
>> in these situations, of course. Perhaps you want to file an entry at
>> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
>> enough to warrant any major effort... :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Howard" <Howard@.discussions.microsoft.com> wrote in message
>> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>> > Hi Ben,
>> >
>> > Thanks for your quick reply. I did consider using 'with recovery' on the
>> > restore statement. The trouble is that msdb does permit 'full' recovery mode.
>> > So you may still need to restore the transaction log to a PIT after restoring
>> > the database. Also, I understand that the msdb was indeed restored despite
>> > the error message. What I really want to know is what to make of the error
>> > message as I work with a program that deals with other people's data and this
>> > type of message makes them nervous.
>> >
>> > Thanks again, Howard
>> >
>> >
>>

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, Howard
If you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connect
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:

> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>
|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see the
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to connect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>
|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode.
So you may still need to restore the transaction log to a PIT after restoring
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and this
type of message makes them nervous.
Thanks again, Howard
|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and try
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:

> This is a kind of catch-22 situation. Each RESTORE want to write to the restore history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that restore history writing isn't
> possible. One could argue that SQL server would be smart enough to produce only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>
>

restoring msdb with SQL 2000

Hi,
I'm running SQL 2000, sp4. I use the following statement to backup msdb:
backup database msdb to disk ='c:\msdb.dat'
then restore with:restore database msdb from disk = 'c:\msdb.dat' with
norecovery
The restore seem to succeed. But I get the following messages:
Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
Server: Msg 927, Level 14, State 2, Line 1
Database 'msdb' cannot be opened. It is in the middle of a restore.
Server: Msg 3009, Level 16, State 3, Line 1
Could not insert a backup or restore history/detail record in the msdb
database. This may indicate a problem with the msdb database. The
backup/restore operation was still successful.
RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
MB/sec).
These messages aren't generated with SQL2005, nor do you see the messages if
you specify 'with recovery' on the restore statement.
I would be appreciative if someone could explain these messages. If I ignore
them, it is still possible to recover the database (restore database msdb
with recovery).
Thanks, HowardIf you want the database to be available for users do not restore it using
'with norecovery', just remove this part from your restore command.
Norecovery is used when you want to apply additional backups. If you see the
status on Enterprise Manager or Management Studio when you are using
norecovery it will show 'Restoring ... ' and no users will be able to connec
t
to it.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Howard" wrote:

> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I igno
re
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||By the way your restore was sucessful but because you are using with
norecovery the msdb database is not available to users. Regarding the error
messages, the msdb database keeps the backup and restore history of the
databases on the instance, and looks like in this case is not able to record
its own restore operation.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you want the database to be available for users do not restore it using
> 'with norecovery', just remove this part from your restore command.
> Norecovery is used when you want to apply additional backups. If you see t
he
> status on Enterprise Manager or Management Studio when you are using
> norecovery it will show 'Restoring ... ' and no users will be able to conn
ect
> to it.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Howard" wrote:
>|||Also, when restoring MSDB, make sure your SQLAgent service is stopped as
this service is using the MSDB database
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2D037B41-1D79-4207-8916-3964747C395C@.microsoft.com...
> Hi,
> I'm running SQL 2000, sp4. I use the following statement to backup msdb:
> backup database msdb to disk ='c:\msdb.dat'
> then restore with:restore database msdb from disk = 'c:\msdb.dat' with
> norecovery
> The restore seem to succeed. But I get the following messages:
> Processed 1424 pages for database 'msdb', file 'MSDBData' on file 1.
> Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.
> Server: Msg 927, Level 14, State 2, Line 1
> Database 'msdb' cannot be opened. It is in the middle of a restore.
> Server: Msg 3009, Level 16, State 3, Line 1
> Could not insert a backup or restore history/detail record in the msdb
> database. This may indicate a problem with the msdb database. The
> backup/restore operation was still successful.
> RESTORE DATABASE successfully processed 1425 pages in 2.985 seconds (3.908
> MB/sec).
> These messages aren't generated with SQL2005, nor do you see the messages
> if
> you specify 'with recovery' on the restore statement.
> I would be appreciative if someone could explain these messages. If I
> ignore
> them, it is still possible to recover the database (restore database msdb
> with recovery).
> Thanks, Howard
>
>
>|||Hi Ben,
Thanks for your quick reply. I did consider using 'with recovery' on the
restore statement. The trouble is that msdb does permit 'full' recovery mode
.
So you may still need to restore the transaction log to a PIT after restorin
g
the database. Also, I understand that the msdb was indeed restored despite
the error message. What I really want to know is what to make of the error
message as I work with a program that deals with other people's data and thi
s
type of message makes them nervous.
Thanks again, Howard|||This is a kind of catch-22 situation. Each RESTORE want to write to the rest
ore history tables in
msdb. Since these aren't available (yet) when you restore msdb, that restore
history writing isn't
possible. One could argue that SQL server would be smart enough to produce o
nly a warning or similar
in these situations, of course. Perhaps you want to file an entry at
http://connect.microsoft.com/sqlserver for this... Probably MS didn't find t
his happening often
enough to warrant any major effort... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
> Hi Ben,
> Thanks for your quick reply. I did consider using 'with recovery' on the
> restore statement. The trouble is that msdb does permit 'full' recovery mo
de.
> So you may still need to restore the transaction log to a PIT after restor
ing
> the database. Also, I understand that the msdb was indeed restored despit
e
> the error message. What I really want to know is what to make of the error
> message as I work with a program that deals with other people's data and t
his
> type of message makes them nervous.
> Thanks again, Howard
>|||Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
problem doesn't appear in SQL 2005. I think I'll take your suggestion and tr
y
to get an official response from Microsoft because at least on of my
customers may be expecting it.
"Tibor Karaszi" wrote:

> This is a kind of catch-22 situation. Each RESTORE want to write to the re
store history tables in
> msdb. Since these aren't available (yet) when you restore msdb, that resto
re history writing isn't
> possible. One could argue that SQL server would be smart enough to produce
only a warning or similar
> in these situations, of course. Perhaps you want to file an entry at
> http://connect.microsoft.com/sqlserver for this... Probably MS didn't find
this happening often
> enough to warrant any major effort... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Howard" <Howard@.discussions.microsoft.com> wrote in message
> news:9AE3DA6D-40B3-4577-894C-D9347C11C28A@.microsoft.com...
>
>|||> As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005.
Cool. I didn't know that. So MS did spend some time on this. Thanks for the
update. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Howard" <Howard@.discussions.microsoft.com> wrote in message
news:2FA4EC15-34D7-41FA-867F-8DC825A67414@.microsoft.com...[vbcol=seagreen]
> Thanks for this info. As I said, this is a relic of SQL2000 (SP4). The
> problem doesn't appear in SQL 2005. I think I'll take your suggestion and
try
> to get an official response from Microsoft because at least on of my
> customers may be expecting it.
> "Tibor Karaszi" wrote:
>

Restoring msdb

Hi,
My system went down and I had to reinstall the O/S and of
course SQL Server and MSDE. Anyhow after that saga, I
finally have SQL Server running again with another
Instance called <computername>\VSDOTNET
I have backups of all dbs from prior to the crash, but I
am having trouble restoring them, especially the msdb
because it contains my DTS packages which I need
desperately!
Problem is when I attempt to restore the msdb it fails
and tells me it can't do it b/c the backup was created
with a different version of the server.
I've tried to Force restore over existing database with
no luck. Any suggestions please?
Thank you.
JasonMake sure that the service pack level for your reinstalled system is the same as the one you had
when the prior install crashed. SQL Server cannot go between even service packs for system
databases.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jason" <shogun1972@.hotmail.com> wrote in message news:051401c3b7e2$01590910$a501280a@.phx.gbl...
> Hi,
> My system went down and I had to reinstall the O/S and of
> course SQL Server and MSDE. Anyhow after that saga, I
> finally have SQL Server running again with another
> Instance called <computername>\VSDOTNET
> I have backups of all dbs from prior to the crash, but I
> am having trouble restoring them, especially the msdb
> because it contains my DTS packages which I need
> desperately!
> Problem is when I attempt to restore the msdb it fails
> and tells me it can't do it b/c the backup was created
> with a different version of the server.
> I've tried to Force restore over existing database with
> no luck. Any suggestions please?
> Thank you.
> Jason

restoring master on sp3 to sp2

I have torestore a master database from sql server 2000 running sp2 onto a
sql server 2000 running sp3. Would it cause any issues? Thanks.
Hi
"sharman" wrote:

> I have torestore a master database from sql server 2000 running sp2 onto a
> sql server 2000 running sp3. Would it cause any issues? Thanks.
Why?
You should have backed up the system database post upgrade (the installation
does tell you to do that!!)
John
|||Hi John,
I think I haven't phrased my question correctly. My question is can I
restore master, model and msdb on sql 2000 sp3 from backups that were backed
up on SQL 2000 SP2?
"John Bell" wrote:

> Hi
> "sharman" wrote:
>
> Why?
> You should have backed up the system database post upgrade (the installation
> does tell you to do that!!)
> John
|||Hi
On Jun 27, 5:14 pm, sharman <shar...@.discussions.microsoft.com> wrote:
> Hi John,
> I think I haven't phrased my question correctly. My question is can I
> restore master, model and msdb on sql 2000 sp3 from backups that were backed
> up on SQL 2000 SP2?
>
> "John Bell" wrote:
>
>
>
> - Show quoted text -
You can try this!! AFAIK there is no guarantee that something has
changed in a service pack or that it will work correctly, therefore it
is best to restore the same level.
John

restoring master on sp3 to sp2

I have torestore a master database from sql server 2000 running sp2 onto a
sql server 2000 running sp3. Would it cause any issues? Thanks.Hi
"sharman" wrote:

> I have torestore a master database from sql server 2000 running sp2 onto a
> sql server 2000 running sp3. Would it cause any issues? Thanks.
Why?
You should have backed up the system database post upgrade (the installation
does tell you to do that!!)
John|||Hi John,
I think I haven't phrased my question correctly. My question is can I
restore master, model and msdb on sql 2000 sp3 from backups that were backed
up on SQL 2000 SP2?
"John Bell" wrote:

> Hi
> "sharman" wrote:
>
> Why?
> You should have backed up the system database post upgrade (the installati
on
> does tell you to do that!!)
> John|||Hi
On Jun 27, 5:14 pm, sharman <shar...@.discussions.microsoft.com> wrote:
> Hi John,
> I think I haven't phrased my question correctly. My question is can I
> restore master, model and msdb on sql 2000 sp3 from backups that were back
ed
> up on SQL 2000 SP2?
>
> "John Bell" wrote:
>
>
>
>
>
> - Show quoted text -
You can try this!! AFAIK there is no guarantee that something has
changed in a service pack or that it will work correctly, therefore it
is best to restore the same level.
John

restoring master on sp3 to sp2

I have torestore a master database from sql server 2000 running sp2 onto a
sql server 2000 running sp3. Would it cause any issues? Thanks.Hi
"sharman" wrote:
> I have torestore a master database from sql server 2000 running sp2 onto a
> sql server 2000 running sp3. Would it cause any issues? Thanks.
Why?
You should have backed up the system database post upgrade (the installation
does tell you to do that!!)
John|||Hi John,
I think I haven't phrased my question correctly. My question is can I
restore master, model and msdb on sql 2000 sp3 from backups that were backed
up on SQL 2000 SP2?
"John Bell" wrote:
> Hi
> "sharman" wrote:
> > I have torestore a master database from sql server 2000 running sp2 onto a
> > sql server 2000 running sp3. Would it cause any issues? Thanks.
> Why?
> You should have backed up the system database post upgrade (the installation
> does tell you to do that!!)
> John|||Hi
On Jun 27, 5:14 pm, sharman <shar...@.discussions.microsoft.com> wrote:
> Hi John,
> I think I haven't phrased my question correctly. My question is can I
> restore master, model and msdb on sql 2000 sp3 from backups that were backed
> up on SQL 2000 SP2?
>
> "John Bell" wrote:
> > Hi
> > "sharman" wrote:
> > > I have torestore a master database from sql server 2000 running sp2 onto a
> > > sql server 2000 running sp3. Would it cause any issues? Thanks.
> > Why?
> > You should have backed up the system database post upgrade (the installation
> > does tell you to do that!!)
> > John- Hide quoted text -
> - Show quoted text -
You can try this!! AFAIK there is no guarantee that something has
changed in a service pack or that it will work correctly, therefore it
is best to restore the same level.
John