SQL Server : copy database data and structure including aspnet security to blank database

Question

I have to transfer the data and structure of a SQL Server database to an already created empty database.

I have 4 tables with auto-increment keys, where 3 tables are sub tables of the master. The database also has the ASP.NET membership tables, which I need to copy across as has user data.

Any ideas, I have tried usually SQL Server 2012 Management Studio, but won't keep the auto inc keys and ASP.NET membership tables.

you can take backup of DB and restore on empty DB. Follow msdn.microsoft.com/en-us/library/ms187510.aspx and msdn.microsoft.com/en-us/library/ms177429.aspx — HaveNoDisplayName
– HaveNoDisplayName, Commented Jan 11, 2015 at 20:30
It wont let me do it as its via a website panel, and i cannot use the same db name. maybe iam doing it wrong.? — Mark
– Mark, Commented Jan 11, 2015 at 20:35
Then make your question Clear, what and where you want? and let us know what you tried till now, what problem you getting — HaveNoDisplayName
– HaveNoDisplayName, Commented Jan 11, 2015 at 20:36
Ok. bit more info. I have successfully created a backup of the database in question using sql management studio to my local drive folder. On the destination server i dont have the option to name the db as the same name. Due to the providers auto naming prefix which it attaches. When i try and restore the db to this newly created db, i use vs man studio, select the file location of the backup in the device option and choose the new database database in the destination folder. The verify backup media works ok but the restore fails. I can restore it to its original name. Any ideas — Mark
– Mark, Commented Jan 11, 2015 at 21:48
Ok. changed the filename and overite option and now working. — Mark
– Mark, Commented Jan 11, 2015 at 21:54

M.Ali · Accepted Answer · 2015-01-11 21:23:01Z

1

You have two simple options

backup and Restore database on the target server.
But since you have mentioned you want to copy the data and schema from one database to another empty(existing) database, I would suggest SQL Server Scripting would be a better option for you. You can scripts sql-server objects and data (just data or just schema) and execute these scripts on the target database.

answered Jan 11, 2015 at 21:23

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : copy database data and structure including aspnet security to blank database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related