INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Need to restore to reporting server without log file/best way to create reporting server.

Need to restore to reporting server without log file/best way to create reporting server.

Need to restore to reporting server without log file/best way to create reporting server.

(OP)
Hello all.

I have a situation where the powers that be are using Red Gate software to replicate a production database onto a report server. The Red Gate app backs up the live database creating the files in one location, then restores while moving the files from that location to the report server. The problem is there is not enough space now on the report server to accommodate the log file. I know other ways of creating a report server but I was not part of the decision making process/team.

Now I believe this being a report server, it could as well be a read-only database. We don't need the transaction log here. I know you can reattach a DB without the transaction log to create a new one, but we cannot afford to detach the production database.

My question is, how do I with the current configuration restore the database without the transaction log, or create a new one during the restore? In the Red Gate error log I see the following command:

CODE --> T-SQL

RESTORE DATABASE [MyDBName] FROM DISK = 'B:\MyDB Backup\Dailybackup\*.sqb' SOURCE = 'Windopath' LATEST_FULL WITH MAILTO_ONERRORONLY = 'support@auroradx.com', RECOVERY, DISCONNECT_EXISTING, MOVE DATAFILES TO 'E:\MyDBName\databasefiles', MOVE LOGFILES TO 'E:\Windopath\logfiles', REPLACE, ORPHAN_CHECK, CHECKDB = 'NO_INFOMSGS, ALL_ERRORMSGS' 

Can I modify that command to exclude or recreate the log file?

Thank you very much for your prompt answers.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

RE: Need to restore to reporting server without log file/best way to create reporting server.

I don't know of a way to do what you want....but do you have enough room on the production server to restore the database there as a new name? If so, you could do this:

Backup the databaase MyDBName
Restore the database as MyDBName_backup
Detach MyDBName_backup
Copy the .mdf file to the report server
Attach it and rename it.

That might work.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Need to restore to reporting server without log file/best way to create reporting server.

(OP)
Thank you SQLBill! Not sure about the available space. Hopefully they grant my admin privileges, otherwise I can only advise. Then I will find out.

I thought about that solution but on the same server where the backup is being restore...of course, the problem remains the same.

The log file is currently 900 GB and the data file 500 GB. Initial sizes are 550GB and 900 GB; this is the REAL issue...The data file's initial size was bigger, and it was shrunk under my supervision. I am thinking that the same could be done with the log file: we could shrink it to about 25%-40% of the data file's size (switch to simple recovery model, shrink log file, switch back to full recovery model- I read backup with TRUNCATE_ONLY option is no longer available on SQL Server 2008) then let the Reg Gate SQL back up continue doing its job. Do you believe this is a good solution?

Thanks a lot for your insights.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

RE: Need to restore to reporting server without log file/best way to create reporting server.

Why is the log file so huge? Is it really free space? How often does the database get backed up? If it is only backed up once a week, the issue is that the log file can't be shrunk until a full backup or transaction log backup is done. Being that the log is larger than the data file, that is what appears to be happening.

If so, do a transaction log backup, then shrink the log file.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Need to restore to reporting server without log file/best way to create reporting server.

(OP)
I have requested admin access to the server. Once granted I will be able to answer your questions more accurately. However, from what I have seen so far, you are correct. There is a weekly maintenance plan that is run on Sunday night and that one seems to be the only one baking up and shrinking the transaction log. Then there is a nightly backup from which they recreate the reporting server's database. Looking at the Red Gate log, total file size is small on Mondays and grows throughout the week and that's when we run into the space issue. Waiting to talk to the CIO...Will post my findings.

Thank you so much.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

RE: Need to restore to reporting server without log file/best way to create reporting server.

(OP)
It is a mess! There is nightly full back up of the database and a Sunday maintenance job that reorganizes indices, updates statistics and cleans up history; it has not been successful the last three times it was executed. I need to come up with a better plan.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

RE: Need to restore to reporting server without log file/best way to create reporting server.

(OP)
I was granted all necessary privileges and here is how I fixed the problem:
-Backed up the transaction log without really backing it up, just to truncate it

CODE

BACKUP LOG MyDB TO DISK= 'NUL:' 
That had the effect of reducing the percentage used space from 98% to less than 1%. The file however remained at close to 1 TB in size.
-Switched recovery model to SIMPLE, shrunk the file, then switched back to FULL. This reduced the log file's initial size to 50 MB.
-Increased initial log file size to 100 GB, that's about 1/5 of the data file.

Red Gate's backup was successful. Now we are going to monitor the log file growth and take action proactively.

Thanks for your input.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

RE: Need to restore to reporting server without log file/best way to create reporting server.

Good luck on getting it all under control.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close