Database Compatibility Mode - when to change??

I have been migrating Databases from a SQL Server 7.0 Instance to a 2000 Instance. Basically the method I use is as follows:

a)Create a new Database on the Destination Server (same name as the Source) When I create the new Database on my destination server, the compatibility mode is '80' and the source is always '70'

b) do a 'revlogin' on the Source Server and use the output from that query to recreate the logins on the destination server

c) make sure that the default DB for the newly creates logins are correct and change them if necessary

d) Backup the DB on the Source Server and Restore it to the Destination Server.

e) Check login permissions and fix any orphaned users - usually I don't find any that need to be fixed, but I always check.

I've read BOL on Changing the compatibility mode on the Database... but I'm still unsure when I would have to do this and why?????? SHOULD I be changing the compatiblity mode from 80 to 70 when migrating Databases from 7.0 to 2000?? Any advice on moving DBs in this manner would be appreciated.sounds like you're doing it correctly. I always changed the compatability mode as the very last step...I know I've had an issue when I didn't change it - at one point, but it happened a very long time ago, and I can't remember the specifics - I think it had something to do with Quoted Identifiers...if you have procs that use double quotes instead of single quotes to identify text fields...it was pretty bizarre.|||Thanks much for the reply!

Database back-up

Hi there,
What we basically want is seperating the development and production
servers. I need to transfer all the data to the new server from the
existing server.
My question is that what's a good way to transfer schema, tables and
data from one SQL Server to another. I have tried export option in the
enterprise manager, but I guess it doesn't transfer things like pk,
relationships etc or does it?
Once I have all data on the other server, I can set up a Trans
Replication, so that I get same data on both servers.
Thanks in advance.
Regards,
Ricky Singh
--
Posted via http://dbforums.comYou could create backups of your main databases and restore them into other
environments. Make sure you desensitize info like credit card numbers etc.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3059366.1057005218@.dbforums.com...
Hi there,
What we basically want is seperating the development and production
servers. I need to transfer all the data to the new server from the
existing server.
My question is that what's a good way to transfer schema, tables and
data from one SQL Server to another. I have tried export option in the
enterprise manager, but I guess it doesn't transfer things like pk,
relationships etc or does it?
Once I have all data on the other server, I can set up a Trans
Replication, so that I get same data on both servers.
Thanks in advance.
Regards,
Ricky Singh
--
Posted via http://dbforums.com|||Originally posted by Quentin Ran
> IMO backing up and then restore on the new machine is a easy,
> convenient
> way.
> Quentin
> Thanks for the replies. I m a newbie on SQL Server. Can I back-up
> using Tools-Backup database on enterprise manager and then save the
> file over WAN' I was trying doing that but cudn't see the mapped
> network drive over the WAN!!
>
> "Ricky_Singh" wrote in message
> news:3059366.1057005218@.dbforums.com"]news:3059366.1057005218@.d-
> bforums.com[/url]...
> > Hi there,
> > What we basically want is seperating the development and
> production
> > servers. I need to transfer all the data to the new server from
> the
> > existing server.
> > My question is that what's a good way to transfer schema, tables
> and
> > data from one SQL Server to another. I have tried export option
> in the
> > enterprise manager, but I guess it doesn't transfer things like
> pk,
> > relationships etc or does it?
> > Once I have all data on the other server, I can set up a
> Trans
> > Replication, so that I get same data on both servers.
> > Thanks in advance.
> > Regards,
> > Ricky Singh
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.com|||Ricky,
I never tried that. I believe you can backup to a mapped LAN drive. If you
have this option, you can then transfer the file over to the WAN site.
Quentin
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3059937.1057009483@.dbforums.com...
> Originally posted by Quentin Ran
> > IMO backing up and then restore on the new machine is a easy,
> > convenient
> > way.
> >
> > Quentin
> >
> > Thanks for the replies. I m a newbie on SQL Server. Can I back-up
> > using Tools-Backup database on enterprise manager and then save the
> > file over WAN' I was trying doing that but cudn't see the mapped
> > network drive over the WAN!!
> >
> >
> > "Ricky_Singh" wrote in message
> > news:3059366.1057005218@.dbforums.com"]news:3059366.1057005218@.d-
> > bforums.com[/url]...
> > > Hi there,
> > > What we basically want is seperating the development and
> > production
> > > servers. I need to transfer all the data to the new server from
> > the
> > > existing server.
> > > My question is that what's a good way to transfer schema, tables
> > and
> > > data from one SQL Server to another. I have tried export option
> > in the
> > > enterprise manager, but I guess it doesn't transfer things like
> > pk,
> > > relationships etc or does it?
> > > Once I have all data on the other server, I can set up a
> > Trans
> > > Replication, so that I get same data on both servers.
> > > Thanks in advance.
> > > Regards,
> > > Ricky Singh
> > > --
> > Posted via
> http://dbforums.com/http://dbforums.com
>
> --
> Posted via http://dbforums.com|||Originally posted by Quentin Ran
> Ricky,
> I never tried that. I believe you can backup to a mapped LAN
> drive. If you
> have this option, you can then transfer the file over to the WAN site.
> Quentin
> Thanks a lot Quentin, I ll try that. Another question I have is that
> if I make a copy of the database file, Can I use that copy for restore
> at another SQL Server location.
> Thanks again!!
> "Ricky_Singh" wrote in message
> news:3059937.1057009483@.dbforums.com"]news:3059937.1057009483@.d-
> bforums.com[/url]...
> > Originally posted by Quentin Ran
> > > IMO backing up and then restore on the new machine is a
> easy,
> > > convenient
> > > way.
> > >
> > > Quentin
> > >
> > > Thanks for the replies. I m a newbie on SQL Server. Can I
> back-up
> > > using Tools-Backup database on enterprise manager and then
> save the
> > > file over WAN' I was trying doing that but cudn't see the
> mapped
> > > network drive over the WAN!!
> > >
> > >
> > > "Ricky_Singh" wrote in message
> > > news:3059366.1057005218@.dbforums.com"]news:3059366.1057-
> 005218@.dbforums.com[/url]"]news:3059366.1057005218@.d-"]news-
> :3059366.1057005218@.d-[/url]
> > > bforums.com[/url]...
> > > > Hi there,
> > > > What we basically want is seperating the development
> and
> > > production
> > > > servers. I need to transfer all the data to the new server
> from
> > > the
> > > > existing server.
> > > > My question is that what's a good way to transfer schema,
> tables
> > > and
> > > > data from one SQL Server to another. I have tried export
> option
> > > in the
> > > > enterprise manager, but I guess it doesn't transfer things
> like
> > > pk,
> > > > relationships etc or does it?
> > > > Once I have all data on the other server, I can set up
> a
> > > Trans
> > > > Replication, so that I get same data on both servers.
> > > > Thanks in advance.
> > > > Regards,
> > > > Ricky Singh
> > > > --
> > > Posted via
> > http://dbforums.com/http://dbforums.com"]http://dbfor-
> ums.com/http://dbforums.com[/url]
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.com|||Certainly.
"Ricky_Singh" <member32195@.dbforums.com> wrote in message
news:3062421.1057073733@.dbforums.com...
> Originally posted by Quentin Ran
> > Ricky,
> >
> > I never tried that. I believe you can backup to a mapped LAN
> > drive. If you
> > have this option, you can then transfer the file over to the WAN site.
> >
> > Quentin
> >
> > Thanks a lot Quentin, I ll try that. Another question I have is that
> > if I make a copy of the database file, Can I use that copy for restore
> > at another SQL Server location.
> > Thanks again!!
> >
> > "Ricky_Singh" wrote in message
> > news:3059937.1057009483@.dbforums.com"]news:3059937.1057009483@.d-
> > bforums.com[/url]...
> > > Originally posted by Quentin Ran
> > > > IMO backing up and then restore on the new machine is a
> > easy,
> > > > convenient
> > > > way.
> > > >
> > > > Quentin
> > > >
> > > > Thanks for the replies. I m a newbie on SQL Server. Can I
> > back-up
> > > > using Tools-Backup database on enterprise manager and then
> > save the
> > > > file over WAN' I was trying doing that but cudn't see the
> > mapped
> > > > network drive over the WAN!!
> > > >
> > > >
> > > > "Ricky_Singh" wrote in message
> > > > news:3059366.1057005218@.dbforums.com"]news:3059366.1057-
> > 005218@.dbforums.com[/url]"]news:3059366.1057005218@.d-"]news-
> > :3059366.1057005218@.d-[/url]
> > > > bforums.com[/url]...
> > > > > Hi there,
> > > > > What we basically want is seperating the development
> > and
> > > > production
> > > > > servers. I need to transfer all the data to the new server
> > from
> > > > the
> > > > > existing server.
> > > > > My question is that what's a good way to transfer schema,
> > tables
> > > > and
> > > > > data from one SQL Server to another. I have tried export
> > option
> > > > in the
> > > > > enterprise manager, but I guess it doesn't transfer things
> > like
> > > > pk,
> > > > > relationships etc or does it?
> > > > > Once I have all data on the other server, I can set up
> > a
> > > > Trans
> > > > > Replication, so that I get same data on both servers.
> > > > > Thanks in advance.
> > > > > Regards,
> > > > > Ricky Singh
> > > > > --
> > > > Posted via
> > > http://dbforums.com/http://dbforums.com"]http://dbfor-
> > ums.com/http://dbforums.com[/url]
> > > --
> > Posted via
> http://dbforums.com/http://dbforums.com
>
> --
> Posted via http://dbforums.com|||Originally posted by Quentin Ran
> Certainly.
> The problem I m facing is that when I try to back-up the server, I am
> unable to see the mapped network drive. I see a dialog box with the
> server name and the drives on that server, but no mapped drives.
> Thanks for the help.
> "Ricky_Singh" wrote in message
> news:3062421.1057073733@.dbforums.com"]news:3062421.1057073733@.d-
> bforums.com[/url]...
> > Originally posted by Quentin Ran
> > > Ricky,
> > >
> > > I never tried that. I believe you can backup to a mapped
> LAN
> > > drive. If you
> > > have this option, you can then transfer the file over to the
> WAN site.
> > >
> > > Quentin
> > >
> > > Thanks a lot Quentin, I ll try that. Another question I have
> is that
> > > if I make a copy of the database file, Can I use that copy for
> restore
> > > at another SQL Server location.
> > > Thanks again!!
> > >
> > > "Ricky_Singh" wrote in message
> > > news:3059937.1057009483@.dbforums.com"]news:3059937.1057-
> 009483@.dbforums.com[/url]"]news:3059937.1057009483@.d-"]news-
> :3059937.1057009483@.d-[/url]
> > > bforums.com[/url]...
> > > > Originally posted by Quentin Ran
> > > > > IMO backing up and then restore on the new machine is
> a
> > > easy,
> > > > > convenient
> > > > > way.
> > > > >
> > > > > Quentin
> > > > >
> > > > > Thanks for the replies. I m a newbie on SQL Server. Can
> I
> > > back-up
> > > > > using Tools-Backup database on enterprise manager and
> then
> > > save the
> > > > > file over WAN' I was trying doing that but cudn't see
> the
> > > mapped
> > > > > network drive over the WAN!!
> > > > >
> > > > >
> > > > > "Ricky_Singh" wrote in message
> > > > > news:3059366.1057005218@.dbforums.com"]news:3059366.-
> 1057005218@.dbforums.com[/url]"]news:3059366.1057-"]news:305-
> 9366.1057-[/url]
> > > 005218@.dbforums.com[/url]"]news:3059366.1057005218@.-
> d-news:3059366.1057005218@.d-"]news-
> > > :3059366.1057005218@.d-[/url]
> > > > > bforums.com[/url]...
> > > > > > Hi there,
> > > > > > What we basically want is seperating the
> development
> > > and
> > > > > production
> > > > > > servers. I need to transfer all the data to the new
> server
> > > from
> > > > > the
> > > > > > existing server.
> > > > > > My question is that what's a good way to transfer
> schema,
> > > tables
> > > > > and
> > > > > > data from one SQL Server to another. I have tried
> export
> > > option
> > > > > in the
> > > > > > enterprise manager, but I guess it doesn't transfer
> things
> > > like
> > > > > pk,
> > > > > > relationships etc or does it?
> > > > > > Once I have all data on the other server, I can set
> up
> > > a
> > > > > Trans
> > > > > > Replication, so that I get same data on both
> servers.
> > > > > > Thanks in advance.
> > > > > > Regards,
> > > > > > Ricky Singh
> > > > > > --
> > > > > Posted via
> > > > http://dbforums.com/http://dbforums.com"]http://d-
> bforums.com/http://dbforums.com[/url]"]http://dbfor-/"]http-
> ://dbfor-[/url]
> > > ums.com/http://dbforums.com[/url]"]http://dbforums.-
> com[/url][/url]
> > > > --
> > > Posted via
> > http://dbforums.com/http://dbforums.com"]http://dbfor-
> ums.com/http://dbforums.com[/url]
> > --
> Posted via
http://dbforums.com/http://dbforums.com
Posted via http://dbforums.comsql

Database & Flash Wear Management

Hello everyone,

I'm currently developping a windows .net compact framework application which is basically a local datalogger.

Since my application will log data to the database (located on compact flash card) a few times a second over long period, I wonder if SQL Server Compact Edition offers some mechanism to reduce disk access.
By example, can SQL Server compact edition wait let's say 5-10 "insert into" commands before actually write to the database located on the flash card ?.

Any ideas which could help me to reduce flash wear would be greatly appreciated !

Thanks

The storage engine introduced in SQL Mobile (v3.0) and currently in SQL CE is storage-card aware.

If you feel you need to queue up inserts to further reduce writes to the card, that would be something that

you would need to code into your logger application.

My suggestion, instead of queuing your DML commands would be to periodically perform a Verify

and if needed a Repair on the database itself. See the SqlCeEngine documentation for samples

of how to perform these checks. Repair will reorganize the database (reorder indexes, reclaim

unused page space, etc) and int he process the physical file will be rewritten on the storage card.

Regards,

Darren Shaffer

|||

Thank you very much ! It's very appreciated

Regards,

Emmanuel