Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Few questions while performing a transfer of DB file over to another location?

Hi Experts,

I would like to transfer over a DB to someone.
1-What is the command to execute to make a bak of that file to a given destination?
2- Will that person be able to restore that db without having my login/pwd?
3- In order to create that backup I had to run the following (as all views were converted by the import utility as tables with the view's names..)
select 'sp_rename ''['+name+']'', '''+replace(name,'view_','')+''';'+ char(10)+CHAR(13)+ 'go' + char(13) from sys.tables where type='U';

Open in new window

However the I had to go to every single line and click enter before the GO, how can I avoid that?

Thanks in advance
Avatar of HainKurt
HainKurt
Flag of Canada image

I dont get why you click go :)

run this

select 'sp_rename ''['+name+']'', '''+replace(name,'view_','')+''';' from sys.tables where type='U'; 

Open in new window


copy all results and paste into a new editor and press F5

:)
Avatar of bfuchs

ASKER

Hi,

Without the GO I get the following
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sp_rename'.

Open in new window

Did you tried?

And what about the first ones..?

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
probably you have this syntax

begin
  ...
  sp_rename ....;
  ...
end;

Open in new window


in this case "exec" / "execute" is required...

begin
  ...
  exec sp_rename ....;
  ...
end;

Open in new window


if it is only the statement, or the first statement, you dont need exec/execute
Avatar of bfuchs

ASKER

@Eugene,

BACKUP DATABASE YourDB TO DISK='\\yourRemoteSever\SharedFolder\yoursqlDB.bak'
I get the following
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\BlueSky.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Open in new window

Also tried with E drive and got the same error.
Not sure if this refers to the local PC I have SSMS or to the server where the DB resides, In either case both should have access those drives mentioned.

@Hain,
I simply followed what you wrote..
copy all results and paste into a new editor and press F5

:)

Thanks,
Ben
Avatar of bfuchs

ASKER

Not sure why, but when I do that to a sub folder it works..
like 'E\Subfolder\MyDB.BAK.'

BTW,
If I execute again the command
BACKUP DATABASE YourDB TO DISK='\\yourRemoteSever\SharedFolder\yoursqlDB.bak

Open in new window

What will I have, 1 bak containing 2 files, something like a zip file?
and what if I want restore from such a file, will it ask me which one I need?

Thanks,
Ben
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

ASKER

Thank you experts!