Attach remote DB file: location

Showing posts with label location. Show all posts

Friday, February 24, 2012

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

Sunday, February 19, 2012

Attempt to catch error using @@ERROR in SQL 2000 is failing

We have a stored procedure that calculates the floor nr for users at our company campus using their office location. The calculation is done by a function that returns an integer. Unfortunately, not all users enter their information correctly so the function sometimes raises an error. Below is the code of that stored procedure.

UPDATE PERSONS

SET FLOORNR = dbo.FloorNR(OFFICELOCATION)

WHERE OFFICELOCATION IS NOT NULL

IF(@.@.ERROR <> 0 OR @.@.ROWCOUNT = 0)

BEGIN

RAISERROR ('Failed to calculate the floor number', 16, 1 ) with nowait

END

However, when the function dbo.FloorNR fails, it doesn't raise our error, but it seems to raise the error that comes from dbo.FloorNR.

How can we catch errors that come from dbo.FloorNr so that we can raise our own error? Our company still uses SQL 2000, so we cannot use the SQL 2005 try/catch option.

Is it possible for you to try to handle some of those situations inside of the function and have it not return any values in the case of an error?|||We already handle a number of situations inside the functions. But once every week a user finds a new way to enter his office location and our procedure stops responding. At the moment, we have no way of controlling the input by our users, but we don't want to keep on expanding the procedure. In fact, we just want users to enter their office location in a standard way. We will change the application which handles that, but that change is still some months away. Untill then, we just want the catch any errors and log them.

Monday, February 13, 2012

Attaching database

I have an SQL server going on my Notebook.
I want to move it to another location and then reattach it.
I have forgotten how to get to the commandwindow where I can start with EXEC
....
reidarTDo you have QA installed on yuor laptop?
BOL says
This example detaches the pubs database with skipchecks set to true.
EXEC sp_detach_db 'pubs', 'true'
--This example attaches two files from pubs to the current server.EXEC
sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
"reidarT" <reidar@.eivon.no> wrote in message
news:eO%23dBlvKGHA.964@.tk2msftngp13.phx.gbl...
>I have an SQL server going on my Notebook.
> I want to move it to another location and then reattach it.
> I have forgotten how to get to the commandwindow where I can start with
> EXEC ....
> reidarT
>|||Here is an eg from BOL
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"reidarT" <reidar@.eivon.no> wrote in message
news:eO%23dBlvKGHA.964@.tk2msftngp13.phx.gbl...
>I have an SQL server going on my Notebook.
> I want to move it to another location and then reattach it.
> I have forgotten how to get to the commandwindow where I can start with
> EXEC ....
> reidarT
>

Attaching database

I have an SQL server going on my Notebook.
I want to move it to another location and then reattach it.
I have forgotten how to get to the commandwindow where I can start with EXEC
.....
reidarT
Do you have QA installed on yuor laptop?
BOL says
This example detaches the pubs database with skipchecks set to true.
EXEC sp_detach_db 'pubs', 'true'
--This example attaches two files from pubs to the current server.EXEC
sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
"reidarT" <reidar@.eivon.no> wrote in message
news:eO%23dBlvKGHA.964@.tk2msftngp13.phx.gbl...
>I have an SQL server going on my Notebook.
> I want to move it to another location and then reattach it.
> I have forgotten how to get to the commandwindow where I can start with
> EXEC ....
> reidarT
>
|||Here is an eg from BOL
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"reidarT" <reidar@.eivon.no> wrote in message
news:eO%23dBlvKGHA.964@.tk2msftngp13.phx.gbl...
>I have an SQL server going on my Notebook.
> I want to move it to another location and then reattach it.
> I have forgotten how to get to the commandwindow where I can start with
> EXEC ....
> reidarT
>

Attaching database

I have an SQL server going on my Notebook.
I want to move it to another location and then reattach it.
I have forgotten how to get to the commandwindow where I can start with EXEC
....
reidarTDo you have QA installed on yuor laptop?
BOL says
This example detaches the pubs database with skipchecks set to true.
EXEC sp_detach_db 'pubs', 'true'
--This example attaches two files from pubs to the current server.EXEC
sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
"reidarT" <reidar@.eivon.no> wrote in message
news:eO%23dBlvKGHA.964@.tk2msftngp13.phx.gbl...
>I have an SQL server going on my Notebook.
> I want to move it to another location and then reattach it.
> I have forgotten how to get to the commandwindow where I can start with
> EXEC ....
> reidarT
>|||Here is an eg from BOL
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"reidarT" <reidar@.eivon.no> wrote in message
news:eO%23dBlvKGHA.964@.tk2msftngp13.phx.gbl...
>I have an SQL server going on my Notebook.
> I want to move it to another location and then reattach it.
> I have forgotten how to get to the commandwindow where I can start with
> EXEC ....
> reidarT
>

Thursday, February 9, 2012

Attach to SQL Server 2K5 Express Problem

I am building a web app using VS 2K5 with SQL Server Express as the backend.
The project and database are stored in the default location: My
Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also downloaded and
installed the SQL Server Management Studio Express to be able to manage the
database. The problem I have is that I am unable to attach the database. I
go through the process but the SSMSE won't allow me to navigate to where my
database is. I'm needing to import a table with a lot of data. Is this a
bug? Do I need to temporarily move my database file? Thanks.
hi Phill,
Phill wrote:
> I am building a web app using VS 2K5 with SQL Server Express as the
> backend. The project and database are stored in the default location:
> My Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also
> downloaded and installed the SQL Server Management Studio Express to
> be able to manage the database. The problem I have is that I am
> unable to attach the database. I go through the process but the
> SSMSE won't allow me to navigate to where my database is. I'm
> needing to import a table with a lot of data. Is this a bug? Do I
> need to temporarily move my database file? Thanks.
I do not currently have VS2005 installed, but with SSMSE I was able to
navigate to My Documents\Visual Studio 2005\WebSites\MyWebSiteName (the
\WebSites\MyWebSiteName folders have been actually created by me as, again,
I do not have VS2005 installed on that machine) both with a windows
administrator member (trusted connections) and the "traditional" "sa" SQL
Server login (mixed mode security)
I do not think VS2005 sets some sort of particular ACLs for that folder,
but I can be wrong here..
the account running my SQLExpress instance is LocalSystem..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||When you created the database in that location, you probably did it as a
user instance because that's the Visual Studio default. The user running
Visual Studio has permissions to that directory because he created it. You
are now trying to attach it to the main SQL Server instance instead of the
user instance, The main SQL Express instance is running as Network Service
and more than likely does not have permissions to open files in you
application directory. You can get around this by giving Network Service
read-write permission on the database and log files. Be aware that while
the files are attached to the main instance, your application using the user
instance connection will not be able to access them.
Check this article for an explanation of user instances:
http://msdn.microsoft.com/sql/expres...xpuserinst.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:1CF63F8E-902B-4AD7-A2F8-B7DA7233D7AE@.microsoft.com...
>I am building a web app using VS 2K5 with SQL Server Express as the
>backend.
> The project and database are stored in the default location: My
> Documents\Visual Studio 2005\WebSites\MyWebSiteName. I also downloaded
> and
> installed the SQL Server Management Studio Express to be able to manage
> the
> database. The problem I have is that I am unable to attach the database.
> I
> go through the process but the SSMSE won't allow me to navigate to where
> my
> database is. I'm needing to import a table with a lot of data. Is this a
> bug? Do I need to temporarily move my database file? Thanks.
|||Thanks, I am now able to connect to the database. It didn't really do me any
good though. I want to import an Access table into it but there aren't any
tools to do so. No ODBC drivers, nothing. So how are we supposed to import
data into SSE2K? Thanks.
"Roger Wolter[MSFT]" wrote:

> When you created the database in that location, you probably did it as a
> user instance because that's the Visual Studio default. The user running
> Visual Studio has permissions to that directory because he created it. You
> are now trying to attach it to the main SQL Server instance instead of the
> user instance, The main SQL Express instance is running as Network Service
> and more than likely does not have permissions to open files in you
> application directory. You can get around this by giving Network Service
> read-write permission on the database and log files. Be aware that while
> the files are attached to the main instance, your application using the user
> instance connection will not be able to access them.
> Check this article for an explanation of user instances:
> http://msdn.microsoft.com/sql/expres...xpuserinst.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Phill" <Phill@.discussions.microsoft.com> wrote in message
> news:1CF63F8E-902B-4AD7-A2F8-B7DA7233D7AE@.microsoft.com...
>
>
|||Use the upsizing Wizard in access to push the data into SSE
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F00DFB95-4D36-47CC-9A46-41A5A3577D60@.microsoft.com...[vbcol=seagreen]
> Thanks, I am now able to connect to the database. It didn't really do me
> any
> good though. I want to import an Access table into it but there aren't
> any
> tools to do so. No ODBC drivers, nothing. So how are we supposed to
> import
> data into SSE2K? Thanks.
> "Roger Wolter[MSFT]" wrote:

Friday, February 24, 2012

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 use the 'with move' statement

Sunday, February 19, 2012

Attempt to catch error using @@ERROR in SQL 2000 is failing

Monday, February 13, 2012

Attaching database

Attaching database

Attaching database

Thursday, February 9, 2012

Attach to SQL Server 2K5 Express Problem

Attach remote DB file

Blog Archive

About Me