Database Structure

Hi,
Is there a way to automate a process that export the database strucuture once a day !
All the objects - Tables, Indexes, Procedures, Views Etc..
Any Help I apreciate !
Thank's
You could run a sql agent job that uses SQL-DMO to script your database.
Here is an article that I wrote that might help:
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carrasco" <Carrasco@.discussions.microsoft.com> wrote in message
news:5964A3E3-E149-4E4D-812D-B6B62FE9C878@.microsoft.com...
> Hi,
> Is there a way to automate a process that export the database strucuture
once a day !
> All the objects - Tables, Indexes, Procedures, Views Etc..
> Any Help I apreciate !
> Thank's
>
sql

Database Snapshot on SAN attached disks

Hi All

I am in process of moving a SQL 2005 solution from a development box that used local storage to UAT environment with SAN attached storage. The solution uses database snapshots

The database files are on the SAN storage but during testing I was unable to create a Database snapshot on the SAN disk. Creating snapshots on the local disk worked fine.

Is their some restriction/problem in using the database snapshot technology with SAN storage?

What edition of Sql server 2005 you have ? Database snapshot is possible only with Enterprise edition I believe
|||

SQL Server Enterprise 2005 SP2 is being used on all systems.

The snapshots are working against the local disks on the server but not against the SAN attached storage.

Regards

Nadreck

|||

Are the SAN attached drives formatted using the NTFS file system? Database Snapshots use NTFS Sparse Files for the underlying technology, and will not work under any other file system.

I have database snapshots on production clustered servers, using SAN storage, and haven't had any problems.

|||Your use of the term "SAN attached" has me wondering. There are Network Attached Storage (NAS) devices that often have their own proprietary file system and then there are Storage Area Networks (SAN) which are usually NTFS in the Windows world. Which type of storage are you referring to? Database snapshots require NTFS.
|||There is no difference between a "SAN" and a local drive to SQL, they both look like local drives. This must be a security issue on the directory.

If you are talking about a "NAS" drive, as in you access it using "\\servername\share", SQL does not support putting data/transaction files on share drives.

|||

The storage is on a true SAN with the disks attached by Fibre Channel and being presented as Local disk.

I have spoken with the SAN administrators and they have confirmed that the disks are formatted as NTFS

The error received when trying to create the sbnapshot was

Msg 1823, Level 16, State 2, Line 1

A database snapshot cannot be created because it failed to start.

Msg 5123, Level 16, State 1, Line 1

CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'filename'.

|||I think the login with which you have logged into Sql server(windows authentication) might not be having modify privilege to the folder where the Sparse file is being created ! also check if the sql service account has necessary privileges to that folder ! ! !

Thanxx
Deepak


|||

The logon accounts and service accounts both have local admin rights on the box so write permissions do not appear to be the issue. The mdf and ldf files for the database are both on SAN storage so SQL appears to have sufficient rights to write to these disks. Creating snapshots on local disk (with the same accounts) has no issue.

Obviously it may still be some permissions issue with the SAN but I cannot see any difference.

regards

Nadreck

Database snapshot

I drop and recreate a database snapshot for reporting purposes at the end of a DW loading process.

I need to create some indexes to improve query performance.

Where I should create the index, on the originale db or on the snapshot db ?

Cosimo

If I remember from an article, you can not do this on a snapshot database. So I guess you should do this on the source DB. Please check it though.|||

SOLVED

I create the index on the source db.

Database Server Hardware Architecture

Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have."pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.
> Thanks for any tips and advice you have.
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Waiting for more infos about your database, i suggest another point of view:
I suppose that "home project" means something developed for your pleasure.
If not, and especially if you intend expose on the Network your Web Server,
it's strongly recommended to protect the dbserver (this probably would be the
meaning of "...its own box"). At the box should added security (firewalling
and so on...)
Gilberto
"Greg D. Moore (Strider)" wrote:
>
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > Hi.
> >
> > I am in the process of "ramping-up" (read: spending more money)
> > on a home project. To date, I have done the web and database
> > development on the same machine. I have been advised to put the
> > DB stuff on its own box. My question is, what kind of box should this
> > be? What are the essentials when buying hardware for a DB connection
> > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > etc).
> "It depends" a lot on the nature of your data.
> However, databases tend to like lots of RAM and fast disk channels.
> I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> as 10, or 5/50 for data) for data and logs.
> And as much RAM as your version of SQL Server can use.
> But beyond that, impossible to really advise w/o knowing more about your
> database setup.
>
> >
> > Thanks for any tips and advice you have.
> >
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
>|||On Mar 30, 3:34 am, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:
> Waiting for more infos about your database, i suggest another point of view:
> I suppose that "home project" means something developed for your pleasure.
> If not, and especially if you intend expose on the Network your Web Server,
> it's strongly recommended to protect the dbserver (this probably would be the
> meaning of "...its own box"). At the box should added security (firewalling
> and so on...)
> Gilberto
> "Greg D. Moore (Strider)" wrote:
>
> > "pbd22" <dush...@.gmail.com> wrote in message
> >news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > > Hi.
> > > I am in the process of "ramping-up" (read: spending more money)
> > > on a home project. To date, I have done the web and database
> > > development on the same machine. I have been advised to put the
> > > DB stuff on its own box. My question is, what kind of box should this
> > > be? What are the essentials when buying hardware for a DB connection
> > > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > > etc).
> > "It depends" a lot on the nature of your data.
> > However, databases tend to like lots of RAM and fast disk channels.
> > I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> > as 10, or 5/50 for data) for data and logs.
> > And as much RAM as your version of SQL Server can use.
> > But beyond that, impossible to really advise w/o knowing more about your
> > database setup.
> > > Thanks for any tips and advice you have.
> > --
> > Greg Moore
> > SQL Server DBA Consulting Remote and Onsite available!
> > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
server
but, per your firewalling suggestion, would you advise putting an
additional
router in between the connection from the web server and the DB box?
Or,
should the operating system's own firewalling software be sufficient
protection?
thanks again.|||Honestly i have poor skill in firewalling software; however i think that a
separated firewall would work better, considering that in this case you let
both your servers (application and DB) better devoted to their natural
"vocation", not spending CPU and resources do defend your network against
intrusions.
Gilberto
"pbd22" wrote:
> On Mar 30, 3:34 am, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
> > Waiting for more infos about your database, i suggest another point of view:
> > I suppose that "home project" means something developed for your pleasure.
> > If not, and especially if you intend expose on the Network your Web Server,
> > it's strongly recommended to protect the dbserver (this probably would be the
> > meaning of "...its own box"). At the box should added security (firewalling
> > and so on...)
> > Gilberto
> >
> > "Greg D. Moore (Strider)" wrote:
> >
> >
> >
> > > "pbd22" <dush...@.gmail.com> wrote in message
> > >news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> > > > Hi.
> >
> > > > I am in the process of "ramping-up" (read: spending more money)
> > > > on a home project. To date, I have done the web and database
> > > > development on the same machine. I have been advised to put the
> > > > DB stuff on its own box. My question is, what kind of box should this
> > > > be? What are the essentials when buying hardware for a DB connection
> > > > (ie. serverboard vs. workstation motherboard, speen, disk space,
> > > > etc).
> >
> > > "It depends" a lot on the nature of your data.
> >
> > > However, databases tend to like lots of RAM and fast disk channels.
> >
> > > I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> > > as 10, or 5/50 for data) for data and logs.
> >
> > > And as much RAM as your version of SQL Server can use.
> >
> > > But beyond that, impossible to really advise w/o knowing more about your
> > > database setup.
> >
> > > > Thanks for any tips and advice you have.
> >
> > > --
> > > Greg Moore
> > > SQL Server DBA Consulting Remote and Onsite available!
> > > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>
> Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
> server
> but, per your firewalling suggestion, would you advise putting an
> additional
> router in between the connection from the web server and the DB box?
> Or,
> should the operating system's own firewalling software be sufficient
> protection?
> thanks again.
>|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:
>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.

Database Server Hardware Architecture

Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have."pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.

> Thanks for any tips and advice you have.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Waiting for more infos about your database, i suggest another point of view:
I suppose that "home project" means something developed for your pleasure.
If not, and especially if you intend expose on the Network your Web Server,
it's strongly recommended to protect the dbserver (this probably would be th
e
meaning of "...its own box"). At the box should added security (firewalling
and so on...)
Gilberto
"Greg D. Moore (Strider)" wrote:

>
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1175186968.716011.45140@.p77g2000hsh.googlegroups.com...
> "It depends" a lot on the nature of your data.
> However, databases tend to like lots of RAM and fast disk channels.
> I'd have at least 2 separate disk channels with RAID 1 (or other raid such
> as 10, or 5/50 for data) for data and logs.
> And as much RAM as your version of SQL Server can use.
> But beyond that, impossible to really advise w/o knowing more about your
> database setup.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com [url]http://www.greenms.com/sqlserver.html[/ur
l]
>
>|||On Mar 30, 3:34 am, Gilberto Zampatti
<GilbertoZampa...@.discussions.microsoft.com> wrote:[vbcol=seagreen]
> Waiting for more infos about your database, i suggest another point of vie
w:
> I suppose that "home project" means something developed for your pleasure.
> If not, and especially if you intend expose on the Network your Web Server
,
> it's strongly recommended to protect the dbserver (this probably would be
the
> meaning of "...its own box"). At the box should added security (firewallin
g
> and so on...)
> Gilberto
> "Greg D. Moore (Strider)" wrote:
>
>
>
>
>
>
>
>
>
>
Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
server
but, per your firewalling suggestion, would you advise putting an
additional
router in between the connection from the web server and the DB box?
Or,
should the operating system's own firewalling software be sufficient
protection?
thanks again.|||Honestly i have poor skill in firewalling software; however i think that a
separated firewall would work better, considering that in this case you let
both your servers (application and DB) better devoted to their natural
"vocation", not spending CPU and resources do defend your network against
intrusions.
Gilberto
"pbd22" wrote:

> On Mar 30, 3:34 am, Gilberto Zampatti
> <GilbertoZampa...@.discussions.microsoft.com> wrote:
>
> Thanks all for your advice. Gilberto, indeed this is a stand-alone DB
> server
> but, per your firewalling suggestion, would you advise putting an
> additional
> router in between the connection from the web server and the DB box?
> Or,
> should the operating system's own firewalling software be sufficient
> protection?
> thanks again.
>|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:

>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.

Database Server Hardware Architecture

Hi.
I am in the process of "ramping-up" (read: spending more money)
on a home project. To date, I have done the web and database
development on the same machine. I have been advised to put the
DB stuff on its own box. My question is, what kind of box should this
be? What are the essentials when buying hardware for a DB connection
(ie. serverboard vs. workstation motherboard, speen, disk space,
etc).
Thanks for any tips and advice you have.
"pbd22" <dushkin@.gmail.com> wrote in message
news:1175186968.716011.45140@.p77g2000hsh.googlegro ups.com...
> Hi.
> I am in the process of "ramping-up" (read: spending more money)
> on a home project. To date, I have done the web and database
> development on the same machine. I have been advised to put the
> DB stuff on its own box. My question is, what kind of box should this
> be? What are the essentials when buying hardware for a DB connection
> (ie. serverboard vs. workstation motherboard, speen, disk space,
> etc).
"It depends" a lot on the nature of your data.
However, databases tend to like lots of RAM and fast disk channels.
I'd have at least 2 separate disk channels with RAID 1 (or other raid such
as 10, or 5/50 for data) for data and logs.
And as much RAM as your version of SQL Server can use.
But beyond that, impossible to really advise w/o knowing more about your
database setup.

> Thanks for any tips and advice you have.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On 29 Mar 2007 09:49:28 -0700, "pbd22" <dushkin@.gmail.com> wrote:

>I am in the process of "ramping-up" (read: spending more money)
>on a home project. To date, I have done the web and database
>development on the same machine. I have been advised to put the
>DB stuff on its own box. My question is, what kind of box should this
>be? What are the essentials when buying hardware for a DB connection
>(ie. serverboard vs. workstation motherboard, speen, disk space,
>etc).
Besides other good advice, know that SQLServer likes multiple
processors. A single dual-core is good and might do you for a start,
but depending on your anticipated load, more might be (much) better.
When things go mondo you'll want to go 64-bit, but that's probably
overkill at the moment. Say, 4gb RAM would be a good start.
If the database is large and the contents valuable, you'll need some
backup plans. These days it's probably easiest to buy a stack of USB
drives and/or burn DVDs regularly, but do plan ahead!
Again, if the system is likely to be heavily loaded, gigabit LAN is a
good thing, too.
J.

Database Security Question - Can this be done?

Hello,
I am new to MS SQL Server and I am in the process of implementing a
database system which introduces an interesting security issue that I
was hoping some one could advise me on.
BACKGROUND: I am developing a client / server application that which
requires users to be able to download data from a global database and
then save this information in a local database. This enables them to
work offline and upload their local data to the global database at a
later date. FYI: The global DB is MS SQL, and the local database is
Paradox.
THE PROBLEM: The issue is that I dont want to give users the
ability/permissions to update, delete records from the global database
- because this would make it easy for hackers to simply corrupt the
database (i.e. delete * from <table> ). Also, the global database
contains data from a selection of companies and I must ensure that each
user can not see the other company's data.
So to summarise I have the following issues?
1. How do I restrict what users can see?
2. How do I prevent users from accessing data I dont want them to
manipulate (ie. restricting update / delete statements).
I would be gratful for any assistance you can provide.
Best regards
Spencer
(satest@.hotmail.com)The short answer is to use stored procedures and place execute permission on
those.
You can then filter out what can be seen by who.
Other methods include views.
Basically don't permission directly on the base tables.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Spence" <satest@.hotmail.com> wrote in message
news:1130760231.566603.254620@.g43g2000cwa.googlegroups.com...
> Hello,
> I am new to MS SQL Server and I am in the process of implementing a
> database system which introduces an interesting security issue that I
> was hoping some one could advise me on.
> BACKGROUND: I am developing a client / server application that which
> requires users to be able to download data from a global database and
> then save this information in a local database. This enables them to
> work offline and upload their local data to the global database at a
> later date. FYI: The global DB is MS SQL, and the local database is
> Paradox.
> THE PROBLEM: The issue is that I dont want to give users the
> ability/permissions to update, delete records from the global database
> - because this would make it easy for hackers to simply corrupt the
> database (i.e. delete * from <table> ). Also, the global database
> contains data from a selection of companies and I must ensure that each
> user can not see the other company's data.
> So to summarise I have the following issues?
> 1. How do I restrict what users can see?
> 2. How do I prevent users from accessing data I dont want them to
> manipulate (ie. restricting update / delete statements).
> I would be gratful for any assistance you can provide.
> Best regards
> Spencer
> (satest@.hotmail.com)
>|||From what you have described, the users don't really need access to the
Global database at all. In fact, they don't even need a login to the server.
What you can use is a DTS package that exports the appropriate from the
Global database to a distributed offline Paradox database located on a
network folder that is accessable by the users. Once the users have finished
inserting/updating/deleting the Paradox database, another DTS package can
migrate the data back into the Global database.
Also, you may want to consider using MS Access instead of Paradox for
the front end application/database. I don't know that much about Paradox,
but I would bet it's options for integrating with SQL Server are much more
limited than MS Access. Here is an article that describes the concepts of an
architecture for migrating data to and from a distributed MS Access
database.
http://www.microsoft.com/technet/pr...bldsysarch.mspx
"Spence" <satest@.hotmail.com> wrote in message
news:1130760231.566603.254620@.g43g2000cwa.googlegroups.com...
> Hello,
> I am new to MS SQL Server and I am in the process of implementing a
> database system which introduces an interesting security issue that I
> was hoping some one could advise me on.
> BACKGROUND: I am developing a client / server application that which
> requires users to be able to download data from a global database and
> then save this information in a local database. This enables them to
> work offline and upload their local data to the global database at a
> later date. FYI: The global DB is MS SQL, and the local database is
> Paradox.
> THE PROBLEM: The issue is that I dont want to give users the
> ability/permissions to update, delete records from the global database
> - because this would make it easy for hackers to simply corrupt the
> database (i.e. delete * from <table> ). Also, the global database
> contains data from a selection of companies and I must ensure that each
> user can not see the other company's data.
> So to summarise I have the following issues?
> 1. How do I restrict what users can see?
> 2. How do I prevent users from accessing data I dont want them to
> manipulate (ie. restricting update / delete statements).
> I would be gratful for any assistance you can provide.
> Best regards
> Spencer
> (satest@.hotmail.com)
>

Database Roles being modified by unknown process

I have two databases on two separate servers, with Merge replication running
between them. DB1 and DB2 both have the same 3 roles I created for user
access. Twice within the last week, the roles on DB2, the subscriber, have
changed removing most of their table permissions, but not all. Does anyone
know of something that could be causing this?
Thanks
Profiler is your friend - yo can trace Grant, Revoke and Deny commands like
all other commands and procedures. Check the "Security Audit Event Classes"
topic in Books OnLine.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"kahunaVA" <kahunaVA@.discussions.microsoft.com> wrote in message
news:6A920DFD-EFDC-4D23-8D97-4B51E65F6F40@.microsoft.com...
> I have two databases on two separate servers, with Merge replication
running
> between them. DB1 and DB2 both have the same 3 roles I created for user
> access. Twice within the last week, the roles on DB2, the subscriber,
have
> changed removing most of their table permissions, but not all. Does anyone
> know of something that could be causing this?
> Thanks

database restore from production to development

Hi,
I am in the process of overlaying our development data
with production backup. The users are different in prod as
compared with dev. My concern is if I restore production
copy on to dev, it might overwrite the sysusers info in
dev. I don't want to a) add prod users onto dev and b)
remove existing users in dev after the restore.
How do I keep my sysusers info intact ?
Currently I am thinking of
a) Get all users from dev and keep it aside
b) Restore the production onto dev
c) Delete all users in dev
d) Add users from (a) to dev
Will this work ? are there any other ways ?
TIA
MohanHave you looked at sp_change_users_login?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>|||Mo,
A way would be scripting out all database users on the development server
and run this script every time you restore the production backup. You can
create a sql agent job that restores the database and then runs this script
as an additional step.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>

database restore from production to development

Hi,
I am in the process of overlaying our development data
with production backup. The users are different in prod as
compared with dev. My concern is if I restore production
copy on to dev, it might overwrite the sysusers info in
dev. I don't want to a) add prod users onto dev and b)
remove existing users in dev after the restore.
How do I keep my sysusers info intact ?
Currently I am thinking of
a) Get all users from dev and keep it aside
b) Restore the production onto dev
c) Delete all users in dev
d) Add users from (a) to dev
Will this work ? are there any other ways ?
TIA
MohanHave you looked at sp_change_users_login?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>|||Mo,
A way would be scripting out all database users on the development server
and run this script every time you restore the production backup. You can
create a sql agent job that restores the database and then runs this script
as an additional step.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>

Database Restore Error - URGENT

I am trying to restore a database sent from a client and getting the below
error in the process:
Server: Msg 3270, Level 16, State 1, Line 1
An internal consistency error occurred. Contact Technical Support for
assistance.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Any ideas why ?.
It is a SQL Server 2000 backup going into another SQL Server 2000 server.
Thanks.DXC,
If their database is in an inconsistent state, the backup will carry that
state to you. They should run DBCC CHECKDB on their database to try to
determine what is wrong, fix it if possible, then send you a new backup.
RLF
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:267D80B1-6158-43C5-9252-46B8BCD169DE@.microsoft.com...
>I am trying to restore a database sent from a client and getting the below
> error in the process:
> Server: Msg 3270, Level 16, State 1, Line 1
> An internal consistency error occurred. Contact Technical Support for
> assistance.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Any ideas why ?.
> It is a SQL Server 2000 backup going into another SQL Server 2000 server.
> Thanks.|||Russell, Thanks for the quick reply...........Also, I get the following
message when I run RESTORE VERIFYONLY.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'D:\MTTBRP7_db_200802182100.BAK' is not a valid Microsoft
Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
"Russell Fields" wrote:
> DXC,
> If their database is in an inconsistent state, the backup will carry that
> state to you. They should run DBCC CHECKDB on their database to try to
> determine what is wrong, fix it if possible, then send you a new backup.
> RLF
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:267D80B1-6158-43C5-9252-46B8BCD169DE@.microsoft.com...
> >I am trying to restore a database sent from a client and getting the below
> > error in the process:
> >
> > Server: Msg 3270, Level 16, State 1, Line 1
> > An internal consistency error occurred. Contact Technical Support for
> > assistance.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > Any ideas why ?.
> >
> > It is a SQL Server 2000 backup going into another SQL Server 2000 server.
> >
> > Thanks.
>
>|||DXC,
Sorry, I don't really know, but here are a couple of posts that might help
you think of something. (I don't suppose that this backup is from SQL
Server 6.5 or earlier, is it?)
http://msdn2.microsoft.com/en-us/library/aa238234(SQL.80).aspx
http://support.microsoft.com/?kbid=905616
http://www.webservertalk.com/archive132-2004-7-330939.html
RLF
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8803F9CE-B0A1-4E5B-A39F-BF73DFDBC1C0@.microsoft.com...
> Russell, Thanks for the quick reply...........Also, I get the following
> message when I run RESTORE VERIFYONLY.
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'D:\MTTBRP7_db_200802182100.BAK' is not a valid
> Microsoft
> Tape Format backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> "Russell Fields" wrote:
>> DXC,
>> If their database is in an inconsistent state, the backup will carry that
>> state to you. They should run DBCC CHECKDB on their database to try to
>> determine what is wrong, fix it if possible, then send you a new backup.
>> RLF
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:267D80B1-6158-43C5-9252-46B8BCD169DE@.microsoft.com...
>> >I am trying to restore a database sent from a client and getting the
>> >below
>> > error in the process:
>> >
>> > Server: Msg 3270, Level 16, State 1, Line 1
>> > An internal consistency error occurred. Contact Technical Support for
>> > assistance.
>> > Server: Msg 3013, Level 16, State 1, Line 1
>> > RESTORE DATABASE is terminating abnormally.
>> >
>> > Any ideas why ?.
>> >
>> > It is a SQL Server 2000 backup going into another SQL Server 2000
>> > server.
>> >
>> > Thanks.
>>|||No it is not. It is from SQL Server 2000 server............Thanks......
"Russell Fields" wrote:
> DXC,
> Sorry, I don't really know, but here are a couple of posts that might help
> you think of something. (I don't suppose that this backup is from SQL
> Server 6.5 or earlier, is it?)
> http://msdn2.microsoft.com/en-us/library/aa238234(SQL.80).aspx
> http://support.microsoft.com/?kbid=905616
> http://www.webservertalk.com/archive132-2004-7-330939.html
> RLF
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8803F9CE-B0A1-4E5B-A39F-BF73DFDBC1C0@.microsoft.com...
> > Russell, Thanks for the quick reply...........Also, I get the following
> > message when I run RESTORE VERIFYONLY.
> >
> > Server: Msg 3242, Level 16, State 2, Line 1
> > The file on device 'D:\MTTBRP7_db_200802182100.BAK' is not a valid
> > Microsoft
> > Tape Format backup set.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > VERIFY DATABASE is terminating abnormally.
> >
> >
> > "Russell Fields" wrote:
> >
> >> DXC,
> >>
> >> If their database is in an inconsistent state, the backup will carry that
> >> state to you. They should run DBCC CHECKDB on their database to try to
> >> determine what is wrong, fix it if possible, then send you a new backup.
> >>
> >> RLF
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:267D80B1-6158-43C5-9252-46B8BCD169DE@.microsoft.com...
> >> >I am trying to restore a database sent from a client and getting the
> >> >below
> >> > error in the process:
> >> >
> >> > Server: Msg 3270, Level 16, State 1, Line 1
> >> > An internal consistency error occurred. Contact Technical Support for
> >> > assistance.
> >> > Server: Msg 3013, Level 16, State 1, Line 1
> >> > RESTORE DATABASE is terminating abnormally.
> >> >
> >> > Any ideas why ?.
> >> >
> >> > It is a SQL Server 2000 backup going into another SQL Server 2000
> >> > server.
> >> >
> >> > Thanks.
> >>
> >>
> >>
>
>|||DXC,
I don't know what else to do except get another backup from them. Were you
able to do that and did it work?
RLF
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:FFA04E40-48DA-4590-9832-EC44415EFFDB@.microsoft.com...
> No it is not. It is from SQL Server 2000
> server............Thanks......
> "Russell Fields" wrote:
>> DXC,
>> Sorry, I don't really know, but here are a couple of posts that might
>> help
>> you think of something. (I don't suppose that this backup is from SQL
>> Server 6.5 or earlier, is it?)
>> http://msdn2.microsoft.com/en-us/library/aa238234(SQL.80).aspx
>> http://support.microsoft.com/?kbid=905616
>> http://www.webservertalk.com/archive132-2004-7-330939.html
>> RLF
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:8803F9CE-B0A1-4E5B-A39F-BF73DFDBC1C0@.microsoft.com...
>> > Russell, Thanks for the quick reply...........Also, I get the
>> > following
>> > message when I run RESTORE VERIFYONLY.
>> >
>> > Server: Msg 3242, Level 16, State 2, Line 1
>> > The file on device 'D:\MTTBRP7_db_200802182100.BAK' is not a valid
>> > Microsoft
>> > Tape Format backup set.
>> > Server: Msg 3013, Level 16, State 1, Line 1
>> > VERIFY DATABASE is terminating abnormally.
>> >
>> >
>> > "Russell Fields" wrote:
>> >
>> >> DXC,
>> >>
>> >> If their database is in an inconsistent state, the backup will carry
>> >> that
>> >> state to you. They should run DBCC CHECKDB on their database to try
>> >> to
>> >> determine what is wrong, fix it if possible, then send you a new
>> >> backup.
>> >>
>> >> RLF
>> >>
>> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> >> news:267D80B1-6158-43C5-9252-46B8BCD169DE@.microsoft.com...
>> >> >I am trying to restore a database sent from a client and getting the
>> >> >below
>> >> > error in the process:
>> >> >
>> >> > Server: Msg 3270, Level 16, State 1, Line 1
>> >> > An internal consistency error occurred. Contact Technical Support
>> >> > for
>> >> > assistance.
>> >> > Server: Msg 3013, Level 16, State 1, Line 1
>> >> > RESTORE DATABASE is terminating abnormally.
>> >> >
>> >> > Any ideas why ?.
>> >> >
>> >> > It is a SQL Server 2000 backup going into another SQL Server 2000
>> >> > server.
>> >> >
>> >> > Thanks.
>> >>
>> >>
>> >>
>>

Database Permissions

We have a system at work that copies (using DTS) over databases from
one SQL Server box to another every night. The copying process drops
each target object and then recreates them. On the 2nd SQL Server box
I have an account setup that is supposed to only have access to run
queries on the databases that get copied over every night. However,
because the DTS packages are dropping the objects first we are losing
the table level permissions for this user, so this user can't access
these databases the next day. Is there a way to automate resetting
these permissions on each table in the databases? Perhaps I should be
using replication as opposed to DTS packages for copying over entire
databases? If I used replication, would this avoid losing the
permissions that I need?

Thanks,

Jeffjeffpuro@.yahoo.com (Jeff) wrote in message news:<7851a310.0401151322.4b8cf2e7@.posting.google.com>...
> We have a system at work that copies (using DTS) over databases from
> one SQL Server box to another every night. The copying process drops
> each target object and then recreates them. On the 2nd SQL Server box
> I have an account setup that is supposed to only have access to run
> queries on the databases that get copied over every night. However,
> because the DTS packages are dropping the objects first we are losing
> the table level permissions for this user, so this user can't access
> these databases the next day. Is there a way to automate resetting
> these permissions on each table in the databases? Perhaps I should be
> using replication as opposed to DTS packages for copying over entire
> databases? If I used replication, would this avoid losing the
> permissions that I need?
> Thanks,
> Jeff

You could script the table permissions, then execute that script after
copying the objects. Although it's not clear from your post why you
always drop and recreate the objects - DTS can copy only the data, or
you can put it in staging tables first, then insert into the final
production tables if you have some mapping logic. Replication would be
an option (perhaps snapshot replication), but it can be complex.

Simon