sql server 2008: should i be running differential or transactional logs or both HELP!
i have two databases:
this one was set up by a third party and they do not allow us to change the database to full recovery. so it is my understanding that if a database is in simple mode, then the only option for backup is the FULL BACKUP? we need to be able to recover it in 15 min increments. would this be possible without doing a full backup every 15min?
the other database we have full control over and we would like to be able to recover in 15min intervals. how do i set this up in sql server 2008? should we be doing transactional or differential or both?
how do i change a DB from simple to full recovery mode?
so it is my understanding that if a database is in simple mode, then the only option for backup is the FULL BACKUP?
Yes.
we need to be able to recover it in 15 min increments. would this be possible without doing a full backup every 15min?
Ah - yes. That said, it is simply not practical.
What setup is that? It makes NO sense to not allow this change. Like zero. It is actually gross negelct, totally voiding any way to make backups. I never heard of THAT requirement. THere are a lot of bad requirements out, but not allowing the transaction log to work is something I never heard of.
how do i set this up in sql server 2008? should we be doing transactional or differential or both?
Ever thought of transaction log backups? There is a third option. Basically, you can do full backup weekly, differential daily, tx log every 15 minutes and use log file hipping to move stuff off your database RIGHT NOW (in near real time) automatically.
3.how do i change a DB from simple to full recovery mode?
THIS is a RTFM question. Check the properties of the database, there you will find the setting. Change it, press ok, done.
To expand on the answers provided in your other question ( basics of SQL Server 2008 backups):
If you need 15-minute recovery and are not allowed to change to Full recovery mode, then you'd need to take Full or Differential backups every 15 minutes, which is pretty insane. It doesn't make sense that you'd be required to have 15 minute recovery, but couldn't use transaction log backups. Whoever is making that policy needs to be removed from their position.
As discussed in your other question, for such a database you'd want to perform something like weekly full and daily differential backups on the database. Then, you'd create a SQL Maintenance Plan to perform transaction log backups every fifteen minutes. The three backup types (full, differential, transaction log) are designed to be used in combination, not either/or.
In the SSMS GUI, you can change it by opening the Properties dialog for a database and going to the Options section. In T-SQL, you would do:
ALTER DATABASE my_database_name SET RECOVERY FULLGO
As a takeaway from this and your other question, someone on your team needs to get some documentation, take some training, etc and learn the basics of SQL Server if you're going to be using it in production.