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

[TheBugSlayer]

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:

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', [b][COLOR=red]MOVE LOGFILES TO 'E:\Windopath\logfiles'[/color][/b], 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)

 

[SQLBill]

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

 

[TheBugSlayer]

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)

 

[SQLBill]

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

 

[TheBugSlayer]

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)

 

[TheBugSlayer]

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)

 

[TheBugSlayer]

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

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)

 

[SQLBill]

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