Hi,
I am planning to automate a nighty restore of a DB on another server
can someone point me in the right direction with the SQL script to
modify the logical file names to the correct path and not the ones
carried over with the DB??
i.e the database is to be renamed on the new server
any help much appreciated
Many thanks in advance 14 36816
blueboy wrote:
Hi,
I am planning to automate a nighty restore of a DB on another server
can someone point me in the right direction with the SQL script to
modify the logical file names to the correct path and not the ones
carried over with the DB??
i.e the database is to be renamed on the new server
any help much appreciated
Many thanks in advance
This should point you in the right direction: http://support.microsoft.com/default...b;en-us;314546
If you restore the database to a different file location than the source
database, you must specify the WITH MOVE option. For example, on the
source server the database is in the D:\Mssql\Data folder. The
destination server does not have a D drive, and you want to restore the
database to the C:\Mssql\Data folder.
Good luck
Many thanks for that it seems to be what i was after however i keep
getting an error -
The job failed. The Job was invoked by User domainname\user. The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).
here is the scripting i have;
kill connections -
ALTER DATABASE {db name} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Restore -
RESTORE DATABASE {db name}
FROM DISK =
'E:\folde\{db name} .bak'
WITH MOVE 'Logical_Name_Data' TO 'G:\SQLDATA\MSSQL\data\{db name}
_Data.MDF',
MOVE 'Logical_Data_Log' TO 'G:\SQLDATA\MSSQL\data\{db name}
_Log.LDF',
STATS = 1, REPLACE
GO
It stops at step 2 i also notice when i go back into the steps they
are defaulting back to the master database??
Any help much appreciated
blueboy (ma********@hotmail.com) writes:
Many thanks for that it seems to be what i was after however i keep
getting an error -
The job failed. The Job was invoked by User domainname\user. The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).
Did you look under Job history to see what failed? Up to the right
(in Enterprise Manager in SQL 2000), there is a checkbox which says "View
step history". There should be an error message.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
yes the error is
The job failed. The Job was invoked by User domainname\user. The
last step to run was step 2 (Restore). The job was requested to start
at step 1 (Kill connections).
cheers
Arrr Apologies
here is the info
Executed as user: User domainname\user. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.
Step one runs fine which disconnects users so not sure what the prob
is? any ideas??
Many thanks
"blueboy" <ma********@hotmail.comwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...
Arrr Apologies
here is the info
Executed as user: User domainname\user. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.
Step one runs fine which disconnects users so not sure what the prob
is? any ideas??
Are you sure the job isn't trying to run while in that DB?
Many thanks
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Sorry what do you mean by
Are you sure the job isn't trying to run while in that DB?
appologies for sounding daft its been a long day!!
cheers
blueboy (ma********@hotmail.com) writes:
here is the info
Executed as user: User domainname\user. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error
3101) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed.
Step one runs fine which disconnects users so not sure what the prob
is? any ideas??
Seems like you set the database for that job step to be the database
you want to restore. Change to master, and you should be fine.
Or someone manages to sneak in betnween the job steps. Make it one
single step to avoid this risk.
(But put SET MULTI_USER in step 2, and on the Advanced tab for step 1,
configure the job to continue with step 2, even if step 1 fails.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Hi still having probs get the following error
Executed as user: domain\user. Logical file 'UKReports _Data.MDF' is
not part of database 'ukreports'. Use RESTORE FILELISTONLY to list the
logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
failed.
RESTORE DATABASE rentsmartukreports
FROM DISK =
'E:\Nightly backups server\UK.bak'
WITH MOVE 'G:\SQLDATA\MSSQL\data\UK _Data' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Data.MDF',
MOVE 'G:\SQLDATA\MSSQL\data\UK _Log' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Log.LDF',
STATS = 1, REPLACE
GO
Where uk is old DB name and UKReports is new DB name
Any ideas??
blueboy (ma********@hotmail.com) writes:
Hi still having probs get the following error
Executed as user: domain\user. Logical file 'UKReports _Data.MDF' is
not part of database 'ukreports'. Use RESTORE FILELISTONLY to list the
logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step
failed.
RESTORE DATABASE rentsmartukreports
FROM DISK =
'E:\Nightly backups server\UK.bak'
WITH MOVE 'G:\SQLDATA\MSSQL\data\UK _Data' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Data.MDF',
MOVE 'G:\SQLDATA\MSSQL\data\UK _Log' TO 'G:\SQLDATA\MSSQL\data
\UKReports _Log.LDF',
STATS = 1, REPLACE
GO
Indeed, 'G:\SQLDATA\MSSQL\data\UK _Data' looks like a very unusual
logical name. Usually the logical file name of the data file is the
same as the database name, and the log file has "_log" tacked on it.
Sometimes the MDF has "_Data" in the logical name.
You can use sp_helpdb to find out the logical names of a dataase.
Or, if all you have is a backup, RESTORE FILELISTONLY.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
This is so frustrating, i have tried numerous variations and always
get the same error can any one help??
RESTORE DATABASE [new DB name]
FROM DISK =
'E:\Nightly backups Man1s\[Old DB name].bak'
WITH MOVE '[Old DB name] _Data' TO 'G:\SQLDATA\MSSQL\data\[new DB
name] _Data.MDF',
MOVE '[Old DB name]_Log' TO 'G:\SQLDATA\MSSQL\data\[new DB
name]_Log.LDF',
STATS = 1, REPLACE
GO
i get above error as stated, any help much appreciated does anyone
actaully have this working?? if so can i see your script??
cheers
blueboy (ma********@hotmail.com) writes:
This is so frustrating, i have tried numerous variations and always
get the same error can any one help??
RESTORE DATABASE [new DB name]
FROM DISK =
'E:\Nightly backups Man1s\[Old DB name].bak'
WITH MOVE '[Old DB name] _Data' TO 'G:\SQLDATA\MSSQL\data\[new DB
name] _Data.MDF',
MOVE '[Old DB name]_Log' TO 'G:\SQLDATA\MSSQL\data\[new DB
name]_Log.LDF',
STATS = 1, REPLACE
GO
i get above error as stated, any help much appreciated does anyone
actaully have this working?? if so can i see your script??
You have the syntax right, but how could I write a script for you
when I don't know the logical names of your database files? All I can
say is that
'[Old DB name] _Data'
looks funny. There are brackets in the name, and there is a blank in
the middle.
This will remain frustrating, if you just take chances on the name.
You need to find out what the names are, and there are two ways to
do it:
1) sp_helpdb on the source database. It's the first column in the
second result set, just copy and paste into the single quotes.
2) RESTIRE FILELISTONLY on the back-up file. Again, it's the first
column.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Hi
I finally got this working so thanks for anyone else here is the
script i used
databasename should be the Database Name
step 1 kill conections
ALTER DATABASE databasename SET SINGLE_USER WITH ROLLBACK IMMEDIATE
step 2 restore DB
RESTORE DATABASE datbasename
FROM DISK =
'F:\backup\databasename.bak'
WITH MOVE 'databasename_Data' TO 'F:\MSSQL\MSSQL\Data\databasename
MDF',
MOVE 'databasename_Log' TO 'F:\MSSQL\MSSQL\databasename.LDF',
STATS = 1, REPLACE
GO
step 3 allow connections
ALTER DATABASE databasename SET MULTI_USER This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: pb648174 |
last post by:
Is there a way to rename the logical file names? I'm not talking
specifically about the physical files, because those can be changed
during a restore, but the values immediately to the left of...
|
by: MS |
last post by:
I would like to be able to examine a folder, and populate a List Box or
Combo Box with the files that are in it.
How would you go about doing this?
The reason is that I would like the user to...
|
by: Lance |
last post by:
Are there any methods that indicate whether a string can be used as a full path for a file? For example, what I'm looking for is a method that would test for things like correct file name format,...
|
by: mikkel |
last post by:
Hi.
I have two physical files on an iSeries (AS/400) box, from which I
would like to make a join logical file.
The files are in a master-detail setup, where the master table
contains:
-...
|
by: Lyle Avery |
last post by:
Hi guys,
Who knows how to restore VS 2k5 file associations, I've clicked Tools->General->"Restore File Associations" button but it doesn't work at all. I know Repair the whole product could fix...
|
by: jaimebienlesfruits |
last post by:
Hi, I'm not quite sure where I should post this, so feel free to direct me. ;) (Have already posted on Apache forum.) Anyway, where do you go and what do you do so that capitalised file names don't...
|
by: bharadwaj |
last post by:
Hi,
If I will rename the logical file name in production will it create any issues.(sql server 2000).
I am getting the problem while restore the production database to other server. So,that I am...
|
by: pradeep c |
last post by:
Currently, I am having a backup file of MSSQL server 2005 database. I want to restore the backup file(of MS Sql server 2005) to MYSQL database.
Please Note: I don't have MS sql server 2005...
|
by: Ormazd |
last post by:
Hello,
I was wondering if anyone might be able to help me with a little PERL script? I'm very new and I have been given a task to write a simple Perl script that prints out the file names and...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |