Back up file?

Hello everyone,
I have a PC and I am using it as a "mini" server for Temp testing purpose. This PC is on the network. I am wondering if there is a DB file with specific name and extension I can save on our network drive or folder which is backed up automatically overnight daily.
Any suggestion/comments are highly appreciated!not sure I got your question .. did you want to ask if you could back up your database to a network server|||That's correct. I have this "mini server" as a testing machine which is not "supported" by IT group since it is not part of data center. I do want my "mini server" backed up. I know all the files on the network drive get backed up overnight so that I am wondering if there is a file or set of files I could push onto our network drive so that my database can be backed up.

Thank you for the help.|||moving the 'YourDB.mdf' and 'YourDB_Log.ldf' files to the network drive will help you to back it up

;)|||moving the 'YourDB.mdf' and 'YourDB_Log.ldf' files to the network drive will help you to back it up

;)

Do you think your Database will work after that ?

What you need to do is schedule a backup of your database using the "Backup database" command and move it over to the file share.|||Do you think your Database will work after that ?Definitely not, but at least it could be recovered if you had enough expertise.

On a more serious note, you really need to use the BACKUP DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp) command to produce a dump file. That file can be copied wherever you like, backed up, whatever... It can be restored using RESTORE DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp) command, and things will be exactly as they were when you issued the first BACKUP DATABASE command.

-PatP|||Thank you for all replies. Just want to be clear, I need to use database backup command to backup my current DB. Can I assume everything (tables, stored procedures, DTS package and etc) I have created in the DB will be saved and backed up on the network. Is my assumption right?
Many thanks.|||All of the transactions that are committed by the end of the backup operation will be included in the backup file. One file will be produced, regardless of how many files make up your database (you could have an MDF, a few NDF, and a couple of LDF files and still get just one dump file). The dump file created by the backup can then be zipped, copied, etc as you see fit, and that dump can be used to restore the database later.

-PatP|||... (tables, stored procedures, DTS package and etc) I have created in the DB will be saved and backed up on the network. Is my assumption right?
Many thanks.
DTS packages are not stored in user databases. If they are stored on the SQL Server, they are in the MSDB database. Soooooo ... if you want to save everything, you will need to backup **all** your databases then move them off server for storage.|||would you please more explicit about DTS packages are not stored in user databases. if you want to save everything, you will need to backup **all** your databases then move them off server for storage.
How and what I should do in order to backup **all**? Is there a function or command I should do? Please advise.|||Open Books OnLine. Search the index for the "backup" command. Make sure you run a backup for each and every database on your server. Take all the backup files created during this process and copy them to a safe place off your server.

If you are so inclined, you can create a database mainenance plan to backup all databases on the server in one job than can be run by the SQLServer Agent, if it is active. Look up "Database Maintenance Plan Wizard" in Books OnLine.|||I tried to save DTS packages. I have backed up msdb database on my local PC (which is SQL server as well) c:program files\MS SQL server\backup\msdb_backup (I just wanted to test to see if it works the way I expected since this is my first time try so that only saved on the server directory.)
I went back to Enterprise Mgr and select "Restore database...", and select the file [msdb_backup] which was created through backup command. I got the error message attached. Not sure why. Would you please give me a hint?

Thank you in advance for helping me out!|||here is the error msg.|||Have you got Query Analyzer open at the time that you try to back up the database?
If so, this spawns a process that connects to your database. When you backup a database, SQL Server tries to obtain an exclusive database lock (to ensure that data cannot be changed while the backup is in progress) and it cannot do this if there is another process connected to the database.

Check out 'How to view current server activity' in Books Online.

Once you've made sure that there are no other processes connected to your database, you can put the database in 'single user' mode by right-clicking on the database name, selecting Properties and checking the 'Restrict access' box on the Options tab.
Then backup your database.

Avoiding temp tables

Dear all,
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:

>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan

Avoid duplicate index while creating temp table

We are using SQL 2K with sp4.
In our stored procedure, we create a temp table (i.e. #tablename) and
various indexes within the temp table. If mutliple users execute this store
d
procedure at the same time, it would cause duplicate index error. As a
temporary fix, we attach an unique number to the index name. I like to know
what is the best way to solve this problem without extra work?
wingmanI posted a similar query yesterday ( in which I completely mispelled
temporary);
http://groups.google.co.uk/group/mi...b23b0ffd6?hl=en
the basic response was that the indexes can co-exist without naming
conflicts
Cheers
Will|||Wingman,
You can read about this in the BOL. They must be unique within a table or
view but do not need to be unique within a database. Each temporary table
(not global ones ##) is unique. SQL Server create a unique name per user.
AMB
"Wingman" wrote:

> We are using SQL 2K with sp4.
> In our stored procedure, we create a temp table (i.e. #tablename) and
> various indexes within the temp table. If mutliple users execute this sto
red
> procedure at the same time, it would cause duplicate index error. As a
> temporary fix, we attach an unique number to the index name. I like to kn
ow
> what is the best way to solve this problem without extra work?
> wingman|||dude, step back a bit, and think about a solution where you don't have
multipel users accessing the same temp table.
this has GOT to lead to integrity issues.

AUTOSTATS on tempdb

Hello,

I've found this recomendation recently:

"To help speed queries or joins on large temp tables, be sure the AUTOSTATS database option is turned on for tempdb"

How can I find out what this option set for our tempdb? If I go to "database options" of tempdb under "System databases", I do not see that option at all.

Please help. Thanks

Do you see 'Auto Create Statistics'?

Alternatively you can run sp_dboption on tempdb to see what the settings are:

exec sp_dboption 'tempdb'

You should see that 'auto create statistics' is set

Don

|||

run this query

select DATABASEPROPERTY( 'tempdb ', 'IsAutoCreateStatistics' )

1 = TRUE

0 = FALSE

NULL = Invalid input

Madhu