May 9, 2012 at 9:41 am
Hello
I have to automate backup-restore from production environment to Day behind server.
I schedule and it works fine for 2 months
here is my sql statement
declare @filenametbl1 varchar(60), @thepath1 nvarchar(150)
set @filenametbl1 = (
SELECT top 1 rtrim(ltrim(REVERSE(SUBSTRING(REVERSE(T2.physical_device_name),0,(charindex('\',REVERSE(T2.physical_device_name))))))) [filename]
FROM RTIDBPROD.msdb.dbo.backupset T1 JOIN RTIDBPROD.msdb.dbo.backupmediafamily T2 ON
T1.media_set_id = T2.media_set_id
where T1.[type] = 'D' and T1.database_name = 'STARS'
order by T1.backup_finish_date desc
)
set @thepath1 = '\\data-store-srv\rtibackups\'+@filenametbl1+''
select @thepath1
RESTORE DATABASE stars
FROM DISK = @thepath1
WITH MOVE 'Stars_Data' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\STARS_Data.mdf',
MOVE 'Stars_Log' TO 'C:\Program Files\Microsoft SQL Server\STARS_Log.ldf'
, REPLACE
but right now. In Production databases, Log files used to be 60-100 MB before but right now it increases upto 14 GB.
so I try that command above manually and I got an error like, there is insufficent space in C drive (There is 10.3 GB Free space and current log file is 60 MB)
here is error log
Msg 3257, Level 16, State 1, Line 13
There is insufficient free space on disk volume 'C:\' to create the database. The database requires 13877903360 additional free bytes, while only 11076681728 bytes are available.
Msg 3119, Level 16, State 4, Line 13
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 13
RESTORE DATABASE is terminating abnormally.
and the error log from SQL job is
Executed as user: PPSCENTRAL\mrcluster. Cannot open backup device '\\data-store-srv\rtibackups\'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.). [SQLSTATE 42000] (Error 3201) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
so I am Thinking ,
If i shrink Log file from Production database and it come down till to 100 MB. then i took backup and restore on Day behind.
is that solution of my problem or any other way i can do.
please help me for this problem
May 9, 2012 at 10:01 am
No real solution other than adding space. When restoring a database, there must always be enough space for the files.
p.s. Why did the log get to 14GB in the first place?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2012 at 10:05 am
No real solution other than adding space. When restoring a database, there must always be enough space for the files.
p.s. Why did the log get to 14GB in the first place?
i get some info.
there were many truncation happen in last week.
so i am guessing, thats why it grow upto 14 GB
but if i shrink log file, then it should work right?
and one more question ,
is it ok to take backup in working time, means it don't heart any process or user while i am taking 100 GB backup for that database on Production.
May 9, 2012 at 12:39 pm
surma.sql (5/9/2012)
but if i shrink log file, then it should work right?
Yes, but you'll need to shrink the log on the source system, take a fresh backup and then restore that.
surma.sql (5/9/2012)
is it ok to take backup in working time, means it don't heart any process or user while i am taking 100 GB backup for that database on Production.
You can take a backup without having to offline the database, bear in mind that for a large database there will be some extra work for the server to do
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 9, 2012 at 12:49 pm
There will be an impact of backup on production system. try to take backup in non peak time or take a strip backup.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply