I have been using the Import Wizard every month end to do a snapshot of a vendor's database. I save the script into a SSIS package (dtsx). All has been good. I think it saves the schema, tables, indexes, etc...
Is it possible to copy an entire vendor's database like above, by using TSQL? At the very MINIMUM, will it pickup on (schema, tables, indexes, etc...? Should I use sp_MSForEachTable?
Thanks
Why not create backups?
Because it is a vendor database and we only have read-only.
Doable but can be messy.
You night need to do it using dynamic sql.
How will you handle deltas in schema etc? Will you be doing this daily? What is the purpose of you doing this?
yes it possible ... hope the below links helps 
https://www.mssqltips.com/sqlservertutorial/20/sql-server-backup-database-command/
Read-Only is just a setting than someone with sysadmin privs can easily change. The big question is... are you violating a contract or license by copying anything of that database? There could be some serious legal ramifications here.
Hi Jeff,
The answer to your question is "no". It is a report database from the vendor and a while back, they gave us an SSIS package to pull all tables, schemas, indexes, etc... into our internal servers so that we don't constantly query against their servers.
There are a few reasons why we are not using their SSIS package. They don't keep their package updated to their database changes. Their generic package does not apply to our business needs. Also, their database constantly update the data while we need to capture the changes daily to do monthly and weekly reports.
I would ask them to give you a daily data dump of the pertinent data you need for your specific reports.
Cause irregardless of schema changes the data should be pretty much the same at the core of it?
Why should you create a very complex yet fragile data transfer ecosystem that can easily break when you could just get a dump of 4 tab delimited files that have all you will need for reporting purposes
I agree but at this point, anything that they provide to us is a very scary thought. The three items (SSIS package, reports, views) that they have provided to us are constantly flops, with hidden data integrity concerns. It takes them weeks to isolate the issue and more weeks when they reply. Even their report database is missing some key daily data, poor syncing from their application database which we only found out when it was compared to another data source. We have been pounding on them and all we hear is "Sorry, we will correct it". The worst vendor ever! I rather talk to a monkey than to ask for another painful solution from them. Sorry if you can hear my sarcasm in my writing.
Then you should be able to easily make a copy of the database using BACKUP and RESTORE. Whether or not it's Read_Only is entirely up to you because anyone with sysadmin privs can easily change it all.
Jeff,
The vendor provided us with a SQL Authentication account, connecting to their data lake. I thought that in order to do a BACKUP, we would have to have access to their server where the BAK file saves to it. We have access to their database but not the server Jeff. Is it possible to do a BAK to our server?
We have been looking into this software:
https://sqlbackupandftp.com/
You need to reconsider your relationship with this vendor.
None of the best tools in the market can fix this issue unless they change their internal processes and behaviour. Or unless you accept that is just the way things are.
From what you previously said, I didn't realize that the database was on the vendor's hardware at their site. I thought it was a vendor DB on your site.
That being said, I totally agree with what @yosiasz stated. No tool in the world can help here if you don't have a great relationship with the vendor.
Unfortunately, I am not the decision maker to hire this vendor. Their ERP system has been integrated into our business years before I arrived here. I am sure the vendor has good intentions but they don't always hire/retain the best people.
So that leaves me at a disadvantage and try to improvise if possible.
As long as management is ok with "good intentions " dont gild the lily or Kintsugi