I am not a DBA, somewhat forced into the role of pseudo-DBA. My situation
is this - I have a production database on a server. Occasionally, I need to
have an exact duplicate of this production database on my laptop. What I do
in this case is stop the SQLSERVER service, make a physical copy of the mdf
and log files, then copy those files over to my laptop and attach database.
Subsequent to that, I just transparently replace the folder without
attaching, and usually the server is none the wiser and everything works
fine.
However, a recurring problem I have is that logins and users don't seem to
match up with each other. I have a login 'test' and a user within my
database 'test'. Sometimes when I do this manual copy, it works without a
hitch. Other times, it tells me that access is denied for 'test' user. I
go into the Security folder on EM and open the login and sure enough, it
doesn't have my database checked under the "Database Access" tab. When I
check the database to give the login access, it gives me an "Error 21002:
[SQL-DMO]User 'test' already exists.
So two questions:
First, how do I resolve this specific problem with logins and users not
matching up short of deleting them and recreating them and having to
rescript the permissions from my server to my laptop?
Second, I'm sure the way I'm copying the files manually, having to stop the
server and all is a pretty kludgy way of doing it. Is there a better way to
duplicate a database from my server to my laptop which won't incur this
login/user communication problem (not including replication)?(1) If the logins are SQL Server logins, you should be able to use =sp_change_users_login. You can read up on it within Books Online.
(2) The Transact-SQL commands BACKUP and RESTORE allow you to backup a =database to a file and then restore the database from that file without =shutting down the server. There is much documentation on these topics =within Books Online as well.
-- Keith
"James" <capricorn@.nospam.com> wrote in message =news:%23S7onQCjDHA.2544@.TK2MSFTNGP11.phx.gbl...
> I am not a DBA, somewhat forced into the role of pseudo-DBA. My =situation
> is this - I have a production database on a server. Occasionally, I =need to
> have an exact duplicate of this production database on my laptop. =What I do
> in this case is stop the SQLSERVER service, make a physical copy of =the mdf
> and log files, then copy those files over to my laptop and attach =database.
> Subsequent to that, I just transparently replace the folder without
> attaching, and usually the server is none the wiser and everything =works
> fine.
> > However, a recurring problem I have is that logins and users don't =seem to
> match up with each other. I have a login 'test' and a user within my
> database 'test'. Sometimes when I do this manual copy, it works =without a
> hitch. Other times, it tells me that access is denied for 'test' =user. I
> go into the Security folder on EM and open the login and sure enough, =it
> doesn't have my database checked under the "Database Access" tab. =When I
> check the database to give the login access, it gives me an "Error =21002:
> [SQL-DMO]User 'test' already exists.
> > So two questions:
> First, how do I resolve this specific problem with logins and users =not
> matching up short of deleting them and recreating them and having to
> rescript the permissions from my server to my laptop?
> > Second, I'm sure the way I'm copying the files manually, having to =stop the
> server and all is a pretty kludgy way of doing it. Is there a better =way to
> duplicate a database from my server to my laptop which won't incur =this
> login/user communication problem (not including replication)?
> > >|||Keith,
Thanks for pointing me in the right direction. I happen to have figured out
issue 1 just after I posted the question. sp_change_users_login
'Update_One', 'test', 'test' did the trick.
I am also aware of backup and restore, but I haven't quite figured out how
to restore from a network drive location even after looking through the
books online. It says to use DISK if the location is a redirected drive,
but I keep getting a Device Offline error. I also can't figure out the
syntax for backing up to a remote folder as I can't get it to add the
device, or at least it won't recognize it. I try using paths such as
'\\servername\d$\backup' but it doesn't see these. Can you suggest a sample
syntax for how I can backup and restore to network locations? Do I have to
map the drives before I can use them as devices?
Thanks,
James
"Keith Kratochvil" <sqlguy@.comcast.net> wrote in message
news:%23S1hPXCjDHA.1940@.TK2MSFTNGP09.phx.gbl...
(1) If the logins are SQL Server logins, you should be able to use
sp_change_users_login. You can read up on it within Books Online.
(2) The Transact-SQL commands BACKUP and RESTORE allow you to backup a
database to a file and then restore the database from that file without
shutting down the server. There is much documentation on these topics
within Books Online as well.
--
Keith
"James" <capricorn@.nospam.com> wrote in message
news:%23S7onQCjDHA.2544@.TK2MSFTNGP11.phx.gbl...
> I am not a DBA, somewhat forced into the role of pseudo-DBA. My situation
> is this - I have a production database on a server. Occasionally, I need
to
> have an exact duplicate of this production database on my laptop. What I
do
> in this case is stop the SQLSERVER service, make a physical copy of the
mdf
> and log files, then copy those files over to my laptop and attach
database.
> Subsequent to that, I just transparently replace the folder without
> attaching, and usually the server is none the wiser and everything works
> fine.
> However, a recurring problem I have is that logins and users don't seem to
> match up with each other. I have a login 'test' and a user within my
> database 'test'. Sometimes when I do this manual copy, it works without a
> hitch. Other times, it tells me that access is denied for 'test' user. I
> go into the Security folder on EM and open the login and sure enough, it
> doesn't have my database checked under the "Database Access" tab. When I
> check the database to give the login access, it gives me an "Error 21002:
> [SQL-DMO]User 'test' already exists.
> So two questions:
> First, how do I resolve this specific problem with logins and users not
> matching up short of deleting them and recreating them and having to
> rescript the permissions from my server to my laptop?
> Second, I'm sure the way I'm copying the files manually, having to stop
the
> server and all is a pretty kludgy way of doing it. Is there a better way
to
> duplicate a database from my server to my laptop which won't incur this
> login/user communication problem (not including replication)?
>
>|||Hi James,
Thanks for Keith's help. To backup up a database to a remote folder and
restore it, please try to perform the following article.
1. Share a folder on the remote machine.
2. Perform these codes using Query Analyzer on your original instance:
-- Create a logical backup device for the full EXCHANGETEST1 backup.
EXEC sp_addumpdevice 'disk', 'EXCHANGETEST1',
'\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
-- Back up the full EXCHANGETEXT1 database.
BACKUP DATABASE EXCHANGETEST1 TO EXCHANGETEST1
3. Perform these codes using Query Analyzer on the remote instance to
restore the database EXCHANGETEST1.
--Restore the EXCHANGETEXT1 database.
RESTORE DATABASE EXCHANGETEST1 FROM
DISK='\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
WITH RECOVERY,
MOVE 'exchangetest1_data'TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\exchangetest1_data.mdf',
MOVE 'exchangetest1_log'TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\exchangetest1_log.ldf'
It works on my side. Does it work on your machine? Please feel free to let
me know if this solves your problem or if you would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||It is important to note that the account that SQL Server (and SQL Server =Agent) runs under has permissions to the share. If the account has the =appropriate permissions, it is also possible to backup to an admin share =(example: \\Machine\c$)
You can backup directly to the share/admin share with the backup command =(without adding the backup device):
BACKUP DATABASE master TO DISK =3D '\\SomeOtherMachine\c$\master.bak' =WITH INIT
-- Keith
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message =news:wrUppOLjDHA.1544@.cpmsftngxa06.phx.gbl...
> Hi James,
> > Thanks for Keith's help. To backup up a database to a remote folder =and > restore it, please try to perform the following article. > > 1. Share a folder on the remote machine.
> > 2. Perform these codes using Query Analyzer on your original instance:
> > -- Create a logical backup device for the full EXCHANGETEST1 backup.
> > EXEC sp_addumpdevice 'disk', 'EXCHANGETEST1', > '\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
> > -- Back up the full EXCHANGETEXT1 database.
> BACKUP DATABASE EXCHANGETEST1 TO EXCHANGETEST1
> > 3. Perform these codes using Query Analyzer on the remote instance to > restore the database EXCHANGETEST1.
> > --Restore the EXCHANGETEXT1 database.
> > RESTORE DATABASE EXCHANGETEST1 FROM > DISK=3D'\\<RemoteMachineName>\<ShareFolderName>\EXCHANGETEST1.dat'
> WITH RECOVERY,
> MOVE 'exchangetest1_data'TO 'c:\Program Files\Microsoft SQL > Server\MSSQL\Data\exchangetest1_data.mdf', > MOVE 'exchangetest1_log'TO 'c:\Program Files\Microsoft SQL > Server\MSSQL\Data\exchangetest1_log.ldf'
> > It works on my side. Does it work on your machine? Please feel free to =let > me know if this solves your problem or if you would like further =assistance.
> > Regards, > > Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no =rights.
>
