SQLAgentMail: databases

Showing posts with label databases. Show all posts

Friday, March 30, 2012

question sql server

what is the best way to distribute one stored procedure to 50
different databases in 50 different servers?
<soalvajavab1@.yahoo.com> wrote in message
news:1178036848.568869.225690@.o5g2000hsb.googlegro ups.com...
> what is the best way to distribute one stored procedure to 50
> different databases in 50 different servers?
>
Script it out and use OSQL in a batch file.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||On May 1, 10:31 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <soalvajav...@.yahoo.com> wrote in message
> news:1178036848.568869.225690@.o5g2000hsb.googlegro ups.com...
>
> Script it out and use OSQL in a batch file.
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Is this the best way?
On other topic, what is the best practice to take care of syslogs?
and what is the best way to move a large database?
thanks
|||<soalvajavab1@.yahoo.com> wrote in message
news:1178053985.861219.308440@.y80g2000hsf.googlegr oups.com...
> On May 1, 10:31 am, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>
> Is this the best way?
Given so little detail, it's hard to say, but it's definitely one possible
way.

> On other topic, what is the best practice to take care of syslogs?
>
Take care of how?

> and what is the best way to move a large database?
Depends on if you can take downtime or not, version of SQL etc.

> thanks
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

question sql server

what is the best way to distribute one stored procedure to 50
different databases in 50 different servers?<soalvajavab1@.yahoo.com> wrote in message
news:1178036848.568869.225690@.o5g2000hsb.googlegroups.com...
> what is the best way to distribute one stored procedure to 50
> different databases in 50 different servers?
>
Script it out and use OSQL in a batch file.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On May 1, 10:31 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:[vbcol=seagreen]
> <soalvajav...@.yahoo.com> wrote in message
> news:1178036848.568869.225690@.o5g2000hsb.googlegroups.com...
>
> Script it out and use OSQL in a batch file.
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html[/vbco
l]
Is this the best way?
On other topic, what is the best practice to take care of syslogs?
and what is the best way to move a large database?
thanks|||<soalvajavab1@.yahoo.com> wrote in message
news:1178053985.861219.308440@.y80g2000hsf.googlegroups.com...
> On May 1, 10:31 am, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>
> Is this the best way?
Given so little detail, it's hard to say, but it's definitely one possible
way.

> On other topic, what is the best practice to take care of syslogs?
>
Take care of how?

> and what is the best way to move a large database?
Depends on if you can take downtime or not, version of SQL etc.

> thanks
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

question sql server

what is the best way to distribute one stored procedure to 50
different databases in 50 different servers?<soalvajavab1@.yahoo.com> wrote in message
news:1178036848.568869.225690@.o5g2000hsb.googlegroups.com...
> what is the best way to distribute one stored procedure to 50
> different databases in 50 different servers?
>
Script it out and use OSQL in a batch file.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On May 1, 10:31 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <soalvajav...@.yahoo.com> wrote in message
> news:1178036848.568869.225690@.o5g2000hsb.googlegroups.com...
> > what is the best way to distribute one stored procedure to 50
> > different databases in 50 different servers?
> Script it out and use OSQL in a batch file.
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Is this the best way?
On other topic, what is the best practice to take care of syslogs?
and what is the best way to move a large database?
thanks|||<soalvajavab1@.yahoo.com> wrote in message
news:1178053985.861219.308440@.y80g2000hsf.googlegroups.com...
> On May 1, 10:31 am, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>> <soalvajav...@.yahoo.com> wrote in message
>> news:1178036848.568869.225690@.o5g2000hsb.googlegroups.com...
>> > what is the best way to distribute one stored procedure to 50
>> > different databases in 50 different servers?
>> Script it out and use OSQL in a batch file.
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com
>> http://www.greenms.com/sqlserver.html
>
> Is this the best way?
Given so little detail, it's hard to say, but it's definitely one possible
way.
> On other topic, what is the best practice to take care of syslogs?
>
Take care of how?
> and what is the best way to move a large database?
Depends on if you can take downtime or not, version of SQL etc.
> thanks
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Monday, March 26, 2012

Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.
|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.
|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.

Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.

Question on using SQLBulkCopy

I need to copy large amounts of data between SQL databases, and between SQL and Access. I've been reading a lot of good explanations of SQLBulkCopy, but the only good examples I've found are written in C# and I work in VB. I'm very new to ASP.NET 2.0 (about a month) and came from Classic ASP, not .NET 1.1. I'm still getting my feet wet and I'm afraid it doesn't take much to confuse me. Can someone point me to a good article or tutorial that includes a clear example written in VB?

Diane

The MSDN Library has a decent example in all languages:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Friday, March 23, 2012

Question on SSIS and .NET 1.1

We are migrating our databases from SQL Server 2000 on Win2k to SQL Server 2005 on Win 2k3 machines. Our applications useDTS packages , like creating dynamically in the (application) front end dynamically and sometimes just passing global parameters.

Now with SQL Server 2005 , we have decided to move to SSIS, as SQL Server 2005 has limited support for DTS. Now we were using a com+ assembly from microsoft for using these DTS packages from the front-end(Application). Can we accomplish the same with SSIS using applications developed in .Net Framework 1.1? Are there any work arounds? If not how can we accomplish this in 1.1 framework. Does SSIS expose any API that can be leveraged using .NET 1.1?

Did anyone face any other problems apart from these trying to make 1.1 apps talk to SQL 2005?

I guess this is more of a question to Microsoft experts, or programmers who already had similar experience already migrating to 2005.

It is hard to find information online on this subject. If anyone has valuable information regarding this, please reply with links..

Thanks in advance,

My understanding is that a .NET 1.1 application cannot interoperate directly with .NET 2.0 libraries such as the SSIS API. A web service should fit the bill nicely, though as far as just executing packages and passing in variables. If you're actually generating packages, that logic would probably need to be moved completely into the service which would also help insulate your application against future SSIS API changes. Your .NET 1.1 app could talk to the .NET 2.0 service, which could in turn automate the SSIS APIs.

In general, a .NET 1.1 application should not have much trouble making the switch from SQL Server 2000 to 2005, especially if you're using managed providers and calling stored procedures. If the app is calling the database with direct SQL queries, then you may have some minor syntax issues. If you're using procedures the Upgrade Advisor should be able to notify you of any problems, otherwise you'll have to test thoroughly.|||

In addition to nice Jay's wrap up, some mode ideas:

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

|||Thanks for all the replies..

Wednesday, March 21, 2012

Question on SQL Server and the cache

I'm new to databases and I'm trying to understand how the memory cache
works with sql server (I think Oracle calls it the "Block Buffer")
I fire up sql server
I query a table (ie select * from mytable where myid = 10)
This query's plan ends up in cache?
Do the returned rows end up there too?
Does it matter if myid is an index or not for the cache to utilised?
If the query did not have a where clause, would the entire table end up in
memory?
Sorry for the dumb questions... just trying learn
TIA> This query's plan ends up in cache?
Maybe. SQL Server caches plans to avoid the compilation costs. However,
trivial plans (probably this one) are not cached since little work is needed
to compile.
> Do the returned rows end up there too?
Data are read into the buffer cache. Execution plans are kept in separate
plan cache.
> Does it matter if myid is an index or not for the cache to utilised?
No.
> If the query did not have a where clause, would the entire table end up in
> memory?
If there is sufficient buffer cache, yes. If not, cache is reused according
to (roughly) a LRU algorithm so that the most recent and frequently accessed
data stay in cache.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dodo Lurker" <none@.noemailplease> wrote in message
news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
> I'm new to databases and I'm trying to understand how the memory cache
> works with sql server (I think Oracle calls it the "Block Buffer")
> I fire up sql server
> I query a table (ie select * from mytable where myid = 10)
> This query's plan ends up in cache?
> Do the returned rows end up there too?
> Does it matter if myid is an index or not for the cache to utilised?
> If the query did not have a where clause, would the entire table end up in
> memory?
> Sorry for the dumb questions... just trying learn
> TIA
>|||Some additional information inline...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> This query's plan ends up in cache?
> Maybe. SQL Server caches plans to avoid the compilation costs. However, trivial plans (probably
> this one) are not cached since little work is needed to compile.
Check the syscacheobjects table. Cotains one row for each plan. You can see how much a plan has been
reused, type of plan etc.
>> Do the returned rows end up there too?
> Data are read into the buffer cache. Execution plans are kept in separate plan cache.
By "Data", Dan is referring to data pages as well as index pages.
>> Does it matter if myid is an index or not for the cache to utilised?
> No.
>> If the query did not have a where clause, would the entire table end up in
>> memory?
> If there is sufficient buffer cache, yes. If not, cache is reused according to (roughly) a LRU
> algorithm so that the most recent and frequently accessed data stay in cache.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dodo Lurker" <none@.noemailplease> wrote in message news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
>> I'm new to databases and I'm trying to understand how the memory cache
>> works with sql server (I think Oracle calls it the "Block Buffer")
>> I fire up sql server
>> I query a table (ie select * from mytable where myid = 10)
>> This query's plan ends up in cache?
>> Do the returned rows end up there too?
>> Does it matter if myid is an index or not for the cache to utilised?
>> If the query did not have a where clause, would the entire table end up in
>> memory?
>> Sorry for the dumb questions... just trying learn
>> TIA
>>
>|||Thanks guys
Some additional questions
What does the cache hit ratio relate to? The data or the index pages being
in memory? Or both?
By LRU algorithm, you mean the lazy writer process, correct? From my
reading, it seems that this sweeps through and ages out stale queries from
the query cache to make room for new queries. At the same time, does it age
out the data and index pages from their data and index caches that were
generated from the aged out query?
You guys are a great help. I struggle a lot because I'm more of a visual
learner (ie I need to see process flows rather than just read text). I
can't seem to find good material.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#O8AqUtAGHA.3584@.TK2MSFTNGP14.phx.gbl...
> Some additional information inline...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
> >> This query's plan ends up in cache?
> >
> > Maybe. SQL Server caches plans to avoid the compilation costs.
However, trivial plans (probably
> > this one) are not cached since little work is needed to compile.
> Check the syscacheobjects table. Cotains one row for each plan. You can
see how much a plan has been
> reused, type of plan etc.
>
> >
> >> Do the returned rows end up there too?
> >
> > Data are read into the buffer cache. Execution plans are kept in
separate plan cache.
> By "Data", Dan is referring to data pages as well as index pages.
>
> >
> >> Does it matter if myid is an index or not for the cache to utilised?
> >
> > No.
> >
> >> If the query did not have a where clause, would the entire table end up
in
> >> memory?
> >
> > If there is sufficient buffer cache, yes. If not, cache is reused
according to (roughly) a LRU
> > algorithm so that the most recent and frequently accessed data stay in
cache.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Dodo Lurker" <none@.noemailplease> wrote in message
news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
> >> I'm new to databases and I'm trying to understand how the memory cache
> >> works with sql server (I think Oracle calls it the "Block Buffer")
> >>
> >> I fire up sql server
> >> I query a table (ie select * from mytable where myid = 10)
> >>
> >> This query's plan ends up in cache?
> >> Do the returned rows end up there too?
> >> Does it matter if myid is an index or not for the cache to utilised?
> >> If the query did not have a where clause, would the entire table end up
in
> >> memory?
> >>
> >> Sorry for the dumb questions... just trying learn
> >>
> >> TIA
> >>
> >>
> >
> >
>|||> What does the cache hit ratio relate to? The data or the index pages being
> in memory? Or both?
In perfmon, you have cache hit ration counters for data/index pages as well as for plan pages.
> By LRU algorithm, you mean the lazy writer process, correct? From my
> reading, it seems that this sweeps through and ages out stale queries from
> the query cache to make room for new queries. At the same time, does it age
> out the data and index pages from their data and index caches that were
> generated from the aged out query?
Yes. Each page has a cost associated. A bit simplified, we can say that each reference to a page
increases the cost by 1. Each time Lazywriter sweeps, the cost for each page decreases by 1. When
cost goes to 0, the page goes in the free list. The algorithm for plan pages is a bit different, but
the same principal applies. So apart for the costing algorithms, data/index pages are handled the
same way as plan pages.
> I
> can't seem to find good material.
I have a feeling that "Inside SQL Server" from MS Press, by Kalen Delaney has some good info on
this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dodo Lurker" <none@.noemailplease> wrote in message
news:kqSdnZDRD4fItDnenZ2dnUVZ_vidnZ2d@.comcast.com...
> Thanks guys
> Some additional questions
> What does the cache hit ratio relate to? The data or the index pages being
> in memory? Or both?
> By LRU algorithm, you mean the lazy writer process, correct? From my
> reading, it seems that this sweeps through and ages out stale queries from
> the query cache to make room for new queries. At the same time, does it age
> out the data and index pages from their data and index caches that were
> generated from the aged out query?
> You guys are a great help. I struggle a lot because I'm more of a visual
> learner (ie I need to see process flows rather than just read text). I
> can't seem to find good material.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#O8AqUtAGHA.3584@.TK2MSFTNGP14.phx.gbl...
>> Some additional information inline...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:OU8xOqrAGHA.2704@.TK2MSFTNGP15.phx.gbl...
>> >> This query's plan ends up in cache?
>> >
>> > Maybe. SQL Server caches plans to avoid the compilation costs.
> However, trivial plans (probably
>> > this one) are not cached since little work is needed to compile.
>> Check the syscacheobjects table. Cotains one row for each plan. You can
> see how much a plan has been
>> reused, type of plan etc.
>>
>> >
>> >> Do the returned rows end up there too?
>> >
>> > Data are read into the buffer cache. Execution plans are kept in
> separate plan cache.
>> By "Data", Dan is referring to data pages as well as index pages.
>>
>> >
>> >> Does it matter if myid is an index or not for the cache to utilised?
>> >
>> > No.
>> >
>> >> If the query did not have a where clause, would the entire table end up
> in
>> >> memory?
>> >
>> > If there is sufficient buffer cache, yes. If not, cache is reused
> according to (roughly) a LRU
>> > algorithm so that the most recent and frequently accessed data stay in
> cache.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "Dodo Lurker" <none@.noemailplease> wrote in message
> news:UJednWzblJWP4z7eRVn-qA@.comcast.com...
>> >> I'm new to databases and I'm trying to understand how the memory cache
>> >> works with sql server (I think Oracle calls it the "Block Buffer")
>> >>
>> >> I fire up sql server
>> >> I query a table (ie select * from mytable where myid = 10)
>> >>
>> >> This query's plan ends up in cache?
>> >> Do the returned rows end up there too?
>> >> Does it matter if myid is an index or not for the cache to utilised?
>> >> If the query did not have a where clause, would the entire table end up
> in
>> >> memory?
>> >>
>> >> Sorry for the dumb questions... just trying learn
>> >>
>> >> TIA
>> >>
>> >>
>> >
>> >
>

Question on SQL Server and the cache

Tuesday, March 20, 2012

Question on restoring encrypted databases on different servers

I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

"An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

However, it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

-- Will succeed

create certificate foo with subject = 'foo'

CLOSE MASTER KEY

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

I try this on my original database and I get the same result - what have i done?! I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

-- Will succeed

create certificate foo with subject = 'foo'

CLOSE MASTER KEY

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

-- Will succeed

create certificate foo with subject = 'foo'

CLOSE MASTER KEY

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Question on restoring encrypted databases on different servers

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

I then try the following commands, which I used when I brought the development database over to the test box the first time:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

This caused the following error:

1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the commands that you need to execute when moving a database to another server are:

OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

The REGENERATE option should be used only if you want to have a new master key in the database.

Thanks
Laurentiu

|||

This solves my problem. Thank you for your help.

So, how do you make the original database require the Open Master Key Statement?

|||

You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

Thanks
Laurentiu

|||

You can use the following statement:

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

Just to verify it:

-- Should fail with error 15581:

-- Please create a master key in the database or open the master key in the session before performing this operation.

create certificate foo with subject = 'foo'

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@.ssw0rD>>'

-- Will succeed

create certificate foo with subject = 'foo'

CLOSE MASTER KEY

-Raul Garcia

SDE/T

SQL Server Engine

|||

Hi,

I am also trying to restore a database which has an encrypted column. I used methods from Larentiu's blog post - column encryption demo.

I execute this command and get:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

Msg 15313, Level 16, State 1, Line 1

The key is not encrypted using the specified decryptor.

|||

Hi Sam,

I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

thanks...

|||

I just answered a similar problem in the following thread of this forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Monday, March 12, 2012

Question on moving logs

Running SQL Server 2000. I have been asked to move the transaction logs
to another drive. I was able to do that for the user databases using
detach/attach.

However I don't see that as an option for moving the logs for the
system databases.
Is there any way I can move the system database logs? I'm guessing it
might be possible through a restore.

Any thoughts ?

Thanks in advance.

GerryShutdown the service, and change thew appropiate registry settings for
the temp and the master database. The other databases can be moved with
attach / detach.

HTH, Jens K. Suessmeyer.

--
http://www.sqlserver2005.de
--|||Jens wrote:

Quote:

Originally Posted by

Shutdown the service, and change thew appropiate registry settings for
the temp and the master database. The other databases can be moved with
attach / detach.
>
HTH, Jens K. Suessmeyer.
>
--
http://www.sqlserver2005.de
--

Thanks Jens

Also came across this

http://support.microsoft.com/kb/224071/

Question on moving logs

Running SQL Server 2000. I have been asked to move the transaction logs to another drive. I was able to do that for the user databases using detach/attach.

However I don't see that as an option for moving the logs for the system databases.
Is there any way I can move the system database logs? I'm guessing it might be possible through a restore.

Any thoughts ?

Thanks in advance.

GerryGiven the number of transactions that happen in master, model and msdb on a daily basis, I have never found it necessary to move the logs of these three databases. Tempdb on the other hand can be moved with a dirty trick. Leave the existing log with a maxsize limit of say 5MB, and create a second logfile on the new device at the actual size you need. Logfiles are used in sequence, as opposed to datafiles which are used in a more distributed way. You will still have the original disk used for 5MB of logs every once in a while, but the vast majority of the usage should be on the other drive.|||Thanks

Also came across this article

http://support.microsoft.com/kb/224071/

Saturday, February 25, 2012

Question on copying over overlapping data from one database to another...

... and preserving the relationships.
(Note, this is more of a SQL question than a SQL-related ASP.NET question...)
Say I have two databases, D1 and D2, with the same three tables:
Companies
Departments
Employees
With the standard one-to-many relationships down the line, with each
table having a PK, IDENTITY field like CompanyID, DepartmentID, and
EmployeeID.

I have a smattering of data in each of D1 and D2 for these tables with
overlaps in the ID field values. What I want to be able to do is copy
over D1's data to D2, preserving the relationships in D1 even though
there are ID overlaps in D2. So the tool I'd use would have to be
smart enough to check for ID dups in D2 and appropriate change the ID
values in D1's tables, maintaining the relationships.
Is there some built-in SQL tool to do this or do I have to do this myself?
Thanks!

There may be 3rd Party tools that do this but there's not any way, I'm aware of, of doing it in Enterprise Manager or Management Server Express. I do have an idea, though, of a way you could at least merge the tables using standard SQL:

For instance, if we take the Northwind database and imagine you have two Products tables in it, both with the exact same columns and both populated with data where the ProductID (PK) might be the same in both tables. We shall call these two tablesProducts andProducts2. Now, if you wanted to merge these two tables into one new table with a new, unqiue primary key you would do this:

Create a new table called (say) Products_Temp with the same structure as Products and make sure that ProductID is set as an Identity column. The easiest way is to create a script from Products table which would look like:

CREATETABLE [dbo].[Products](

[ProductID] [int]IDENTITY(1,1)NOTNULL,

[ProductName] [nvarchar](40)COLLATE Latin1_General_CI_ASNOTNULL,

[SupplierID] [int]NULL,

[CategoryID] [int]NULL,

[QuantityPerUnit] [nvarchar](20)COLLATE Latin1_General_CI_ASNULL,

[UnitPrice] [money]NULLCONSTRAINT [DF_Products_UnitPrice]DEFAULT((0)),

[UnitsInStock] [smallint]NULLCONSTRAINT [DF_Products_UnitsInStock]DEFAULT((0)),

[UnitsOnOrder] [smallint]NULLCONSTRAINT [DF_Products_UnitsOnOrder]DEFAULT((0)),

[ReorderLevel] [smallint]NULLCONSTRAINT [DF_Products_ReorderLevel]DEFAULT((0)),

[Discontinued] [bit]NOTNULLCONSTRAINT [DF_Products_Discontinued]DEFAULT((0)),

CONSTRAINT [PK_Products]PRIMARYKEYCLUSTERED

(

[ProductID]ASC

)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]

)ON [PRIMARY]

Then to merge the two tables you would UNION them together on all columnsexceptthe primary key column (ProductID) and place the results into the Products_Temp table you just created. Eg.

SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

INTO Product_TEMP

FROM

(SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

FROM Products

UNION

SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

FROM Products2)

AS Temp;

Now Products_Temp will contain the merged data from both tables (with any exact duplicate rows removed) and each row will now have a unique primary key (because of Identity insert). I know this isn't that much help because it doesn't preserve the PK > FK relationships between multiple tables, but it might give you some ideas or inspiration... Good luck :)

|||Scott,
If you have sql enterprise you can use there management studio and do this with a breeze. even if the database is local or remote. You can execute scripts on the databases, copy, backup, merge. basically everyhing. I hope this helps.
|||Connect, I was not aware of any built-in functionality in SQL Server 2000, and figured it would have to be scripted.
Does anyone know of a third-party tool that accomplishes this?

Blake05 wrote:
Scott,
If you have sql enterprise you can use there management studio and do this with a breeze. even if the database is local or remote. You can execute scripts on the databases, copy, backup, merge. basically everyhing. I hope this helps.

Blake05, how can I use EM to copy data from one DB to another when there are overlapping ID values between the two databases' tables?
Thanks
|||Yes you can
|||You don't need third party tool create Unique index with IGNORE_DUP_KEY option and do standard INSERT INTO and in SQL Server 2000 and below that statement will not affect update statements. What happens is SQL Server will not insert the duplicate values it will insert only the unique values. I am assuming this is what you want and run a search for the above in SQL Server BOL (books online). Hope this helps.

Question on Changing Jobs in SQL Server 2000

We have a bunch of databases on this SQL Server 2000 and for each
database we pretty much have the same kind of job running daily and
weekly. We are running out of disk space where we used to normally
backup the databases so we now have a new drive which will be used only
for backups so I need to change all my jobs to backup the databases to
the new drive. I can do that manually by going into each job to change
the drive path, but I was wondering if there was a way to update the
system table so that I did not have to manually change all my jobs.
Any help, ideas in this regard will be greatly appreciated.
ThanksHi
This is where having devices would make the task easier! But you can script
the jobs from Enterprise manager and then globally edit the file created.
John
"shub" wrote:

> We have a bunch of databases on this SQL Server 2000 and for each
> database we pretty much have the same kind of job running daily and
> weekly. We are running out of disk space where we used to normally
> backup the databases so we now have a new drive which will be used only
> for backups so I need to change all my jobs to backup the databases to
> the new drive. I can do that manually by going into each job to change
> the drive path, but I was wondering if there was a way to update the
> system table so that I did not have to manually change all my jobs.
> Any help, ideas in this regard will be greatly appreciated.
> Thanks
>|||John
Thanks for your response. Wouldnt the script drop the existing job and
create a brand new one which would cause me to lose all my job history?
Is there a way to get around that?
Thanks|||Hi
Yes it would, the jobs would have different Ids when re-created so you would
have to export everything before doing the scripting. Another reason for
using devices.
John
"shub" wrote:

> John
> Thanks for your response. Wouldnt the script drop the existing job and
> create a brand new one which would cause me to lose all my job history?
> Is there a way to get around that?
> Thanks
>|||John,
If we had devices in place would't we still need to change tha path off
all the devices that we set up for all the different databases? Also
can you set up devices if the physical location of the back up files
are in different computers in the network, basically does it support
UNC? I will look it up myself, but I would sure appreciate your input
as well.
Thanks again|||Hi
If you need to change the job to make it use a device it will not change the
problems of loosing the history, only make it more flexible in the future.
You should only be keeping a certain amount of history as this information
will bloat your msdb, if you need to retain this for a prolonged period then
you may want to put into place a system that will copy it elsewhere.
Devices can use UNC paths, there is an example of sp_adddumpdevice in books
online that shows exactly this. If you are backing up to another machine it
is worth considering a dedicated (1GB) subnet and adding extra cards to
improve performance.
John
"shub" wrote:

> John,
> If we had devices in place would't we still need to change tha path off
> all the devices that we set up for all the different databases? Also
> can you set up devices if the physical location of the back up files
> are in different computers in the network, basically does it support
> UNC? I will look it up myself, but I would sure appreciate your input
> as well.
> Thanks again
>|||I believe when all our jobs were created originally they were all
created by The maintenance plan wizard and I don't see you have an
option of backing up databases to devices.
Maybe in the near future we need to look at redoing all our jobs to use
the devices.
Thank you for all your help.

Question on Changing Jobs in SQL Server 2000

We have a bunch of databases on this SQL Server 2000 and for each
database we pretty much have the same kind of job running daily and
weekly. We are running out of disk space where we used to normally
backup the databases so we now have a new drive which will be used only
for backups so I need to change all my jobs to backup the databases to
the new drive. I can do that manually by going into each job to change
the drive path, but I was wondering if there was a way to update the
system table so that I did not have to manually change all my jobs.
Any help, ideas in this regard will be greatly appreciated.
ThanksHi
This is where having devices would make the task easier! But you can script
the jobs from Enterprise manager and then globally edit the file created.
John
"shub" wrote:
> We have a bunch of databases on this SQL Server 2000 and for each
> database we pretty much have the same kind of job running daily and
> weekly. We are running out of disk space where we used to normally
> backup the databases so we now have a new drive which will be used only
> for backups so I need to change all my jobs to backup the databases to
> the new drive. I can do that manually by going into each job to change
> the drive path, but I was wondering if there was a way to update the
> system table so that I did not have to manually change all my jobs.
> Any help, ideas in this regard will be greatly appreciated.
> Thanks
>|||John
Thanks for your response. Wouldnt the script drop the existing job and
create a brand new one which would cause me to lose all my job history?
Is there a way to get around that?
Thanks|||Hi
Yes it would, the jobs would have different Ids when re-created so you would
have to export everything before doing the scripting. Another reason for
using devices.
John
"shub" wrote:
> John
> Thanks for your response. Wouldnt the script drop the existing job and
> create a brand new one which would cause me to lose all my job history?
> Is there a way to get around that?
> Thanks
>|||John,
If we had devices in place would't we still need to change tha path off
all the devices that we set up for all the different databases? Also
can you set up devices if the physical location of the back up files
are in different computers in the network, basically does it support
UNC? I will look it up myself, but I would sure appreciate your input
as well.
Thanks again|||Hi
If you need to change the job to make it use a device it will not change the
problems of loosing the history, only make it more flexible in the future.
You should only be keeping a certain amount of history as this information
will bloat your msdb, if you need to retain this for a prolonged period then
you may want to put into place a system that will copy it elsewhere.
Devices can use UNC paths, there is an example of sp_adddumpdevice in books
online that shows exactly this. If you are backing up to another machine it
is worth considering a dedicated (1GB) subnet and adding extra cards to
improve performance.
John
"shub" wrote:
> John,
> If we had devices in place would't we still need to change tha path off
> all the devices that we set up for all the different databases? Also
> can you set up devices if the physical location of the back up files
> are in different computers in the network, basically does it support
> UNC? I will look it up myself, but I would sure appreciate your input
> as well.
> Thanks again
>|||I believe when all our jobs were created originally they were all
created by The maintenance plan wizard and I don't see you have an
option of backing up databases to devices.
Maybe in the near future we need to look at redoing all our jobs to use
the devices.
Thank you for all your help.

Friday, March 30, 2012

question sql server

question sql server

question sql server

Monday, March 26, 2012

Question regarding .LDF and .MDF files and backup

Question regarding .LDF and .MDF files and backup

Question on using SQLBulkCopy

Friday, March 23, 2012

Question on SSIS and .NET 1.1

Wednesday, March 21, 2012

Question on SQL Server and the cache

Question on SQL Server and the cache

Question on SQL Server and the cache

Tuesday, March 20, 2012

Question on restoring encrypted databases on different servers

Question on restoring encrypted databases on different servers

Question on restoring encrypted databases on different servers

Question on restoring encrypted databases on different servers

Monday, March 12, 2012

Question on moving logs

Question on moving logs

Saturday, February 25, 2012

Question on copying over overlapping data from one database to another...

Question on Changing Jobs in SQL Server 2000

Question on Changing Jobs in SQL Server 2000

SQLAgentMail

Blog Archive

About Me