bfuchs
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..)
Thanks in advance
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';
However the I had to go to every single line and click enter before the GO, how can I avoid that?Thanks in advance
ASKER
Hi,
Without the GO I get the following
And what about the first ones..?
Thanks,
Ben
Without the GO I get the following
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sp_rename'.
Did you tried?And what about the first ones..?
Thanks,
Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
probably you have this syntax
in this case "exec" / "execute" is required...
if it is only the statement, or the first statement, you dont need exec/execute
begin
...
sp_rename ....;
...
end;
in this case "exec" / "execute" is required...
begin
...
exec sp_rename ....;
...
end;
if it is only the statement, or the first statement, you dont need exec/execute
ASKER
@Eugene,
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..
Thanks,
Ben
BACKUP DATABASE YourDB TO DISK='\\yourRemoteSever\ShI get the followingaredFolder \yoursqlDB .bak'
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.
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
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
and what if I want restore from such a file, will it ask me which one I need?
Thanks,
Ben
like 'E\Subfolder\MyDB.BAK.'
BTW,
If I execute again the command
BACKUP DATABASE YourDB TO DISK='\\yourRemoteSever\SharedFolder\yoursqlDB.bak
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you experts!
run this
Open in new window
copy all results and paste into a new editor and press F5
:)