Database question related to Replication

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sqldba - 27 Apr 2006 16:08 GMT

I need you help to have a best practice that i can use to bring in effect.In
my SQL Server 2000 i have database name XYZ (Production Database) & i have
created another Database on same server as XYZ_Monthly.
As per Reporting requirement at the last day of every month i have to backup
XYZ Production DB & restore to Reporting Database which is XYZ_Monthly. I
know i can schedule Monthly backup, but can i schedule monthly restore(I
don't think so) or another option is about using replication (Snapshot)on
the same server, where the same server is Dist,Pub & Sub.
Please let me know the best approach to over come this task. Any help will
be highly appreciated. Thank you

Reply to this Message

Paul Ibison - 27 Apr 2006 16:47 GMT

Certainly replication could be used here, but probably the simplest solution
would be a backup and restore in a single scheduled job. The script for this
is quite straightforward (see BOL for 'Backup' and 'Restore'), with the only
issue being how to ensure there aren't any connections open on the reporting
database which would prevent the restore. I'd use "alter database dbname set
single_user with rollback immediate". Then you would issue a 'use
otherdbname' immediately prior to starting the restore.
I'd also comment that you could use transactional replication and have the
reporting database almost continually current, if this would be of business
interest.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Reply to this Message

sqldba - 27 Apr 2006 17:57 GMT

Thanks for your feedback & quick reply, i appreciate. as this requirement
about reporting is every end of the month, so backup & restore is good
solution. I completely agree with you about Tr. Replication, but they are
going to ran report for the end of month & after that the DB will not be
used until next month.

Reply to this Message

sqldba - 27 Apr 2006 23:43 GMT

Can you please help me to see if my script is wrong, as i am scheduling my
Database RESTORE Job. This Job will run once in every month. My schedule is
fine but problem is on my script & getting following error.
Script:-
alter database CCS_SITE_Monthly
set RESTRICTED_USER
with rollback immediate
restore database CCS_SITE_Monthly
from disk = 'E:\Temp\ccs_site. bak'
with replace;

ERROR:-
Exclusive access could not be obtained because the database is in use.
[SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.

Reply to this Message

Hilary Cotter - 28 Apr 2006 00:40 GMT

Kill all users in the database. use sp_who2 to identify them and then kill
their spids.

Signature

Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Can you please help me to see if my script is wrong, as i am scheduling my
> Database RESTORE Job. This Job will run once in every month. My schedule
[quoted text clipped - 12 lines]
> [SQLSTATE 42000] (Error 3101) RESTORE DATABASE is terminating abnormally.
> [SQLSTATE 42000] (Error 3013). The step failed.

Reply to this Message

sqldba - 28 Apr 2006 02:24 GMT

I did that & still getting same error message. Infact i am only one logged
in to this server.no user nor any query analyzer is open. This server is on
Latest SP-4.

Reply to this Message

Paul Ibison - 28 Apr 2006 22:31 GMT

It's probably your spid that is blocking. Try inserting

Use Master
Go

before the "restore database" command.

Cheers,
Paul Ibison

Reply to this Message