database move

Question

database move

bruce_reid

SSCrazy

Points: 2419
More actions
October 10, 2003 at 2:08 pm

#81233

I have a database on my Pc.I wanted to move this database onto a production server(tables,veiws,sps,udfs and users and logins).Which is the best way to do this.
Thanks in advance.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

kleb_writes Old Hand Points: 337 More actions · Answer 1

If your have Enterprise edt. and have access to Enterprise mgr, then make a back up file (.BAK) transfer the file to the new server and restore database from the device, click add and then you browse out to the file location to load the device. You'll need to go to the options tab and edit the "move to physical file name" file path and name for both the log file and the mdf to be what the should be on the new server machine.

Hope that helps.

-kel

K Leb

gljjr SSChampion Points: 10080 More actions · Answer 2

In addition to the above you will want to script out the users/logins and move them manually first. Then once the database has been restored I would double check to make sure the users have been mapped correctly.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

I think the Copy Database Wizard does quite an acceptable job. You can invoke him by right-clicking on the target server in EM ->All tasks->Copy Database wizard.

The wizard will also take care of logins, role, permissions.

However, one pitfall I've noticed with the wizard is when it comes to Full Text Catalogs. It failed to copy these correctly. If you have full text catalogs I would manually built them anew on the target server.

Could save you a lot of trouble.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

bruce_reid SSCrazy Points: 2419 More actions · Answer 4

It helps me in moving to destination server but its not available on source server,But i need that database on source server too.How can i do that.

Thanks.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

On one of the wizard pages there is a check box where you can specify if want to copy or to move the db. Check the copy option. Maybe you have to increase that column width to see this option.

Frank

http://www.insidesql.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]