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?
>

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?
>

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?
>> >
>> >.
>> >
>>
>>.
>.
>

Get servers current date format?

sql server 2k...
I found the Set DateFormat method but I can't seem to find a get method
where it will return to me what sqlserver is set as...(mdy,dmy etc..) does
such a method/property exist?
thanks
Doug
Doug Swanson
Senior Applications Developer
Synchrono, Inc
651.228.1772
dswanson@.synchrono.comYou can use DBCC USEROPTIONS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Doug Swanson" <dcswanson@._remove_this_surfbest.net> wrote in message
news:ujHMya0FEHA.1272@.TK2MSFTNGP12.phx.gbl...
> sql server 2k...
> I found the Set DateFormat method but I can't seem to find a get method
> where it will return to me what sqlserver is set as...(mdy,dmy etc..) doe
s
> such a method/property exist?
> thanks
> Doug
> --
> Doug Swanson
> Senior Applications Developer
> Synchrono, Inc
> 651.228.1772
> dswanson@.synchrono.com
>

get rid of time from SQL servers data by using ASPX

Hi,

I have the problem in accessing data from MS-SQL server by using Dreamwearver MX 's VB.NET page. Although it works well, in the date format, it also display time. Please see the following code.

SELECT student_ID, Material_ID, Borrow_Date, Due_Date, Renew_Date, Status, include_with
FROM dbo.Record
WHERE student_ID = @.student_ID

To remove time, I added the following code as follows. After that, I can preview and works well without having time.

SELECT student_ID, Material_ID, convert(varchar(10),Borrow_Date,103), convert(varchar(10),Due_Date,103), convert(varchar(10),Renew_Date,103), Status, include_with
FROM dbo.Record
WHERE student_ID = @.student_ID

But, when I browse, I get the runtime error. When I look more details in server, the error said " Parser Error Message: The server tag is not well formed." and error in <MM:DataSet"

Please help me.

Jon

Try to use alias for your columns after convertion:

convert(varchar(10),Borrow_Date,103) AS Borrow_Date, convert(varchar(10),Due_Date,103) AS Due_Date,

|||Many thanks indeed.It is working now.Jon|||

don't do the formatting in T-SQL / Database. Do it in the ASP. How are you going to sort it when the date is now :

21/03/2007

01/04/2007

Get Remote SQL Server Date Time

Hi All,
I have 3 sql servers located at different time zones. Say, CST,PST,EST.
Now how can I get current time at EST,PST from the SQL server located
at CST? Is there any query to do that?
I have a stored proc located in SQL server at CST zone where I need to
query for the current date/time of the other zone sql servers.
I tried below query at CST SQL server
SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
But it always gives CST datetime.
Please reply...
Thanks
RP
Please don't multi-post, this question was answered in another group.
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:7368A848-06BD-4CD0-935D-B98A656B9A5D@.microsoft.com...
> Hi All,
> I have 3 sql servers located at different time zones. Say, CST,PST,EST.
> Now how can I get current time at EST,PST from the SQL server located
> at CST? Is there any query to do that?
> I have a stored proc located in SQL server at CST zone where I need to
> query for the current date/time of the other zone sql servers.
> I tried below query at CST SQL server
> SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
> But it always gives CST datetime.
> Please reply...
> Thanks
> RP
>

Get Remote SQL Server Date Time

Hi All,
I have 3 sql servers located at different time zones. Say, CST,PST,EST.
Now how can I get current time at EST,PST from the SQL server located
at CST? Is there any query to do that?
I have a stored proc located in SQL server at CST zone where I need to
query for the current date/time of the other zone sql servers.
I tried below query at CST SQL server
SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
But it always gives CST datetime.
Please reply...
Thanks
RPPlease don't multi-post, this question was answered in another group.
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:7368A848-06BD-4CD0-935D-B98A656B9A5D@.microsoft.com...
> Hi All,
> I have 3 sql servers located at different time zones. Say, CST,PST,EST.
> Now how can I get current time at EST,PST from the SQL server located
> at CST? Is there any query to do that?
> I have a stored proc located in SQL server at CST zone where I need to
> query for the current date/time of the other zone sql servers.
> I tried below query at CST SQL server
> SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
> But it always gives CST datetime.
> Please reply...
> Thanks
> RP
>

Get Remote SQL Server Date Time

Hi All,
I have 3 sql servers located at different time zones. Say, CST,PST,EST.
Now how can I get current time at EST,PST from the SQL server located
at CST? Is there any query to do that?
I have a stored proc located in SQL server at CST zone where I need to
query for the current date/time of the other zone sql servers.
I tried below query at CST SQL server
SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
But it always gives CST datetime.
Please reply...
Thanks
RPPlease don't multi-post, this question was answered in another group.
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:7368A848-06BD-4CD0-935D-B98A656B9A5D@.microsoft.com...
> Hi All,
> I have 3 sql servers located at different time zones. Say, CST,PST,EST.
> Now how can I get current time at EST,PST from the SQL server located
> at CST? Is there any query to do that?
> I have a stored proc located in SQL server at CST zone where I need to
> query for the current date/time of the other zone sql servers.
> I tried below query at CST SQL server
> SELECT TOP 1 GETDATE() FROM [SERVER-PST].master.dbo.syslocks
> But it always gives CST datetime.
> Please reply...
> Thanks
> RP
>