Copy ENTIRE database including table contents and user ids

Ron Howerton · March 26th, 2015, 12:56 PM

Perhaps I'm just not searching using the right keywords, but is there really no SIMPLE way to copy an entire SQL Server database from one server to another, including:

ALL tables
- Primary keys, indices, identity specifications and any other settings not specifically listed here but whose oimission cause problems
- Complete table contents
ALL views created as actual VIEWS instead of changing them to tables (Why would I ever want a view converted to a table, MS?)
ALL Stored Procedures
ALL Functions
ALL Users
- At the server level if they do not already exist there
- Then foir the database with all the same permissions granted automatically
And any other database objects or features not explicitly listed here but which I might need in order to reproduce some problem in production in our test environment?

rstelma · March 26th, 2015, 02:57 PM

Full backup should have all of that

Ron Howerton · March 26th, 2015, 03:41 PM

Not an option.

Any other suggestions?

mat41 · March 26th, 2015, 07:22 PM

I have been told this before. No you cant create a .bak file, even if you could you cant restore it...... This is the easiest option for you. Someone has this access and can do it. It took me a few weeks but I did manage to email the right people and get it done in the end.

No other option I am aware of. Whoever has given you this requirement with such restrictions has unrealistic business expectations.

I use SQLServer TableDiff to move data from one instance to another. You do have this option available on 2008 but it will not move everything you need moved.

Ron Howerton · March 27th, 2015, 08:12 AM

That's what I thought.

Thanks!

RacerBoy · March 27th, 2015, 10:33 AM

Still looking for solution?

Ron Howerton · March 27th, 2015, 10:39 AM

What do you suggest?

rstelma · March 28th, 2015, 03:30 PM

I believe you can also detach the database and attach it in a different location.

Why can't you make a backup of the database?

mat41 · March 29th, 2015, 06:46 PM

IMO detach and attach is no where near as good as taking a backup. Also i doubt your ability to detach a database given your limited permissions. Base don your post I assume permissions is why you cant create a backup...

rstelma · March 30th, 2015, 01:12 AM

It works though, doesn't it? I haven't seen any difference myself.

If you don't have permission to back up the database maybe you should leave it alone.