Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How do I create a backup of a SQL Server 2008 in a mirrored setup without any logs?

We need to do this so that when we restore our local development databases, we would have the most up to date data, but we don't necessarily have the space for all the transaction logs. (we're all using ssd's).

Currently, because it is mirrored, I can't create simple backups, it must be full backups.

share|improve this question

migrated from stackoverflow.com Aug 6 '12 at 20:14

This question came from our site for professional and enthusiast programmers.

1 Answer 1

Backing up a database (BACKUP DATABASE ...) backs up the pages from all of the database files (data and log) and upon restore those files will be recreated as a result.

A full database backup does just that: It backs up the database in its entirety. There is no way to get around not backing up the transaction log file. If you are in full recovery mode, then you should ensure you are doing the proper transaction log backups so the log doesn't grow out of control. Outside of that, you are constrained with a full backup. A database consists of data file(s) and log file(s).

A workaround to this could be just to generate scripts from you database. When you right-click your database to generate scripts, you can script out the entire database. There is an option to script out both Schema and Data. Then on your other machine/instance you can run this script. It's not a traditional "backup" by any means, but it should get you what you want and you'll just have a script file as a result. Mind you, when the CREATE DATABASE portion of the script runs, a log file will be created. You can't have an RDBMS database without a log file (or at least not on SQL Server that I know of).

share|improve this answer
    
I think the intent of the question was, is there a way to dump data-only out using the backup process, so you don't take the entire transaction log with you (for example, for dumps to local dev replicas from Production, where you could care less about past transaction logs). I'm wondering the same. Are you saying the Generate Scripts approach is the only way? This fails on any moderately-sized database as being too slow and scripts too long. –  Chris Moschini Jun 25 at 19:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.