April 2, 2002 at 8:51 am
I have 2 servers SQl server 7 and 2000.
I got a backup file from the customer for one of the database. So I created a new database using similar script as below
create database TESTUP
ON
(NAME = TESTUPDATA,
FILENAME='C:\PROGRAM FILES\MSSQL7\DATA\TESTUPDATA.dat',
SIZE=10GB)
LOG ON
(NAME = TESTUPLOG,
FILENAME='C:\PROGRAM FILES\MSSQL7\DATA\TESTUPLOG.dat',
MAXSIZE=500MB)
GO
sp_addlogin TESTUP, TESTUP, TESTUP
GO
USE TESTUP
GO
sp_adduser TESTUP
GO
When I use enterprise manager to force restore that backup copy to this database I just created, even though I have setup maxsize for LOG as 500 MB it still gets all their logs 50 GB !!! I do not want their logs in the 1st place and then I tried to restrict them I could not do so.
So I restored it as it was urgent and then tried to shrink the datafiles for LOGS but they did not shrunk much.
Is their any way to avoid getting the transaction logs during the restore ???
In other words can just get the datafiles and not the transaction logs ?
Thnaks
Sonali
April 2, 2002 at 9:55 am
If there is only one log file, you can delete it and only restore the database file. The system should create a new log file for you.
Steve Jones
April 3, 2002 at 6:28 am
You cannot resotre just the database without the log file. It gives the error not found. You can however restore the database and log and use sp_detach_db to remove it then delete the log file and use sp_attach_single_file_db to reattach just the database as log as there is only one log file (it will rebuild). See BOL for details on these procedures. If you will be doing this often then look at shrinking you log file on the original database unless you have a reason not to.
Also the reason why creating with a maxsize of 500mb does not work is the databases settings update to reflect the restored versions settings.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 3, 2002 at 6:34 am
This hasn't been released yet, but discusses the situation where there are two log files and one is missing/deleted.
http://www.sqlservercentral.com/columnists/awarren/attachanddetachagain.asp
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply