making back up copy of sql server tables to include data and all associated table objects
Hi,
I’ve made some changes to some SQL Server objects, tables,views, stored procedures, functions etc in my test environment which I now want to move to the production environment.
I want to make backups of the existing objects in production, eg Production_Table_1 as Production_Table_1_backUp.
I’m looking for the easiest way to make a copy of the table in its existing database, and the copy to include all associated indexes,constraints, data etc…
I looked at Transfer SQL Server Object Tasks in SSIS but it doesn’t seem to allow altering the object name.
I’m now looking at using the “Script Table as” option in Management studio but it doesn’t seem to copy indexes.
Any guidance appreciated.
I’ve made some changes to some SQL Server objects, tables,views, stored procedures, functions etc in my test environment which I now want to move to the production environment.
I want to make backups of the existing objects in production, eg Production_Table_1 as Production_Table_1_backUp.
I’m looking for the easiest way to make a copy of the table in its existing database, and the copy to include all associated indexes,constraints, data etc…
I looked at Transfer SQL Server Object Tasks in SSIS but it doesn’t seem to allow altering the object name.
I’m now looking at using the “Script Table as” option in Management studio but it doesn’t seem to copy indexes.
Any guidance appreciated.
ASKER
thank you for that Icohan, as I am only changing a few objects I thought of backing them up individually, but this makes sense too, will look into this and get back to you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The Copy Database wizard is not allowing me to create user tables....it creates system tables, system views etc., but no user tables or user views.
it writes to a log...but i cannot access the log file as it is on a server that i do not have access to
it writes to a log...but i cannot access the log file as it is on a server that i do not have access to
ASKER
I have only dbo access, this does not allow me to use the copy database wizard fully I believe
in that case your options are to ask for elevated permissions or ask for someone that has that to COPY DATABASE for you or go back to and re-do the SSIS and please see here how you can select what needs to be copied from source to target for each table as it is more than just the indexes you want to bring accross - PKeys, FKeys, Constraints, defaults, ETC...
http://www.kodyaz.com/articles/transfer-sql-server-objects-task.aspx
http://www.kodyaz.com/articles/transfer-sql-server-objects-task.aspx
ASKER
Hi Icohan, I backed up the objects individually as this proved to be the quickest under the circumstances....thanks for your input
https://docs.microsoft.com/en-us/sql/relational-databases/databases/use-the-copy-database-wizard?view=sql-server-2014