Database Snapshots question

Dear all,
How can I make a snapshot in sql25k? When I do click on the option 'Database
snapshots' only appears 'refresh'.
Thanks for any input,from bol
CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO
"Enric" wrote:

> Dear all,
> How can I make a snapshot in sql25k? When I do click on the option 'Databa
se
> snapshots' only appears 'refresh'.
> Thanks for any input,
>|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Omnibuzz" wrote:
> from bol
> CREATE DATABASE AdventureWorks_dbss1800 ON
> ( NAME = AdventureWorks_Data, FILENAME =
> 'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\AdventureWorks_data_1800.ss' )
> AS SNAPSHOT OF AdventureWorks;
> GO
> --
>
>
> "Enric" wrote:
>sql

Database Restore Problem

I've created a Restore Job with owner sa, run as dbo, to refresh a Test database from Production.

After ONE successful refresh from the Production database, this
job fails with:
Msg 3101, Sev 16: Exclusive access could not be obtained because the
database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE
42000]

There are no users connected to the database or processes running against it.

The syntax is as follows:
ALTER DATABASE DBA_pfaids_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE DBA_pfaids_test
FROM DISK = 'd:\PFAIDS\restore\PRODbackup.bak'
WITH REPLACE,
MOVE 'SS2K_prod_empty_Data' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Data.MDF',
MOVE 'SS2K_prod_empty_Log' TO 'D:\MSSQL\DATA\DBA_pfaids_test_Log.LDF'

ALTER DATABASE DBA_pfaids_test SET MULTI_USER

Does this have something to do with the initial refresh from Production?
How can I get it to work?

Thanks.
MaryYou probably have either a (some) thoughtless user(s) who leave their connections open at the end of the day, or scheduled jobs that access the database.

You cannot put a database in single user mode while others are attached. You could preface your script with a call to sp_who2 and capture the returned data in a table. Then use a cursor to inspect the table, and when you find a spid attached to the database you want to restore, kill the spid.

Once you have finished with the cursor, close and deallocate. repeat until no losers (oops ... 'xcuse me ... users) are left in the database. When that occurs, execute the alter to put it into single_user, and proceed with the restore.

Things would run so much better if we just didn't have to let users into our databases ;)|||Thanks for you reply.

Actually, users do not access this database. I created it for testing purposes only, and they don't even know it exists. The restore works fine in Enterprise Manager, but fails when doing it with T-SQL. (I am trying to automate the restore).|||Things would run so much better if we just didn't have to let users into our databases ;)

~"This job would be great if it were'nt for the customers"
-Clerks|||The microsoft tools are getting a bit annoying with how "helpful" they are trying to be. If you have not already, shut down enterprise manager, and close the object browser in Query Analyzer. These two things are the top cause of this error. If that does not help, check to see if you have any extra windows open in Query Analyzer.|||Also check if the QA window you're trying to do the restore in isn't connected to the same database (switch to master to be sure).

Lex

Database refresh question

There's a sql server 2000 database that was created as a copy of another db, let's say db1 and copy_of_db1. db1 has been updated (structure and data) since copy_of_db1 was created, while copy_of_db1 has remained static. I now need to update copy_of_db1 to be in sync with db1 and use copy_of_db1 so I can drop db1. What would be the fastest and most efficient way to update copy_of_db1 to mirror the current db1?

Backup the updated one and restore it, in the Backup and restore wizard choose the restore from device option you also have the option to change the name of the restored one. Hope this helps.

|||Thanks!

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guys
David
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK =
N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guysDavid
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK =
N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys

Database refresh

Hi,
I want to refresh one of the database from production server to QA server
every first day of the month. How can I do it through scheduling a job? Any
one has scripts handy?
Also, will it transfer all the logins when I refresh the database. If not,
how can I achieve this task too?
Thank you guysDavid
You asked two questions
1)
Perform BACKUP /RESTORE DATABASE somethng like that
BACKUP DATABASE myname TO DISK = N'\\Remote(QA)\backup\myname .BAK'
Create a job which executes this command , just make sure that you drop the
old verison of your database on QA server\
2)
Search on internet for "sp_help_revlogin" stored procedure provided by MS to
move logins
"David" <David@.discussions.microsoft.com> wrote in message
news:CA6FF754-AA50-46D3-9BD6-0FF8594AD2AA@.microsoft.com...
> Hi,
> I want to refresh one of the database from production server to QA server
> every first day of the month. How can I do it through scheduling a job?
> Any
> one has scripts handy?
> Also, will it transfer all the logins when I refresh the database. If not,
> how can I achieve this task too?
> Thank you guys