Last month we upgraded our SQL Server 2000 production database to SQL Server
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Server
> 2005. It also has SP1 applied to it. I was just checking the jobs, making
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>> Last month we upgraded our SQL Server 2000 production database to SQL
>> Server
>> 2005. It also has SP1 applied to it. I was just checking the jobs,
>> making
>> certain that everything was running OK. We have 7 jobs defined, when I
>> clicked on the View Job History link on each of the job's property page,
>> I
>> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really
>> nothing
>> else mentioned. Here is the text of the error message:
>> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>>
>> Going to the link didn't help me.
>> So, how do I find out what's wrong and fix it, please?
>> Rod
>>
>>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
> > Hi Rod
> >
> > Does this happen on every job? If it does then how did you upgrade the
> > jobs?
> >
> > You may want to manually see what is in the sysjobhistory table for that
> > job.
> >
> > You may want to use SQL Profiler to see what SQL is being called and check
> > that there is no errors.
> >
> > Try running DBCC CHECKDB on MSDB.
> >
> > If that does not work try scripting the job and creating it as a different
> > one, then disable the original.
> >
> > John
> >
> > "Rod" wrote:
> >
> >> Last month we upgraded our SQL Server 2000 production database to SQL
> >> Server
> >> 2005. It also has SP1 applied to it. I was just checking the jobs,
> >> making
> >> certain that everything was running OK. We have 7 jobs defined, when I
> >> clicked on the View Job History link on each of the job's property page,
> >> I
> >> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really
> >> nothing
> >> else mentioned. Here is the text of the error message:
> >>
> >> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> >>
> >> For help, click:
> >> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
> >>
> >>
> >> Going to the link didn't help me.
> >>
> >> So, how do I find out what's wrong and fix it, please?
> >>
> >> Rod
> >>
> >>
> >>
> >>
> >>
>
>
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Thursday, March 29, 2012
Getting a syntax error involving Microsoft.SqlServer.SmoEnum
Last month we upgraded our SQL Server 2000 production database to SQL Server
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job
.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Serv
er
> 2005. It also has SP1 applied to it. I was just checking the jobs, makin
g
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothi
ng
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...[vbcol=seagreen]
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
>
>
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job
.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Serv
er
> 2005. It also has SP1 applied to it. I was just checking the jobs, makin
g
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothi
ng
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...[vbcol=seagreen]
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
>
>
Tuesday, March 27, 2012
Getting a database on one server to match another
Hello,
We have two servers and want out production db on server 1
to match the production server on server 2.
Whats the best way of doing this?One time only or regularly?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4c2201c42c35$3362d360$a0
01280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Hi,
Install the SQL Server in Server 2 with the identical directory structure
and service packs. After that
1. Stop SQL Server service in both servers
2. Copy the MDF and LDF from server1 to server 2 including system databases
3. Start the SQL server in server 2 and check all databases are ok. If ok,
change the server name in server 2
using sp_dropserver and sp_addserver <servernam>,Local (Refer books
online for syntax usage)
4. Start the server 1.
If you need to keep the server2 always sync with server 1 then go for one of
the below strategies:-
1. Logshipping
2. Transactional Replication
Thanks
Hari
MCDBA
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Sarah,
Several options in my order of preference:
1) Backup the DB on server1 and restore to server2
2) Detach on server1 and attach on server2
3) DTS
Microsoft Knowledge Base Article - 224071
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/defaul...kb;EN-US;224071
Microsoft Knowledge Base Article - 304692
INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE
http://support.microsoft.com/defaul...kb;EN-US;304692
Microsoft Knowledge Base Article - 314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>
We have two servers and want out production db on server 1
to match the production server on server 2.
Whats the best way of doing this?One time only or regularly?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4c2201c42c35$3362d360$a0
01280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Hi,
Install the SQL Server in Server 2 with the identical directory structure
and service packs. After that
1. Stop SQL Server service in both servers
2. Copy the MDF and LDF from server1 to server 2 including system databases
3. Start the SQL server in server 2 and check all databases are ok. If ok,
change the server name in server 2
using sp_dropserver and sp_addserver <servernam>,Local (Refer books
online for syntax usage)
4. Start the server 1.
If you need to keep the server2 always sync with server 1 then go for one of
the below strategies:-
1. Logshipping
2. Transactional Replication
Thanks
Hari
MCDBA
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Sarah,
Several options in my order of preference:
1) Backup the DB on server1 and restore to server2
2) Detach on server1 and attach on server2
3) DTS
Microsoft Knowledge Base Article - 224071
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/defaul...kb;EN-US;224071
Microsoft Knowledge Base Article - 304692
INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE
http://support.microsoft.com/defaul...kb;EN-US;304692
Microsoft Knowledge Base Article - 314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>
Getting a database on one server to match another
Hello,
We have two servers and want out production db on server 1
to match the production server on server 2.
Whats the best way of doing this?
One time only or regularly?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>
|||Hi,
Install the SQL Server in Server 2 with the identical directory structure
and service packs. After that
1. Stop SQL Server service in both servers
2. Copy the MDF and LDF from server1 to server 2 including system databases
3. Start the SQL server in server 2 and check all databases are ok. If ok,
change the server name in server 2
using sp_dropserver and sp_addserver <servernam>,Local (Refer books
online for syntax usage)
4. Start the server 1.
If you need to keep the server2 always sync with server 1 then go for one of
the below strategies:-
1. Logshipping
2. Transactional Replication
Thanks
Hari
MCDBA
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>
|||Sarah,
Several options in my order of preference:
1) Backup the DB on server1 and restore to server2
2) Detach on server1 and attach on server2
3) DTS
Microsoft Knowledge Base Article - 224071
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default...b;EN-US;224071
Microsoft Knowledge Base Article - 304692
INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE
http://support.microsoft.com/default...b;EN-US;304692
Microsoft Knowledge Base Article - 314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>
We have two servers and want out production db on server 1
to match the production server on server 2.
Whats the best way of doing this?
One time only or regularly?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>
|||Hi,
Install the SQL Server in Server 2 with the identical directory structure
and service packs. After that
1. Stop SQL Server service in both servers
2. Copy the MDF and LDF from server1 to server 2 including system databases
3. Start the SQL server in server 2 and check all databases are ok. If ok,
change the server name in server 2
using sp_dropserver and sp_addserver <servernam>,Local (Refer books
online for syntax usage)
4. Start the server 1.
If you need to keep the server2 always sync with server 1 then go for one of
the below strategies:-
1. Logshipping
2. Transactional Replication
Thanks
Hari
MCDBA
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>
|||Sarah,
Several options in my order of preference:
1) Backup the DB on server1 and restore to server2
2) Detach on server1 and attach on server2
3) DTS
Microsoft Knowledge Base Article - 224071
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default...b;EN-US;224071
Microsoft Knowledge Base Article - 304692
INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE
http://support.microsoft.com/default...b;EN-US;304692
Microsoft Knowledge Base Article - 314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>
Getting a database on one server to match another
Hello,
We have two servers and want out production db on server 1
to match the production server on server 2.
Whats the best way of doing this?One time only or regularly?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Hi,
Install the SQL Server in Server 2 with the identical directory structure
and service packs. After that
1. Stop SQL Server service in both servers
2. Copy the MDF and LDF from server1 to server 2 including system databases
3. Start the SQL server in server 2 and check all databases are ok. If ok,
change the server name in server 2
using sp_dropserver and sp_addserver <servernam>,Local (Refer books
online for syntax usage)
4. Start the server 1.
If you need to keep the server2 always sync with server 1 then go for one of
the below strategies:-
1. Logshipping
2. Transactional Replication
Thanks
Hari
MCDBA
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Sarah,
Several options in my order of preference:
1) Backup the DB on server1 and restore to server2
2) Detach on server1 and attach on server2
3) DTS
Microsoft Knowledge Base Article - 224071
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
Microsoft Knowledge Base Article - 304692
INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;304692
Microsoft Knowledge Base Article - 314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Sorry wasn't too clear was I.
The second server is our disaster recovery server, so we
want our second server database to be as up to date as
possible with the production database.
ta
>--Original Message--
>Hello,
>We have two servers and want out production db on server
1
>to match the production server on server 2.
>Whats the best way of doing this?
>.
>|||Sarah,
Hari already answered this with:
1) Log Shipping
2) Transactional replication
If the disaster recovery server does not need to be operational, then log
shipping would be good.You may also want to consider Double-Take from NSI
Software. This replicates the SQL Server data files at the block level on
disk and ensures transactional consistency.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> Sorry wasn't too clear was I.
> The second server is our disaster recovery server, so we
> want our second server database to be as up to date as
> possible with the production database.
> ta
>
> >--Original Message--
> >Hello,
> >
> >We have two servers and want out production db on server
> 1
> >to match the production server on server 2.
> >
> >Whats the best way of doing this?
> >
> >.
> >|||Thanks Mark,
I'll have a look at transactional replication as we backup
the Production db by log files and to be honest I'm not
too sure on what will happen if we change it so it updates
asap.
Thanks for your time
>--Original Message--
>Sarah,
>Hari already answered this with:
>1) Log Shipping
>2) Transactional replication
>If the disaster recovery server does not need to be
operational, then log
>shipping would be good.You may also want to consider
Double-Take from NSI
>Software. This replicates the SQL Server data files at
the block level on
>disk and ensures transactional consistency.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
>> Sorry wasn't too clear was I.
>> The second server is our disaster recovery server, so we
>> want our second server database to be as up to date as
>> possible with the production database.
>> ta
>>
>> >--Original Message--
>> >Hello,
>> >
>> >We have two servers and want out production db on
server
>> 1
>> >to match the production server on server 2.
>> >
>> >Whats the best way of doing this?
>> >
>> >.
>> >
>
>.
>|||There's a web site at MS called something like "Pattern and practices". There you find a two boos on SQL
Server and high availability. You really need to go through that if you intend to use replication for fail
over. There are some issues you need to plan for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4ae801c42c41$337182f0$a401280a@.phx.gbl...
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> >--Original Message--
> >Sarah,
> >
> >Hari already answered this with:
> >
> >1) Log Shipping
> >2) Transactional replication
> >
> >If the disaster recovery server does not need to be
> operational, then log
> >shipping would be good.You may also want to consider
> Double-Take from NSI
> >Software. This replicates the SQL Server data files at
> the block level on
> >disk and ensures transactional consistency.
> >
> >--
> >Mark Allison, SQL Server MVP
> >http://www.markallison.co.uk
> >
> >
> >
> >
> >"Sarah" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> >> Sorry wasn't too clear was I.
> >>
> >> The second server is our disaster recovery server, so we
> >> want our second server database to be as up to date as
> >> possible with the production database.
> >>
> >> ta
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> >We have two servers and want out production db on
> server
> >> 1
> >> >to match the production server on server 2.
> >> >
> >> >Whats the best way of doing this?
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Sarah,
I really don't think transactional replication is a good high availability
solution. There are many drawbacks to using this. Do a search on the web for
high availability sql server and you should find some good information.
Kimberley Tripp did a great presentation on high availability, perhaps
search for that.
Log shipping might be a good way to go, you can have a latency of 5 minutes
with this solution.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4ae801c42c41$337182f0$a401280a@.phx.gbl...
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> >--Original Message--
> >Sarah,
> >
> >Hari already answered this with:
> >
> >1) Log Shipping
> >2) Transactional replication
> >
> >If the disaster recovery server does not need to be
> operational, then log
> >shipping would be good.You may also want to consider
> Double-Take from NSI
> >Software. This replicates the SQL Server data files at
> the block level on
> >disk and ensures transactional consistency.
> >
> >--
> >Mark Allison, SQL Server MVP
> >http://www.markallison.co.uk
> >
> >
> >
> >
> >"Sarah" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> >> Sorry wasn't too clear was I.
> >>
> >> The second server is our disaster recovery server, so we
> >> want our second server database to be as up to date as
> >> possible with the production database.
> >>
> >> ta
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> >We have two servers and want out production db on
> server
> >> 1
> >> >to match the production server on server 2.
> >> >
> >> >Whats the best way of doing this?
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Thanks for your help guys
>--Original Message--
>Thanks Mark,
>I'll have a look at transactional replication as we
backup
>the Production db by log files and to be honest I'm not
>too sure on what will happen if we change it so it
updates
>asap.
>Thanks for your time
>
>
>>--Original Message--
>>Sarah,
>>Hari already answered this with:
>>1) Log Shipping
>>2) Transactional replication
>>If the disaster recovery server does not need to be
>operational, then log
>>shipping would be good.You may also want to consider
>Double-Take from NSI
>>Software. This replicates the SQL Server data files at
>the block level on
>>disk and ensures transactional consistency.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>
>>
>>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
>> Sorry wasn't too clear was I.
>> The second server is our disaster recovery server, so
we
>> want our second server database to be as up to date as
>> possible with the production database.
>> ta
>>
>> >--Original Message--
>> >Hello,
>> >
>> >We have two servers and want out production db on
>server
>> 1
>> >to match the production server on server 2.
>> >
>> >Whats the best way of doing this?
>> >
>> >.
>> >
>>
>>.
>.
>
We have two servers and want out production db on server 1
to match the production server on server 2.
Whats the best way of doing this?One time only or regularly?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Hi,
Install the SQL Server in Server 2 with the identical directory structure
and service packs. After that
1. Stop SQL Server service in both servers
2. Copy the MDF and LDF from server1 to server 2 including system databases
3. Start the SQL server in server 2 and check all databases are ok. If ok,
change the server name in server 2
using sp_dropserver and sp_addserver <servernam>,Local (Refer books
online for syntax usage)
4. Start the server 1.
If you need to keep the server2 always sync with server 1 then go for one of
the below strategies:-
1. Logshipping
2. Transactional Replication
Thanks
Hari
MCDBA
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Sarah,
Several options in my order of preference:
1) Backup the DB on server1 and restore to server2
2) Detach on server1 and attach on server2
3) DTS
Microsoft Knowledge Base Article - 224071
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
Microsoft Knowledge Base Article - 304692
INF: Moving SQL Server 7.0 Databases to a New Server with BACKUP and RESTORE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;304692
Microsoft Knowledge Base Article - 314546
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c2201c42c35$3362d360$a001280a@.phx.gbl...
> Hello,
> We have two servers and want out production db on server 1
> to match the production server on server 2.
> Whats the best way of doing this?
>|||Sorry wasn't too clear was I.
The second server is our disaster recovery server, so we
want our second server database to be as up to date as
possible with the production database.
ta
>--Original Message--
>Hello,
>We have two servers and want out production db on server
1
>to match the production server on server 2.
>Whats the best way of doing this?
>.
>|||Sarah,
Hari already answered this with:
1) Log Shipping
2) Transactional replication
If the disaster recovery server does not need to be operational, then log
shipping would be good.You may also want to consider Double-Take from NSI
Software. This replicates the SQL Server data files at the block level on
disk and ensures transactional consistency.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> Sorry wasn't too clear was I.
> The second server is our disaster recovery server, so we
> want our second server database to be as up to date as
> possible with the production database.
> ta
>
> >--Original Message--
> >Hello,
> >
> >We have two servers and want out production db on server
> 1
> >to match the production server on server 2.
> >
> >Whats the best way of doing this?
> >
> >.
> >|||Thanks Mark,
I'll have a look at transactional replication as we backup
the Production db by log files and to be honest I'm not
too sure on what will happen if we change it so it updates
asap.
Thanks for your time
>--Original Message--
>Sarah,
>Hari already answered this with:
>1) Log Shipping
>2) Transactional replication
>If the disaster recovery server does not need to be
operational, then log
>shipping would be good.You may also want to consider
Double-Take from NSI
>Software. This replicates the SQL Server data files at
the block level on
>disk and ensures transactional consistency.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>
>
>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
>> Sorry wasn't too clear was I.
>> The second server is our disaster recovery server, so we
>> want our second server database to be as up to date as
>> possible with the production database.
>> ta
>>
>> >--Original Message--
>> >Hello,
>> >
>> >We have two servers and want out production db on
server
>> 1
>> >to match the production server on server 2.
>> >
>> >Whats the best way of doing this?
>> >
>> >.
>> >
>
>.
>|||There's a web site at MS called something like "Pattern and practices". There you find a two boos on SQL
Server and high availability. You really need to go through that if you intend to use replication for fail
over. There are some issues you need to plan for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message news:4ae801c42c41$337182f0$a401280a@.phx.gbl...
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> >--Original Message--
> >Sarah,
> >
> >Hari already answered this with:
> >
> >1) Log Shipping
> >2) Transactional replication
> >
> >If the disaster recovery server does not need to be
> operational, then log
> >shipping would be good.You may also want to consider
> Double-Take from NSI
> >Software. This replicates the SQL Server data files at
> the block level on
> >disk and ensures transactional consistency.
> >
> >--
> >Mark Allison, SQL Server MVP
> >http://www.markallison.co.uk
> >
> >
> >
> >
> >"Sarah" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> >> Sorry wasn't too clear was I.
> >>
> >> The second server is our disaster recovery server, so we
> >> want our second server database to be as up to date as
> >> possible with the production database.
> >>
> >> ta
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> >We have two servers and want out production db on
> server
> >> 1
> >> >to match the production server on server 2.
> >> >
> >> >Whats the best way of doing this?
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Sarah,
I really don't think transactional replication is a good high availability
solution. There are many drawbacks to using this. Do a search on the web for
high availability sql server and you should find some good information.
Kimberley Tripp did a great presentation on high availability, perhaps
search for that.
Log shipping might be a good way to go, you can have a latency of 5 minutes
with this solution.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Sarah" <anonymous@.discussions.microsoft.com> wrote in message
news:4ae801c42c41$337182f0$a401280a@.phx.gbl...
> Thanks Mark,
> I'll have a look at transactional replication as we backup
> the Production db by log files and to be honest I'm not
> too sure on what will happen if we change it so it updates
> asap.
> Thanks for your time
>
>
> >--Original Message--
> >Sarah,
> >
> >Hari already answered this with:
> >
> >1) Log Shipping
> >2) Transactional replication
> >
> >If the disaster recovery server does not need to be
> operational, then log
> >shipping would be good.You may also want to consider
> Double-Take from NSI
> >Software. This replicates the SQL Server data files at
> the block level on
> >disk and ensures transactional consistency.
> >
> >--
> >Mark Allison, SQL Server MVP
> >http://www.markallison.co.uk
> >
> >
> >
> >
> >"Sarah" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
> >> Sorry wasn't too clear was I.
> >>
> >> The second server is our disaster recovery server, so we
> >> want our second server database to be as up to date as
> >> possible with the production database.
> >>
> >> ta
> >>
> >>
> >> >--Original Message--
> >> >Hello,
> >> >
> >> >We have two servers and want out production db on
> server
> >> 1
> >> >to match the production server on server 2.
> >> >
> >> >Whats the best way of doing this?
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Thanks for your help guys
>--Original Message--
>Thanks Mark,
>I'll have a look at transactional replication as we
backup
>the Production db by log files and to be honest I'm not
>too sure on what will happen if we change it so it
updates
>asap.
>Thanks for your time
>
>
>>--Original Message--
>>Sarah,
>>Hari already answered this with:
>>1) Log Shipping
>>2) Transactional replication
>>If the disaster recovery server does not need to be
>operational, then log
>>shipping would be good.You may also want to consider
>Double-Take from NSI
>>Software. This replicates the SQL Server data files at
>the block level on
>>disk and ensures transactional consistency.
>>--
>>Mark Allison, SQL Server MVP
>>http://www.markallison.co.uk
>>
>>
>>"Sarah" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:4c5401c42c3a$23ff0f60$a001280a@.phx.gbl...
>> Sorry wasn't too clear was I.
>> The second server is our disaster recovery server, so
we
>> want our second server database to be as up to date as
>> possible with the production database.
>> ta
>>
>> >--Original Message--
>> >Hello,
>> >
>> >We have two servers and want out production db on
>server
>> 1
>> >to match the production server on server 2.
>> >
>> >Whats the best way of doing this?
>> >
>> >.
>> >
>>
>>.
>.
>
Subscribe to:
Posts (Atom)