May 9, 2012 at 9:39 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)
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 11, 2012 at 8:41 pm
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)
you mentioned your log size two times above with different value ..confusing for me :w00t:
another thing you should have almost same or above space as the size of targeted database. like if you have 10 gb of database , space should be 10 gb approx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 12, 2012 at 4:30 am
In Production databases, Log files used to be 60-100 MB before but right now it increases upto 14 GB.
You should worry on Log File size than Backup Size. Adding some disk space on your server would mask the issue for time being.
May 14, 2012 at 5:48 am
Is that database in Full Recovery mode? If so, do you have regularly scheduled log backups (regular = at least one per hour, but more is better)? If not, that would explain why the log files are increasing so radically.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 17, 2012 at 4:20 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply