How can I restore a database from a UNC?

It appears that Management Studio will not allow you to perform a restore from a UNC path. Is this true or am I missing the option to open the magic UNC door? I am very new to SQL2005 and this was buried in SQL2000, but it was possible to do in Enterprise Manager.

Have you tried using the raw TSQL query to start the restore, and not use the GUI Interface.

|||

Not to be snotty, but you didn't answer the question. Your point is taken, there are other ways to do this. I can use a script to restore from a UNC path. I am still interested in getting an answer to the question I posted, not to the underlying assumption that I am looking for any way to accomplish a restore from UNC.

As a DBA, I do many operations by script; however, there are times that, for a number of reasons, the UI is a better option. Since this was something that could be done in SQL2000 through the UI, I thought that perhaps Microsoft would also provide a means to do it through the UI in 2005. We have non-technical folks who need to do restores from different sources for demos. It is more practical to teach them how to use the UI to grab the backup they need for their laptop rather than scripting the numerous possibilities or having them alter a script.

So, if anyone knows if Management Studio can be used to do a UNC restore, please share with me how it can be done.

|||Hi,

this is sure possible: Select Restore Files and Filegroup --> Fome Device --> Add --> Enter a Full qualified name in the filename textbox like \\m2-jenss\SomeShare\Somebakfile.bak

and you are done.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||You nailed it. I initially tried that, but permissions foiled me, but I misread it as a limitation of the UI because the Selected Path pointed to a local drive and the field was disabled. Apparently, anything you put in File name will override the Selected Path entry if using the UNC path. Thanks a ton!

How can i Replace the Minus-Statement

Hi,

I've to translate this SQL-Statement from ORACLE to SQL-Server. But I'm missing the Minus-Statement on SQL-Server.

select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
minus
select tab_name from data_dic
)
minus
select tab_name, col_name from data_dic

what can I do to run it on SQL-Server.

Thanks in advance

Raimund

use whidbey's intersect function|||

Hi you didn′t specify wheter you are using 2k5 or 2k. In SQL Server you would use EXCEPT

select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
EXCEPT
select tab_name from data_dic
)
EXCEPT
select tab_name, col_name from data_dic

in 2k, you would use NOT IN, like you did in the above query:

select table_name, column_name from user_tab_columns
where table_name not in ( select table_name from user_tab_columns
WHERE table_name NOT IN
(
select tab_name from data_dic
)
)
WHERE table_name NOT IN
(
select tab_name, col_name from data_dic
)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks it works fine.

I already had it before but it didn't work. Probably there was something wrong in the Syntax.

Best Regards

Raimund

How can I find out who is running dangerous queries

I recently discovered that some one (or application) updated all the
rows in the database. Looks like a query was missing a where clause.
It was probably one of the applications, but reviews of the code show
that a where clauses is always used, or so it appears. Anyway, I set
up a trigger to capture every update to a particular table that
recorded who did what and when. I created the trigger (on insert and
update) on a table and in it I use the new fn_get_sql function that
comes with SQL Server 2000 SP3. It looks like this:

----------
CREATE TRIGGER Update_Last_Modified ON [dbo].[MYTABLENAME]
FOR UPDATE, INSERT
AS
BEGIN
SET NOCOUNT ON
DBCC TRACEON (2861)

DECLARE @.Qry nvarchar(4000)

DECLARE @.handle binary(20)

SELECT @.handle = sql_handle
FROM master..sysprocesses
WHERE spid = @.@.SPID

SET @.QRY = (SELECT CONVERT(nvarchar(4000), [text]) FROM
::fn_get_sql(@.handle))

UPDATE MYTABLENAME
SET DATE_LAST_MODIFIED = GETDATE(),
LAST_COMMAND = @.QRY,
LAST_USER = SYSTEM_USER
FROM inserted
WHERE MYTABLENAME.UID= Inserted.UID
END

-----------

It was previously coded to use DBCC INPUTBUFFER, and it worked fine,
but I was limited to the first 255 characters of the command, which
prevented me from seeing the critical parts, like the where clause!
When I modified the trigger to use fn_get_sql, all I ever see is the
entire text of the create trigger command. Maybe I should use an
entirely different approach. I'm open to ideas.

Thanks very much in advance for your help!

Miles

_________________Miles (milesfeinberg@.hotmail.com) writes:
> It was previously coded to use DBCC INPUTBUFFER, and it worked fine,
> but I was limited to the first 255 characters of the command, which
> prevented me from seeing the critical parts, like the where clause!
> When I modified the trigger to use fn_get_sql, all I ever see is the
> entire text of the create trigger command. Maybe I should use an
> entirely different approach. I'm open to ideas.

Yes, the idea with fn_get_sql is to get the currently executing statement
of a procedure. And for a process that introspects itself, the current
statement will be the statement it queries sysprocesses. So in your
case DBCC INPUTBUFFER is a better bet.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp