Need to back up a database and then restore it on another server

I need to back up a production database on one server and then copy it to a test server wherel it will be restored. The problem is that I don't know where to move the file to on the production server before I restore it. Is there a special folder I need to copy the file to? I see a folder named ProgramFiles\Microsoft SQL Server but beyond that I have no idea where to copy the file so that it can be restored?

Can someone help me out please?

After backing up your database to file on your production server, you should move the .bak file to your test server for restore. It does not matter where you put it on the test server as you will be able to browse for it when you actually perform the restore.

Need to back up & restore a SQL server database when only SQL 2000 and 2005 are available.

I am maintaining a .net 1.0 asp.net web application that accesses a SQL server 2000 database. I need to backup the database and then restore it on a laptop. Currently it resides on a server. The problem is that on the server they are using sql server 2005. If I backup using 2005 and then try and restore this database using SQL server 2000 or 2005 on the laptop, I get an error message. "Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE DATABASE is terminating abnormally.

What can I do to restore the database? Must it be backed up with SQL server 2000 and then restored with SQL Server 2005?

Please help!

you cannot back up a db from 2005 and restore it on 2000 server. Its possible the other way though.

sql

Need info about odbc and sql through access2k

I recently started with a company that uses access for a front end and sql2000 for the back. They have set up an ODBC connection though access2k to reach sql2k. This works fine but fouls up in regards to the record locking. You can set record locking in access but it has no effect. In the office help it says 'data in a form, report, or query from an Open Database Connectivity (ODBC) database is treated as if the No Locks setting were chosen, regardless of the RecordLocks property setting.'

Also, the access lock option info says it 'could' lock the few records around the record you wish to lock.

So my question is this: How can i (without destroying this ODBC link from access2k to sql2k) get locks for individual records to work successfully?

any info would be great, thankshttp://advisor.com/doc/13043 for information about record locking in Access.

For SQL server http://www.sql-server-performance.com/reducing_locks.asp and
http://www.databasejournal.com/features/mssql/article.php/3289661
HTH

need help with using maintenance plan..

background sql2kt, nt5
wondering if someone can help me with a backup issue.
if i were to create a maintenance plan to back up all user databases on a
server (like 60 of them)
1. how can i backup just one database on demand outside the maintenance
plan? and have it recognized by the maintenance when the next scheduled
backup occures? (such as purge file and so on)
2. what would be the difference of putting all 60 databases in one
maintenance vs. seperating them into 3 plans with staggering schedule?
any advice would be greatly appreciated.
> if i were to create a maintenance plan to back up all user databases on a
> server (like 60 of them)
> 1. how can i backup just one database on demand outside the maintenance
> plan? and have it recognized by the maintenance when the next scheduled
> backup occures? (such as purge file and so on)
> 2. what would be the difference of putting all 60 databases in one
> maintenance vs. seperating them into 3 plans with staggering schedule?
> any advice would be greatly appreciated.
1. you can always right click on a db and backup from there. i believe the
dbmp determines which backups to delete by reading the
msdb.sysdbmaintplan_history table. you would probably have to "forge" an
entry in that table to get your dbmp to delete your manual backups. i
wouldn't recommend this although i've done something similar with no ill
effects.
i usually keep a couple of weeks of backups on disk. every once in a while i
go through those backup directories looking for old manual backups that don't
need to be there any more and manually delete them.
2. one dbmp for 60 db's will backup them up one at a time in alphabetical
order. 3 seperate plans is much more of a headache to manage. the biggest
problem is that when you add a new db, if you forget to add it to one of
those 3 dbmp's, it won't get backed up. if you delete a db without changing
the dbmp, you'll get errors when the jobs run trying to work on that db
that's been deleted. if you have a dbmp for all user databases, then you
don't have to modify a dbmp every time you add a new db or delete a db.
|||thanks for such a good advice.
regarding #1, your suggestion works.
but if i want call that backup routine from the other scheduled task using
sql script, how would I know the backup dump file name currently available?
since the maintenance plan generate a new file name each day such as
MyDb_2004050401800.bak
"ch" <ch@.dontemailme.com> wrote in message
news:4097D623.1F2E0C9B@.dontemailme.com...[vbcol=seagreen]
a
> 1. you can always right click on a db and backup from there. i believe
the
> dbmp determines which backups to delete by reading the
> msdb.sysdbmaintplan_history table. you would probably have to "forge" an
> entry in that table to get your dbmp to delete your manual backups. i
> wouldn't recommend this although i've done something similar with no ill
> effects.
> i usually keep a couple of weeks of backups on disk. every once in a
while i
> go through those backup directories looking for old manual backups that
don't
> need to be there any more and manually delete them.
> 2. one dbmp for 60 db's will backup them up one at a time in alphabetical
> order. 3 seperate plans is much more of a headache to manage. the
biggest
> problem is that when you add a new db, if you forget to add it to one of
> those 3 dbmp's, it won't get backed up. if you delete a db without
changing
> the dbmp, you'll get errors when the jobs run trying to work on that db
> that's been deleted. if you have a dbmp for all user databases, then you
> don't have to modify a dbmp every time you add a new db or delete a db.
>

need help with using maintenance plan..

background sql2kt, nt5
wondering if someone can help me with a backup issue.
if i were to create a maintenance plan to back up all user databases on a
server (like 60 of them)
1. how can i backup just one database on demand outside the maintenance
plan? and have it recognized by the maintenance when the next scheduled
backup occures? (such as purge file and so on)
2. what would be the difference of putting all 60 databases in one
maintenance vs. seperating them into 3 plans with staggering schedule?
any advice would be greatly appreciated.> if i were to create a maintenance plan to back up all user databases on a
> server (like 60 of them)
> 1. how can i backup just one database on demand outside the maintenance
> plan? and have it recognized by the maintenance when the next scheduled
> backup occures? (such as purge file and so on)
> 2. what would be the difference of putting all 60 databases in one
> maintenance vs. seperating them into 3 plans with staggering schedule?
> any advice would be greatly appreciated.
1. you can always right click on a db and backup from there. i believe the
dbmp determines which backups to delete by reading the
msdb.sysdbmaintplan_history table. you would probably have to "forge" an
entry in that table to get your dbmp to delete your manual backups. i
wouldn't recommend this although i've done something similar with no ill
effects.
i usually keep a couple of weeks of backups on disk. every once in a while
i
go through those backup directories looking for old manual backups that don'
t
need to be there any more and manually delete them.
2. one dbmp for 60 db's will backup them up one at a time in alphabetical
order. 3 seperate plans is much more of a headache to manage. the biggest
problem is that when you add a new db, if you forget to add it to one of
those 3 dbmp's, it won't get backed up. if you delete a db without changing
the dbmp, you'll get errors when the jobs run trying to work on that db
that's been deleted. if you have a dbmp for all user databases, then you
don't have to modify a dbmp every time you add a new db or delete a db.|||thanks for such a good advice.
regarding #1, your suggestion works.
but if i want call that backup routine from the other scheduled task using
sql script, how would I know the backup dump file name currently available?
since the maintenance plan generate a new file name each day such as
MyDb_2004050401800.bak
"ch" <ch@.dontemailme.com> wrote in message
news:4097D623.1F2E0C9B@.dontemailme.com...
a[vbcol=seagreen]
> 1. you can always right click on a db and backup from there. i believe
the
> dbmp determines which backups to delete by reading the
> msdb.sysdbmaintplan_history table. you would probably have to "forge" an
> entry in that table to get your dbmp to delete your manual backups. i
> wouldn't recommend this although i've done something similar with no ill
> effects.
> i usually keep a couple of weeks of backups on disk. every once in a
while i
> go through those backup directories looking for old manual backups that
don't
> need to be there any more and manually delete them.
> 2. one dbmp for 60 db's will backup them up one at a time in alphabetical
> order. 3 seperate plans is much more of a headache to manage. the
biggest
> problem is that when you add a new db, if you forget to add it to one of
> those 3 dbmp's, it won't get backed up. if you delete a db without
changing
> the dbmp, you'll get errors when the jobs run trying to work on that db
> that's been deleted. if you have a dbmp for all user databases, then you
> don't have to modify a dbmp every time you add a new db or delete a db.
>

need help with using maintenance plan..

background sql2kt, nt5
wondering if someone can help me with a backup issue.
if i were to create a maintenance plan to back up all user databases on a
server (like 60 of them)
1. how can i backup just one database on demand outside the maintenance
plan? and have it recognized by the maintenance when the next scheduled
backup occures? (such as purge file and so on)
2. what would be the difference of putting all 60 databases in one
maintenance vs. seperating them into 3 plans with staggering schedule?
any advice would be greatly appreciated.> if i were to create a maintenance plan to back up all user databases on a
> server (like 60 of them)
> 1. how can i backup just one database on demand outside the maintenance
> plan? and have it recognized by the maintenance when the next scheduled
> backup occures? (such as purge file and so on)
> 2. what would be the difference of putting all 60 databases in one
> maintenance vs. seperating them into 3 plans with staggering schedule?
> any advice would be greatly appreciated.
1. you can always right click on a db and backup from there. i believe the
dbmp determines which backups to delete by reading the
msdb.sysdbmaintplan_history table. you would probably have to "forge" an
entry in that table to get your dbmp to delete your manual backups. i
wouldn't recommend this although i've done something similar with no ill
effects.
i usually keep a couple of weeks of backups on disk. every once in a while i
go through those backup directories looking for old manual backups that don't
need to be there any more and manually delete them.
2. one dbmp for 60 db's will backup them up one at a time in alphabetical
order. 3 seperate plans is much more of a headache to manage. the biggest
problem is that when you add a new db, if you forget to add it to one of
those 3 dbmp's, it won't get backed up. if you delete a db without changing
the dbmp, you'll get errors when the jobs run trying to work on that db
that's been deleted. if you have a dbmp for all user databases, then you
don't have to modify a dbmp every time you add a new db or delete a db.|||thanks for such a good advice.
regarding #1, your suggestion works.
but if i want call that backup routine from the other scheduled task using
sql script, how would I know the backup dump file name currently available?
since the maintenance plan generate a new file name each day such as
MyDb_2004050401800.bak
"ch" <ch@.dontemailme.com> wrote in message
news:4097D623.1F2E0C9B@.dontemailme.com...
> > if i were to create a maintenance plan to back up all user databases on
a
> > server (like 60 of them)
> >
> > 1. how can i backup just one database on demand outside the maintenance
> > plan? and have it recognized by the maintenance when the next scheduled
> > backup occures? (such as purge file and so on)
> >
> > 2. what would be the difference of putting all 60 databases in one
> > maintenance vs. seperating them into 3 plans with staggering schedule?
> >
> > any advice would be greatly appreciated.
> 1. you can always right click on a db and backup from there. i believe
the
> dbmp determines which backups to delete by reading the
> msdb.sysdbmaintplan_history table. you would probably have to "forge" an
> entry in that table to get your dbmp to delete your manual backups. i
> wouldn't recommend this although i've done something similar with no ill
> effects.
> i usually keep a couple of weeks of backups on disk. every once in a
while i
> go through those backup directories looking for old manual backups that
don't
> need to be there any more and manually delete them.
> 2. one dbmp for 60 db's will backup them up one at a time in alphabetical
> order. 3 seperate plans is much more of a headache to manage. the
biggest
> problem is that when you add a new db, if you forget to add it to one of
> those 3 dbmp's, it won't get backed up. if you delete a db without
changing
> the dbmp, you'll get errors when the jobs run trying to work on that db
> that's been deleted. if you have a dbmp for all user databases, then you
> don't have to modify a dbmp every time you add a new db or delete a db.
>

Need help with UDF useage. Trying to get away without using cursor.

I have a Function (say X) that takes 2 parameters and returns back a table result of multiple records.

And I have a query (say Q) that return rows of 2 columns that I need to feed Function X.

They way I do it right now is I have a cursor that loops through the result of Query Q
and call Function X as I pass the 2 values the the function.

As Function X return with the result set, I load it into a temporary table.

At the end of the cursor processing, I query the temporary table to return the complete result set.

Is there a way do this without using a cursor?

Here is my Function X top part:

alter FUNCTION ReturnItem
(
@.tableName varchar(50),
@.ItemID int
)
returns @.returnTable table
(
ItemName varchar(50),
ItemValue varchar(50),
[Timestamp] datetime
)

JB..

You want to use the CROSS APPLY capability of SS2k5 to "apply" the rows of one table to a UDF. Here is an example:

CREATE TABLE QuerySource
(
c1 INT,
c2 INT
)

INSERT QuerySource VALUES (1,1)
INSERT QuerySource VALUES (2,3)
INSERT QuerySource VALUES (10,15)
INSERT QuerySource VALUES (16,13)

CREATE FUNCTION ReturnItem
(
@.p1 INT,
@.p2 int
)
returns @.returnTable table
(
AddResult int,
SubtractResult int,
TimesResult int,
DivideResult int
)
AS
BEGIN
INSERT @.returnTable SELECT @.p1+@.p2, @.p1-@.p2,@.p1*@.p2,@.p1/@.p2
RETURN
END

SELECT *
FROM QuerySource qs CROSS APPLY dbo.ReturnItem(qs.c1, qs.c2)

|||Hello. Thank you very much for you help. That is exactly what I wanted to do. I never knew such feature exist. Anyway, I was afraid that when the number of data being passed becomes really big, the cursor will slow things down hence, I have to find this solution.

Thank you again.

JB..