February 9, 2015 at 10:48 am
Hi to all once again.
I have re-created a SQL job that ran ok on SQL 2005. When I run on SQL 2014 the restore step fails:
The file 'E:\Data\ftrow_fulltext_catalog.ndf' cannot be overwritten. It is being used by database....
Restore step:
RESTORE DATABASE DBName
FROM DISK = 'H:\Directory\DB.bak'
WITH REPLACE
The job backs up current production system then restores to a dev system ftrow_fulltext_catalog_dev.ndf'
Can I restore the DB without fulltext?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 9, 2015 at 11:02 am
WITH MOVE option appears to have done the trick.
RESTORE DATABASE Training
FROM DISK = 'H:\DB.bak'
WITH
MOVE 'File_Data' TO 'X:\DB.mdf',
MOVE 'ftrow_fulltext_catalog' TO 'X:\ftrow_fulltext_catalog_db.ndf',
MOVE 'File_Log' TO 'W:\DB_log.ldf',
RECOVERY, REPLACE, STATS = 10;
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 10, 2015 at 1:41 am
double check the file isn't used by an other database than the one you are trying to restore.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 10, 2015 at 4:15 am
Hi. I backup the production system lets call it 'Live.bak' to location x and call it DEV.bak I then restore DEV.Bak to the Dev System.
Steps in Job as follows:
Backup
BACKUP DATABASE Live
TO DISK = 'X:\DevSystemRestore\DEV.bak'
WITH INIT
Restore Step
RESTORE DATABASE Dev
FROM DISK = 'X:\DevSystemRestore\Dev.bak'
WITH
MOVE 'Release_Data' TO 'W:\Dev.mdf',
MOVE 'ftrow_fulltext_catalog' TO 'W:\ftrow_fulltext_catalog_Dev.ndf',
MOVE 'Release_Log' TO 'Y:\Dev_log.ldf',
RECOVERY, REPLACE;
Thanks for your time.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 10, 2015 at 5:21 am
I always put the target database offline ( with rollback immediate ) right before the actual restore statement, to be sure no one blocks my restore.
I have incorporated it all in a couple of Powershell functions.
my ITPRoceed2014 presentation on Onedrive
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 10, 2015 at 5:28 am
Thanks for the link. I have a step before the restore as follows:
-- Create the sql to kill the active database connections
DECLARE @execSql varchar(1000)
DECLARE @databaseName varchar(100)
-- Set the database name for which to kill the connections
SET @databaseName = 'Dev'
SET @execSql =
''SELECT @execSql = @execSql + 'kill ' + CONVERT(CHAR(10), spid) + ' '
from master.dbo.sysprocesses
WHERE db_name(dbid) = @databaseName AND status <> 'background' AND status IN ('runnable','sleeping') AND DBID <> 0 AND spid <> @@spid
EXEC (@execSql)
I created this job years ago and it did the job although not as elegant as your solution I am sure 🙂
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 10, 2015 at 5:58 am
No worries ! There are more ways to skin a cat 😉
Off course an "alter database [x] set offline with rollback immediate" is the most safe way.
Seems strange:
Or the db is still in use by non-killed spids
Or another database is actually using the file that is being mentioned
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 10, 2015 at 6:02 am
Thanks. I will review the way I do it.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply