Find out which tables and views recently added
Yesterday I added several tables and views. What query to run to get the tables and views recently created.
It should the ones created on 12/11/2017. Is it possible?
It should the ones created on 12/11/2017. Is it possible?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes it is possible , ask your DBA to create the linked server between the servers and after that we just need to write the simple queries.
ASKER
Can you provide a few details so I can pass your suggestion rationally? Thanks
To create a linked server -
USE master;
GO
EXEC sp_addlinkedserver
N'YourNewServeName',
N'SQL Server';
GO
then use like below
SELECT * FROM YourNewServeName.Database.
USE master;
GO
EXEC sp_addlinkedserver
N'YourNewServeName',
N'SQL Server';
GO
then use like below
SELECT * FROM YourNewServeName.Database.
ASKER
Just a question for myself. I was thinking of backing up the tables in a local db I would to create but in the process of creating a db
I got this error "CREATING DATABASE permission denied in database 'master'
How would I go around with this?
I got this error "CREATING DATABASE permission denied in database 'master'
How would I go around with this?
Go to SSMS...
right click on your database -> Tasks > Backup...
right click on your database -> Tasks > Backup...
I got this error "CREATING DATABASE permission denied in database 'master'If you can't create a database, ask a DBA to create it for you and add your user to the db_owner role in the new database so you can create objects in the database so you can copy the tables from the source database.
How would I go around with this?
>> The question that boggles my mind also is....
That is a very good question. If changes are being made in Students DB Saved, while you might get away with copying (on the new server) to Students DB, you will not be able to simply copy from old server to new server....
This is possibly another question in its own right.
You said : "Therefor there are tables and views for Students DB Saved that are not in Students DB."
Those changes must be scripted, so, each time you copy from production, you will then apply the scripts. Especially when the copy process is going to be repeated several times.
The additional challenge is Data, and if new data elements (rows) are being added, then it isnt a matter of copying DB's.
They need to be merged, or, a decision taken that the (assumed) development group should have their own data import routines to save and recover those new data elements.
It can (and often does) get very tricky and does need a lot of careful prep and planning and coordination.
Best to have a project meeting with your DBA and Dev (or whoever is on the new server) to make sure everyone is on the same page.
I wouldnt go creating DB's just yet, get the group together and plot it out first.
That is a very good question. If changes are being made in Students DB Saved, while you might get away with copying (on the new server) to Students DB, you will not be able to simply copy from old server to new server....
This is possibly another question in its own right.
You said : "Therefor there are tables and views for Students DB Saved that are not in Students DB."
Those changes must be scripted, so, each time you copy from production, you will then apply the scripts. Especially when the copy process is going to be repeated several times.
The additional challenge is Data, and if new data elements (rows) are being added, then it isnt a matter of copying DB's.
They need to be merged, or, a decision taken that the (assumed) development group should have their own data import routines to save and recover those new data elements.
It can (and often does) get very tricky and does need a lot of careful prep and planning and coordination.
Best to have a project meeting with your DBA and Dev (or whoever is on the new server) to make sure everyone is on the same page.
I wouldnt go creating DB's just yet, get the group together and plot it out first.
ASKER
Can you provide an example scripting which purpose is copy 3 tables from one server to another?
With a great deal of respect, not until you have had your meeting(s).
because the scripting I was talking about is a lot more than copying tables. It is metadata and (by the sounds of it) possibly data.
For example, what if some of the new things being added was making security more robust. So, for each student, there are new constructs and dependencies or constraints. Copying tables essentially overrides that. Not to mention what can happen with referential integrity.
But if you have all the change scripts, then you could run those scripts over the data and tables etc. I cannot write them for you - the people making the changes need to supply them.
It is very difficult when working over 2 disconnected databases when one of them is growing in a different direction. Think of a simple case when ID's have been added separately to each table. When then trying to import data, those ID's could create conflicts.
So, assuming you want to keep data in the new DB and just need to refresh occasionally, you are probably better off exporting data from production then running an import of that extract into the copy by updating existing data or importing new data.
To give you the correct answer to your question, we need to know more about your production and copy environment and how they need to relate (in a transition) and ultimately, to become the new production server. For exaample, are they developers on the new machine making changes ? If not, then who is making changes ?
And we dont really know what is happening on the new server or why the new server people are using the saved table.
That is also why I mentioned that this could become an (involved) question in its own right.
And depending on in-house expertise, you might end up with some new tools to help the process / automate some aspects (the tools dont cost much if anything) such as SSIS within SQL Server, or licensed tools like https://solutioncenter.apexsql.com/synchronize-sql-server-databases-in-different-remote-sources/
or https://sqldatabasestudio.com/ (look down the bottom for Database compare & synchronization)
Even still, you must have your internal procedures down pact.
It would be irresponsible of me to not point these things out to you and simply go for the points by writing a script. Hope you understand and can appreciate my concerns for your predicament.
because the scripting I was talking about is a lot more than copying tables. It is metadata and (by the sounds of it) possibly data.
For example, what if some of the new things being added was making security more robust. So, for each student, there are new constructs and dependencies or constraints. Copying tables essentially overrides that. Not to mention what can happen with referential integrity.
But if you have all the change scripts, then you could run those scripts over the data and tables etc. I cannot write them for you - the people making the changes need to supply them.
It is very difficult when working over 2 disconnected databases when one of them is growing in a different direction. Think of a simple case when ID's have been added separately to each table. When then trying to import data, those ID's could create conflicts.
So, assuming you want to keep data in the new DB and just need to refresh occasionally, you are probably better off exporting data from production then running an import of that extract into the copy by updating existing data or importing new data.
To give you the correct answer to your question, we need to know more about your production and copy environment and how they need to relate (in a transition) and ultimately, to become the new production server. For exaample, are they developers on the new machine making changes ? If not, then who is making changes ?
And we dont really know what is happening on the new server or why the new server people are using the saved table.
That is also why I mentioned that this could become an (involved) question in its own right.
And depending on in-house expertise, you might end up with some new tools to help the process / automate some aspects (the tools dont cost much if anything) such as SSIS within SQL Server, or licensed tools like https://solutioncenter.apexsql.com/synchronize-sql-server-databases-in-different-remote-sources/
or https://sqldatabasestudio.com/ (look down the bottom for Database compare & synchronization)
Even still, you must have your internal procedures down pact.
It would be irresponsible of me to not point these things out to you and simply go for the points by writing a script. Hope you understand and can appreciate my concerns for your predicament.
I do hope you check out those tools....
Wish you all the very best with your DB migration / server migration plans. Get those developers under control :)
Wish you all the very best with your DB migration / server migration plans. Get those developers under control :)
ASKER
Thanks!!
ASKER
The Situation.
Old Server New Server
Students DB >>> Students DB
Students DB_Saved
Students DB is copied to New Server. For some reason, we have been using Students DB Saved for a while.
Therefor there are tables and views for Students DB Saved that are not in Students DB.
Yesterday I copied the tables and views from Students DB_Saved to Students Db which will be the production in future.
This copy and replacement from Old server to New Server for Students Db can happen several times and therefore, I may need to recreate the copying of tables and views.
I need a script possibly to execute whenever a copy process is initiated from Old Server to New Server for the Students DB.
The question that boggles my mind also is. Is it possible for the DBA to just replace the tables for the Students DB that are in Old server that are also in Students DB in the new server and just leave the new ones in the to New Database so I don't have to repeatedly recreate them. Plus , those tables have data already. Should I script only table structure or should I import/export table script?