I have a database, in SQL Server 2008, with only temporary data, but it has tables with a certain structure which I need. Should I back it up?
And if I should, should I make one copy of it or should I include it in the rest of the backup routine?
Sql-server – Should I backup a database with only temporary data
backupdatabase-designsql server
Related Solutions
I haven't used it before but there is an object in SSIS called Transfer SQL Server Objects. It has an option to copy data or not and you can select copy all objects or just specific types of objects, permissions etc.
You could also look into doing replication but blocking the data portion of it. In fact if you set up a snapshot replication that just pushed schema for each of your servers you could potentially have an automated process to do the push.
I think the SSIS option is probably your best bet though.
Can this be achieved with replication?
Yes, Transactional replication can be used in your case.
Can we get subsets of data from a subset of tables from the source, into our new replicated database?
Yes you can just replicate the tables (articles) that you want along with its subset of data.
e.g. where PersonTypeId = x --> You need to use static row filter as it uses a WHERE clause to select the appropriate data to be published.
selecting specific articles (tables) :
Filtering what data to publish :
Would it be something like creating a VIEW on the source, and replicating the results of that view, as a table in the replicated database?
No. See above answer to use static row filter when you are publishing the articles.
Related Question
- Is Temporary Table Good for Importing Temporary Data into Database?
- Sql-server – SQL Server – creating a historical data backup
- Sql-server – Copy of a database with an existing database with schema and data in sql server
- MySQL – How to Recover performance_schema After Restoring from Partial innobackupex Backup
- SQL Server – Backup/Restore During Database Migration While in Production
- Sql-server – How to implement a quick backup and restore strategy for database script testing
Best Answer
If there is a database with data on it, someone, at some point, will consider it important. So best approach would be run a full backup to avoid any hassle. Maybe weekly with couple of daily differentials. If there are frequent changes then maybe a daily full backup and one differential in the middle of the day.
If the data is temporary and can be recreated somehow, then I would configure the recovery model to SIMPLE and take a FULL backup daily. And to avoid spaces issues, if the data is not so important and can be recreated afterwards, I would add a process to empty all tables before running the backup. Then configure another step after backup process in order to add fresh data once again so anyone using the database has data to continue working.
And last, but not least, you could just script the database and recreate it from those scripts. Do you have your database code under source control? That would help a lot on this case. We have couple of databases with similar characteristics and we only save the recreation scripts, data can be easily recreated with couple of scripts.