I would like to copy a database (tables, it’s data, stored procs & views) from SQL Server 2008 database to SQL Server Express. Is it possible? If so, would you please let me know how to do it? When I tried, it is giving some error.
Please note that I have only few records in SQL Server 2008 database tables.
Thanks and Regards.. Shruthi Keerthi..
If copying the entire database, I usually find it easiest to stop the source service, copy the *.mdf file to the appropriate new data directory, and just mount the MDF in the new service. As long as there aren't any changes in the setup (like FILESHARE), then this is the easiest way to clone a database.
And yes, this is possible. I just did this earlier today.
You can create a backup of the database and them restore that backup into the Express instance.
Another option, since you said you didn't have very many records in the DB, is to generate the SQL statements (right click on the database --> tasks -> generate scripts) and then run that on the Express instance. Then just copy over the tuples you want.
I prefer to use Backup/Restore when moving a database from one server to another. As long as your SQL Server and SQL Express are the same version, or the one you are moving to is newer than the one you are moving from (SQL 2005 to SQL Express 2008), it should work without a hitch.
You could also try using the Microsoft Database Publishing Wizard.
It's simple to use and will create a SQL script that you can run to rebuild tables (including data), stored procs, views, etc...
You can download it here:
The quickest way is to "detach" the source database from its server, copy the (2) files to a new name/location and (re)attach each database to the appropriate server. Attach/detach is available as a command or via the SQL Visual Studio.