I want to synchronize local version of SQL Server Database 2005 with that of
remote version of SQL Server database 2005.
Are there any tools available which would help me in doing this so that all
the records in all the tables available in the remote SQL Server database is
also available in the local version of the SQL Server database.

Please advise.

S N (uandme72 (AT) invalid (DOT) com) writes:
The most obvious answer that comes to mind is replication, but replication
is a fairly heavy-handed business, and sometimes other solutions like
rolling your own is better.

Another alternative is to use BACKUP/RESTORE, possibly in combination
with log shipping, particularly if you only require that sync:ing
happens only a few times a day.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I would like to sync the remote and local databases once every day.
In this situation, kindly advise on how to achieve the task.
What are the tools available. I currently have only the SQL Server
Management studio Express which I am using to connect to the SQL Server and
create/delete tables etc.





"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

S N (uandme72 (AT) invalid (DOT) com) writes:
BACKUP/RESTORE is absolutely the best way. You need to backup the database,
and by restoring the backup, you test that the backup actually works.

To backup a database:

BACKUP DATABASE db TO DISK = 'somepath.bak'

To restore the database on another server (note that you may also
have to copy the database from one disk to another, unless there is
a path that both servers can use).

RESTORE DATABASE db FROM DISK = 'somepath'
WITH MOVE 'name1' TO 'path1',
MOVE 'name2' TO 'path2',
REPLACE

To find name1 and name2 do "sp_helpdb db" on the source database; you
find the names in the first column in the second result set. 'path1'
and 'path2' are the location you want for the database files on the
receiving servers.

You can schedule this as a job in SQL Server Agent. If you only have
Express, you can instead use Windows Task Scheduler. Use SQLCMD to
run the SQL commands.

Do you use SQL Server logins? They will not match after the restore
on the local server.

Note also that if you have not backed up the other database yet, this
means that the log the for database is auto-truncated. But once you
start to backup the database, the log will continue to grow if the
database is set to full recovery. If you are not interested in doing
up-to-the-point-recovery, set the database in simple recovery:

ALTER DATABASE db SET RECOVERY SIMPLE


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Are path1 and path2 physical paths (of the form c:\database\..... ) or
something else?


I use SQL server logins on both the remote server as well as local server.
How to ensure that the logins match when we restore the database from remote
to the local server and vice versa.

S N (uandme72 (AT) invalid (DOT) com) writes:
Physical paths.

Then you will need a script that you run as part of the restore job. Here
is such a script:

declare @sql nvarchar(MAX)
declare usercur cursor static local for
select 'ALTER USER ' + quotename(name) +
' WITH LOGIN = ' + quotename(name)
from sys.database_principals
where principal_id >= 5
and type_desc = 'SQL_USER'

open usercur

while 1 = 1
begin
fetch usercur into @sql
if @@fetch_status <> 0
break
exec(@sql)
end

deallocate usercur

The script assumes that the logins already exist on the target server.
If any user is missing on the target there will be an error. (But the
rest of the logins are remapped.)



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

All the methodologies indicated involve scripts etc to be done manually.
Is there any tool available for free, which can take backup/restore the
database from remote to local and vice versa?
Further, is there any tool which can help edit the data inside a table, just
like it is possible in MS Access.

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

S N (uandme72 (AT) invalid (DOT) com) writes:
Who says that?

In fact, the whole points with scripts is that they are the foundation
for automation.

The normal procedure would be to run them from SQL Server Agent. But
if I recall directly you are using Express Edition which does not
ship with Agent.

No big deal, you can use the Windows Task Scheduler instead and run
the scripts from SQLCMD.

The preferable way to change data is to use INSERT, UPDATE, DELETE. Again,
scripts can be automated. Pointing and clicking can't. But in Mgmt
Studio you can right-click a table and there is an Edit option as I
recall.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

I have tried logging onto the remote SQL server and run the command

BACKUP DATABASE db TO DISK = 'somepath.bak'

However, it did run and take backup of the remote database on the remote
server itself and it did not take the backup of remote database on the local
computer.
What i actually want is to take backup of the remote database on the local
computer.
please advise on how to achieve it.

"Erland Sommarskog" wrote:

S N (SN (AT) discussions (DOT) microsoft.com) writes:
So you want to be connected to server A and backup a database on server B?

You can only backup a datbase on a server if you are connected to it.

You can be connected to to server A and from server A run commands on
server B, using EXEC() AT, but it may get you into more trouble with
setting up linked servers than it pays back.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx