Attempting to restore a db from a network drive and use the 'with move' statement

Attempting to restore a db from a network drive and need to move the
log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
Here is the script that I am using for this procedure
===================================== USE MASTER
GO
RESTORE FILELISTONLY
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
RESTORE LOG Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
-- WITH NORECOVERY
RESTORE DATABASE Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH NORECOVERY
=====================================
Here are the error(s) that I am receiving
===================================== Server: Msg 913, Level 16, State 8, Line 3
Could not find database ID 65535. Database may not be activated yet or
may be in transition.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Server: Msg 5105, Level 16, State 2, Line 7
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
incorrect.
Server: Msg 3156, Level 16, State 1, Line 7
File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.war_wheelan@.yahoo.com wrote:
> Attempting to restore a db from a network drive and need to move the
> log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
> AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
> Here is the script that I am using for this procedure
> =====================================> USE MASTER
> GO
> RESTORE FILELISTONLY
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> RESTORE LOG Indii
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
> -- WITH NORECOVERY
> RESTORE DATABASE Indii
> FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> Backup\Indii DB'
> WITH NORECOVERY
> =====================================> Here are the error(s) that I am receiving
> =====================================> Server: Msg 913, Level 16, State 8, Line 3
> Could not find database ID 65535. Database may not be activated yet or
> may be in transition.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE LOG is terminating abnormally.
> Server: Msg 5105, Level 16, State 2, Line 7
> Device activation error. The physical file name 'C:\Program
> Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
> incorrect.
> Server: Msg 3156, Level 16, State 1, Line 7
> File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
> a valid location for the file.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE DATABASE is terminating abnormally.
>
It looks like you're trying to restore the log before the database has
been restored. Restore the database first, THEN the log.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
The reason that I attempted to restore the log file first is because I
receive the same error either way.
Tracy McKibben wrote:
> war_wheelan@.yahoo.com wrote:
> > Attempting to restore a db from a network drive and need to move the
> > log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
> > AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
> >
> > Here is the script that I am using for this procedure
> > =====================================> > USE MASTER
> > GO
> > RESTORE FILELISTONLY
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > RESTORE LOG Indii
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$INDII_NY2_PROD\Data\Indii_Log.LDF'
> > -- WITH NORECOVERY
> > RESTORE DATABASE Indii
> > FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
> > Backup\Indii DB'
> > WITH NORECOVERY
> > =====================================> >
> > Here are the error(s) that I am receiving
> > =====================================> > Server: Msg 913, Level 16, State 8, Line 3
> > Could not find database ID 65535. Database may not be activated yet or
> > may be in transition.
> > Server: Msg 3013, Level 16, State 1, Line 3
> > RESTORE LOG is terminating abnormally.
> > Server: Msg 5105, Level 16, State 2, Line 7
> > Device activation error. The physical file name 'C:\Program
> > Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
> > incorrect.
> > Server: Msg 3156, Level 16, State 1, Line 7
> > File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
> > a valid location for the file.
> > Server: Msg 3013, Level 16, State 1, Line 7
> > RESTORE DATABASE is terminating abnormally.
> >
> It looks like you're trying to restore the log before the database has
> been restored. Restore the database first, THEN the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Attempting to restore a db from a network drive and use the 'with move' statement

Attempting to restore a db from a network drive and need to move the
log file to a new location. CAN SOMEONE PLEAS REVIEW THE STATEMENTS
AND ERROR MESSAGES AND LET ME KNOW WHAT I AM DOING WRONG.
Here is the script that I am using for this procedure
=====================================
USE MASTER
GO
RESTORE FILELISTONLY
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
RESTORE LOG Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH MOVE 'Indii_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL$INDII_NY2_PROD\Data\Indii_L
og.LDF'
-- WITH NORECOVERY
RESTORE DATABASE Indii
FROM DISK = '\\NAS1\NASDisk\SQL Server\SQL Server Production
Backup\Indii DB'
WITH NORECOVERY
=====================================
Here are the error(s) that I am receiving
=====================================
Server: Msg 913, Level 16, State 8, Line 3
Could not find database ID 65535. Database may not be activated yet or
may be in transition.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Server: Msg 5105, Level 16, State 2, Line 7
Device activation error. The physical file name 'C:\Program
Files\Microsoft SQL Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF' may be
incorrect.
Server: Msg 3156, Level 16, State 1, Line 7
File 'Indii_Log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$PRODUCTION\Data\\Indii_log.LDF'. Use WITH MOVE to identify
a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.Tracy,
The reason that I attempted to restore the log file first is because I
receive the same error either way.
Tracy McKibben wrote:
> war_wheelan@.yahoo.com wrote:
> It looks like you're trying to restore the log before the database has
> been restored. Restore the database first, THEN the log.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Attaching Database Failed

Can someone please help me with this problem.
I Did a database backup from serverA and restore to
serverB using w2k. I detached the database from serverB,
and when try to attach to the restored database, I got an
error" Error 9003:The LSN(120998:148:1) passed to log
scan in database "xyz" is invalid.
How do I fix this error. Thanks.Hi
That is a Severity 20 error. Errors messages with a severity level of 20 or
higher are considered fatal errors and terminate the client connection.
There is a KB article # 328354 (FIX: A RESTORE DATABASE WITH RECOVERY
Statement Can Fail with Error 9003 or Error 9004), but that was fixed in
SP3.
If you are running SP3, you either have a corrupt DB, or you have
encountered another problem. In the latter's case, call your local Microsoft
PSS.
I would bet that the DB is corrupt. Where did you try to attach the DB
again, server B?
Can you try to restore the backup from Server A again on Server B and then
run DBCC CHECKDB and DBCC CHECKALLOC to see if you have corruption and it
came across in the backup?
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"Someone" <anonymous@.discussions.microsoft.com> wrote in message
news:1ff201c3e05c$bf1da530$a501280a@.phx.gbl...

quote:

---

> Can someone please help me with this problem.
> I Did a database backup from serverA and restore to
> serverB using w2k. I detached the database from serverB,
> and when try to attach to the restored database, I got an
> error" Error 9003:The LSN(120998:148:1) passed to log
> scan in database "xyz" is invalid.
> How do I fix this error. Thanks.

---

|||Thank you for your reply. I applied sp3 and reattach the
database and I got the same error again. I am doing a new
restore as I write this and I will try to attach again.
Thanks.

quote:

---

>--Original Message--
>Hi
>That is a Severity 20 error. Errors messages with a

---

severity level of 20 or

quote:

---

>higher are considered fatal errors and terminate the

---

client connection.

quote:

---

>There is a KB article # 328354 (FIX: A RESTORE DATABASE

---

WITH RECOVERY

quote:

---

>Statement Can Fail with Error 9003 or Error 9004), but

---

that was fixed in

quote:

---

>SP3.
>If you are running SP3, you either have a corrupt DB, or

---

you have

quote:

---

>encountered another problem. In the latter's case, call

---

your local Microsoft

quote:

---

>PSS.
>I would bet that the DB is corrupt. Where did you try to

---

attach the DB

quote:

---

>again, server B?
>Can you try to restore the backup from Server A again on

---

Server B and then

quote:

---

>run DBCC CHECKDB and DBCC CHECKALLOC to see if you have

---

corruption and it

quote:

---

>came across in the backup?
>
>--
>--
>Mike Epprecht, Microsoft SQL Server MVP
>Epprecht Consulting (PTY) LTD
>Johannesburg, South Africa
>Mobile: +27-82-552-0268
>IM: mike@.NOSPAMepprecht.net
>Specialist SQL Server Solutions and Consulting
>"Someone" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:1ff201c3e05c$bf1da530$a501280a@.phx.gbl...
serverB,[QUOTE]
an[QUOTE]
>
>.
>

---

|||I just finished the restore, and got the same error when
I tried to attach to the database again.

quote:

---

>--Original Message--
>Thank you for your reply. I applied sp3 and reattach the
>database and I got the same error again. I am doing a

---

new

quote:

---

>restore as I write this and I will try to attach again.
>Thanks.
>
>severity level of 20 or
>client connection.
DATABASE[QUOTE]
>WITH RECOVERY
>that was fixed in
or[QUOTE]
>you have
>your local Microsoft
to[QUOTE]
>attach the DB
on[QUOTE]
>Server B and then
>corruption and it
in[QUOTE]
>message
>serverB,
got[QUOTE]
>an
>.
>

---

|||wsd
From http://www.google.com.tr/search?q=e...elopmentnow.com

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--Zorpie
In some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie
|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie
|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.

> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignorin
g
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will creat
e a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if
they have any emergency
rescue options. It will probably lead to a possibly both logically and physi
cally inconsistent
database. So this is not something you want to do. Probably to dig up your m
ost recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of th
e
> log files was not getting backed up. (there were three different log file
s)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment, ignor
ing
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.

> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attaching a database, but missing a file

The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie

Attach/restore databases in SQL Server 2000

Hi,

I've got a question regarding attaching/restoring of DBs and wonder if anyone could help me out.

Does SQL Server 2000 provide a functionality to attach/restore DB automatically? ie, some kind of polling service to attach/restore DB that were detached previously?

Thanks

Danny.

No.

And why would it. There could be dozens of experimental, sample, demo, 'old' database files on the computer. It should not ATTACH anything that it is not specifically instructed to attach.

|||

why not?

Imagine the scenerio where there are 100 salesman with a laptop each and each laptop running a local version of web portal supported by local MSDE backend (only relevant database will be on the machine).

A WISE script executable will be created for updates of portal and existing database. If there's a new database required, the server should be able to be aware of it an attach it automatically.

Surely there's got to be a way of attaching database to 100 machines automatically.

|||

You can write scripts, you can even write stored procedures that automatically execute when the server first starts, and you can use Replication.

But if you find an 'automatic' way to attach databases, please let us know.

|||

Will certainly do. "Inside SQL Server 2000" just arrived. I'll consult it instead.

|||

SQL Server has no way for automatic / implicit attaching of databases. You will have to use (as Anrie indicated a SP or task to search for files). Why don′t you put that in your installer script ? As from your description, it is running on the machine where the databases reside on.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Attach/Detach versus Restore/Backup

Hello All,
Given a choice between attach/detach and restore/back up a database(and
subsequently deleting the database) , which one is faster? What I am
interested is, which of the above will release all the memory and other
resources.
Thanks in advance.
Regards,
SoumitraDefine releasing memory and other resources. Memory is
controlled at the server level.
If you delete (nee drop) the DB, you're just deleting it.
Detach does that - it detaches it, but kinda expects to be
re-attached after, say, copying the file. So if your goal
is to delete, I would do a full normal BACKUP and then
delete.
There are also differences in the process: for example, if
using RESTORE, you get your statistics rebuilt.
I would not define things in terms of speed per se if this
is a mission-critical DB you need to do something with.
Speed is certainly a factor, but being down because you
did the wrong thing possibly will negate any speed
differential.
>--Original Message--
>Hello All,
>Given a choice between attach/detach and restore/back up
a database(and
>subsequently deleting the database) , which one is
faster? What I am
>interested is, which of the above will release all the
memory and other
>resources.
>Thanks in advance.
>Regards,
>Soumitra
>
>.
>|||Backup/Restore advantages:
1. Backup you can do on-line while Detach is off-line only operation
2. Backup will create file which has less size than original DB
3. Backup will contain the whole DB (DB+Log) while with Detach you will have
separate file for DB and Log.
4. With backup you can check integrity to be sure file iz not corrupted
after actual backup.
Detach advantages:
1. It is faster to move DB with Detach than with backup - you will need just
copy files and don't need to spend a time on a backup operation.
2. The files will contain current DB and current Transaction log while in
case of full backup you will loose current Transaction Log - it is important
in case of emergency situation with replication.
Regards.
---
All information provided above AS IS.
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> Given a choice between attach/detach and restore/back up a database(and
> subsequently deleting the database) , which one is faster? What I am
> interested is, which of the above will release all the memory and other
> resources.
> Thanks in advance.
> Regards,
> Soumitra
>|||Thanks Guys.
One question that remains is how can one tell SQL Server to release the
memory it holds onto? Let's say I create 1000 databases in a single instance
of SQl Server/MSDE. At the end of this the memory usage let say is 200 MB.
Now out of the 1000 databases, the first 100 databases are not used and
there is no user connected to it.
Is there any way to get back the momory associated with the first 100
databases?
Thanks.
Regards,
Soumitra
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
> Hello All,
> Given a choice between attach/detach and restore/back up a database(and
> subsequently deleting the database) , which one is faster? What I am
> interested is, which of the above will release all the memory and other
> resources.
> Thanks in advance.
> Regards,
> Soumitra
>|||Hi Soumitra
You just drop the databases which are not in use. For
precautionary steps , just have backup of the databases
which you will be DROPING , move these backup files to
Tape or some secured place .
Regards
Suri
>--Original Message--
>Thanks Guys.
>One question that remains is how can one tell SQL Server
to release the
>memory it holds onto? Let's say I create 1000 databases
in a single instance
>of SQl Server/MSDE. At the end of this the memory usage
let say is 200 MB.
>Now out of the 1000 databases, the first 100 databases
are not used and
>there is no user connected to it.
>Is there any way to get back the momory associated with
the first 100
>databases?
>Thanks.
>Regards,
>Soumitra
>"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote
in message
>news:ewos$m9pDHA.372@.TK2MSFTNGP11.phx.gbl...
>> Hello All,
>> Given a choice between attach/detach and restore/back
up a database(and
>> subsequently deleting the database) , which one is
faster? What I am
>> interested is, which of the above will release all the
memory and other
>> resources.
>> Thanks in advance.
>> Regards,
>> Soumitra
>>
>
>.
>|||Soumitra
Why are you so worried about releasing the memory? SQL
Server will use all the memory it needs from the amount
that is available . It will release it if another process
requires it, so usually it is not a problem. It can
sometimes be unpredictable when sharing a server with non
SQL Server applications, so it is best practice to not run
anything but SQL Server on your server.
If you are running another application and are having
memory problems you can always set SQL Server to use fixed
memory. You may slow the performance of your SQL Server by
doing this.
Hope this helps.
John|||"Allan Hirt" <allanh@.avanade.com> wrote in message
news:02f301c3a7e2$5dab22d0$a601280a@.phx.gbl...
> Detach does that - it detaches it, but kinda expects to be
> re-attached after, say, copying the file. So if your goal
> is to delete, I would do a full normal BACKUP and then
> delete.
In what way does it "expect" to be re-attached?
References to the detached database are removed. There is no expectancy.
Never mind the theological debate about whether or not a piece of software
CAN expect something ;)

attach vs restore

When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
Tom
On Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent
|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.

> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator
|||Steen Schlter Persson (DK) wrote:[vbcol=seagreen]
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).

attach vs restore

When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
TomOn Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server
,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.

> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server
,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
Regards
Steen Schlter Persson
Database Administrator / System Administrator|||Steen Schlter Persson (DK) wrote:
> tshad wrote:
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
[vbcol=seagreen]
...[vbcol=seagreen]
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).

attach vs restore

When moving all yous database to a new server where you install Sql Server,
is it better to use detach, copy database to new server than attach it or
backup on the old server and restore the backup.
I assume that I don't really need to move the Master or do I?
Thanks,
TomOn Apr 19, 7:02 pm, "tshad" <t...@.home.com> wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
It's your preference to use restore method or detach/attach database.
When it comes to master most of the time your primary server goes
south and you need to set up a new server that's when you restore
master to your new server.
Good day,
Bulent|||"tshad" <t@.home.com> wrote in message
news:e4%23DbeugHHA.4692@.TK2MSFTNGP04.phx.gbl...
> When moving all yous database to a new server where you install Sql
> Server, is it better to use detach, copy database to new server than
> attach it or backup on the old server and restore the backup.
Either works. If you can detach, copy and attach w/o impacting availability
(e.g. not a 24/7 shop, etc.) that's pretty straightforwad.
Otherwise, you tend to need to do a full backup, restore with norecovery,
then a log, and work to get it in synch.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||tshad wrote:
> When moving all yous database to a new server where you install Sql Server,
> is it better to use detach, copy database to new server than attach it or
> backup on the old server and restore the backup.
> I assume that I don't really need to move the Master or do I?
> Thanks,
> Tom
>
Hi,
Like already mentioned you can use both methods but personally I'd
always recommend using backup/restore. The reason is simply that first
of all it's an on-line operation to do the backup but secondly (and in
my opinion more important...) you will have your source database
operational all the time which means you'll always have something to
fall back to.
When you use the detach/attach method, you'll have to detach you source
database which means that at that point you haven't even got your source
in a safe condition any longer. If for some reason the attach fails then
you haven't got anything. In that case you'll have to find your backup
and restore it. In absolute worst case it could also happen that your
backup is corrupt as well and can't be restored and then you'll have a
problem...:-). (...I agree that that might be a real worst case
scenario, but I so often hear about people having unusable backup files
so I wouldn't be surprised that it could happen...).
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Steen Schlüter Persson (DK) wrote:
> tshad wrote:
>> When moving all yous database to a new server where you install Sql
>> Server, is it better to use detach, copy database to new server than
>> attach it or backup on the old server and restore the backup.
>> I assume that I don't really need to move the Master or do I?
When I do an "equipment replacement" - which is your case as I suspect -
I do it even in more straightforward way: stop original SQL server, copy
all MDF and LDF files (including master) to the stopped new server,
start new server.
However, it only works if all the paths are identical AND the accounts,
under which SQL Server runs, are identical.
(I had a problem trying to do this trick for an original SQL server
running under Local System and new server running under dedicated user.
But that is a different topic.)
That saves all information including users/passwords, backup history,
etc. There are additional pieces of information to consider - computer
name, for example - but works like charm.
If the upgrade intends a reconfiguration, the choice is made based on
the downtime tolerance, data corruption possibilities, etc.
If the DBs are not that large and/or there is a reasonably wide downtime
window, it is a matter of preference as Steen just explained:
...
> Like already mentioned you can use both methods but personally I'd
> always recommend using backup/restore. The reason is simply that first
> of all it's an on-line operation to do the backup but secondly (and in
> my opinion more important...) you will have your source database
> operational all the time which means you'll always have something to
> fall back to.
> When you use the detach/attach method, you'll have to detach you source
> database which means that at that point you haven't even got your source
> in a safe condition any longer. If for some reason the attach fails then
> you haven't got anything. In that case you'll have to find your backup
> and restore it. In absolute worst case it could also happen that your
> backup is corrupt as well and can't be restored and then you'll have a
> problem...:-). (...I agree that that might be a real worst case
> scenario, but I so often hear about people having unusable backup files
> so I wouldn't be surprised that it could happen...).