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 SQL Server 2K sp3

NOT 3a. I have to rebuild a system that was using sp3 so I have to restore
the master database. The system is using sp3 and NOT 3a. I can't find sp3
anymore?Never mind. I found the decompressed files on another server. It seems
strange for MS to have made this difficult to obtain for situations like
this, though.
"michelle" <michelle@.nospam.com> wrote in message
news:uMHak8SNFHA.3928@.TK2MSFTNGP09.phx.gbl...
> NOT 3a. I have to rebuild a system that was using sp3 so I have to restore
> the master database. The system is using sp3 and NOT 3a. I can't find sp3
> anymore?
>

Need script to kill all processes on a database

I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

While I'm going down this path, does anybody have a script to look at the master database and dynamically manage their backup strategy? That would be super cool?!?!

Thanks,

Alex8675I restore a backup every week. It serves a couple of purposes. One, to verify the backup and secondly, to give us a current testing/training environment. As things evolve, it would be nice to script this and then schedule it as a job.

I can write the restore statement, but it fails if any sessions are open on the db. I thought about using sp_who and the the kill statement. But I was having trouble using the recordset returned by sp_who.

Now I'm thinking the sysprocess table is a better place to look. Does anybody have some experience in this area and can point me in the right direction?

Go to google type "kill all processes IN A DATABASE SQL SERVER" i got atleast 10 different SP's which will do your job.....choose the one which suits you|||this will work if you are not logged connected to the database and no other sysadmin is doing anything...

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE|||Very elegant solution and exactly what I was looking for...but I couldn't find and was going a long way around to find what was readily available.|||this will work if you are not logged connected to the database and no other sysadmin is doing anything...

ALTER DATABASE MyDatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
This one's not as elegant, but worked when I had to disconnect / move the log files tonight.
DECLARE @.sql nVARCHAR(4000)
SET @.sql = ''

-- To disconnect, first kill all processes.
SELECT @.sql = @.sql + ' KILL ' + CAST(spid AS VARCHAR(10)) + ' '
FROM master.dbo.sysprocesses
WHERE DB_NAME(dbid) = @.MYDB
AND spid > 50 AND spid <> @.@.SPID
EXEC(@.sql)

Thrasymachus; Do I have to do something to undo the SET?

Need restore help...

Hi,
I am trying to find out where SQL Server 2000 writes
when and which last position of a transaction log was
restored. I am restoring full database backup,
differential backup and multiple transaction logs.
Full backup = 1 x week
Differential backup = 6 x week
Transaction log backup = hourly
I am restoring this to another server via SQL Server
Agent jobs. When I get the error of:
The log in this backup set begins at LSN
39300000000091600001, which is too late to apply to
the database. An earlier log backup that includes LSN
38932000000015600001 can be restored. [SQLSTATE 42000]
(Error 4305) RESTORE LOG is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.
There must be a comparison going on somewhere... where
there is some value that gives the last position or
number of the last time a transaction log was restored.
I am hoping that someone from microsoft comes to my
rescue or anyone else who knows where i can find this
information. This is very valuable information that will
help in releaving this error from happening.
Please advise...
Cheers!
Brettif you do
RESTORE HEADERONLY FROM ...
you will see the first LSN and last LSN.
"Brett" <anonymous@.discussions.microsoft.com> wrote in message
news:084d01c3d6e3$30e1a3c0$a001280a@.phx.gbl...
> Hi,
> I am trying to find out where SQL Server 2000 writes
> when and which last position of a transaction log was
> restored. I am restoring full database backup,
> differential backup and multiple transaction logs.
> Full backup = 1 x week
> Differential backup = 6 x week
> Transaction log backup = hourly
> I am restoring this to another server via SQL Server
> Agent jobs. When I get the error of:
> The log in this backup set begins at LSN
> 39300000000091600001, which is too late to apply to
> the database. An earlier log backup that includes LSN
> 38932000000015600001 can be restored. [SQLSTATE 42000]
> (Error 4305) RESTORE LOG is terminating abnormally.
> [SQLSTATE 42000] (Error 3013). The step failed.
> There must be a comparison going on somewhere... where
> there is some value that gives the last position or
> number of the last time a transaction log was restored.
> I am hoping that someone from microsoft comes to my
> rescue or anyone else who knows where i can find this
> information. This is very valuable information that will
> help in releaving this error from happening.
> Please advise...
> Cheers!
> Brett
>

Need restore help...

Hi,
I am trying to find out where SQL Server 2000 writes
when and which last position of a transaction log was
restored. I am restoring full database backup,
differential backup and multiple transaction logs.
Full backup = 1 x week
Differential backup = 6 x week
Transaction log backup = hourly
I am restoring this to another server via SQL Server
Agent jobs. When I get the error of:
The log in this backup set begins at LSN
39300000000091600001, which is too late to apply to
the database. An earlier log backup that includes LSN
38932000000015600001 can be restored. [SQLSTATE 42000]
(Error 4305) RESTORE LOG is terminating abnormally.
[SQLSTATE 42000] (Error 3013). The step failed.
There must be a comparison going on somewhere... where
there is some value that gives the last position or
number of the last time a transaction log was restored.
I am hoping that someone from microsoft comes to my
rescue or anyone else who knows where i can find this
information. This is very valuable information that will
help in releaving this error from happening.
Please advise...
Cheers!
Brettif you do
RESTORE HEADERONLY FROM ...
you will see the first LSN and last LSN.
"Brett" <anonymous@.discussions.microsoft.com> wrote in message
news:084d01c3d6e3$30e1a3c0$a001280a@.phx.gbl...

quote:

---

> Hi,
> I am trying to find out where SQL Server 2000 writes
> when and which last position of a transaction log was
> restored. I am restoring full database backup,
> differential backup and multiple transaction logs.
> Full backup = 1 x week
> Differential backup = 6 x week
> Transaction log backup = hourly
> I am restoring this to another server via SQL Server
> Agent jobs. When I get the error of:
> The log in this backup set begins at LSN
> 39300000000091600001, which is too late to apply to
> the database. An earlier log backup that includes LSN
> 38932000000015600001 can be restored. [SQLSTATE 42000]
> (Error 4305) RESTORE LOG is terminating abnormally.
> [SQLSTATE 42000] (Error 3013). The step failed.
> There must be a comparison going on somewhere... where
> there is some value that gives the last position or
> number of the last time a transaction log was restored.
> I am hoping that someone from microsoft comes to my
> rescue or anyone else who knows where i can find this
> information. This is very valuable information that will
> help in releaving this error from happening.
> Please advise...
> Cheers!
> Brett
>

---