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

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

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)

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.

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

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



[quoted text, click to view]

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

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

Use Master
Go

before the "restore database" command.

Cheers,
Paul Ibison