please help
-
Is there a way to restore database onto another server without copying over the backup file . eg:
On server1 there’s a database, I want to take backup file of this database and restore it over to server2 without having to copy backup file or running any operating system command to copy the file
Is there a way to create a script where the script reads the backup file name from sysdevices table of server1 or something like declaring variable inside the code that reads the 'phyname' of the particular database backup file
Any input greatly appreciated - thanks a lot
-
If you can connect to the backupfile on server1 from server2, you can choose this backup with the task RESTORE FROM DEVICE in EM. Alternatively you can use the TSQL RESTORE DATABASE FROM DISK and give the networkpath to server1.
Depending on your networkcapacity this might make your networkadministrator frown, but alas...
Hth
Greetz,
Hans Brouwer -
Restore database yourdb
from DISK = '\\UNCservername\path\backupfile.bak'
Check restore in books online
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
-
Just curious as to ...
1) How big is the database backup ?
2) Why the aversion to a network copy of the backup ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
hi thanks for your response -
the size of the database is around 120gb
Regards!
-
Keep in mind your restore-time will depend on your network speed / availability !
maybe this site can help you calculate the needed time :
http://www.homenethelp.com/web/explain/about-network-speeds.asp
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
-
Hi,
This post is a bit late, but hope it helps someone else later on.
To enable a database backup/restore over the network, please
follow the steps listed below:
1. Ensure that the SQL Server service on the target server
(where the restore will occur) is running under a domain
account.
2. Ensure that the SQL Server service account has sufficient
privileges on the network share that hosts the backup file.
3. Modify the following registry key on the machine where the
network folder is located to add the shared folder name in
the list:
[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters\NullSessionShares]
Now you can do database backups/restores over the n/w from
either EM or QA. I am not sure if mapped drives work, but UNC definitely does.
Hope this helps.
-
"[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters\NullSessionShares]"
This may help if you, the DBA, are allowed to do such thing on a remote server. I can think of plenty of reasons(and have experienced them) why you are not allowed to do such thing.
Greetz,
Hans Brouwer -
Hans, you are correct with your argument. Of course, there is a certain degree of risk involved, but it is the only way to backup to or restore from a n/w folder in SQL Server if that is what is required. And modifying the registry should be temporary; after the purpose is served, the changes should be reverted back.
Regards,
Sanjay
-
Hi Sanjay,
To avoid having to make the change I have created 2 stored procedures to perform the backups to a NAS server...
Backup ....
/*
Description
------------------
This stored procedure will backup the requested database.
Amendment History
------------------------------
Date By Description
------- ---- ------------------
19/01/05 Peter Gadsby Initial version
31/01/05 Peter Gadsby Added SET DATEFIRST command to correct the week number issue (for Sunday)
*/
CREATE PROCEDURE BACKUP_DB
(
@DATABASE varchar(255) = 'uscgdm_staging',
@BACKUP_DIRECTORY varchar(255) = \\172.16.1.40\backups\USC\DATABASE BACKUPS\INCREMENTAL',
@BACKUP_TYPE varchar(255) = 'FULL',
@BACKUP_DATE datetime = null
 
AS
DECLARE
@BACKUP_FILE varchar(255),
@BACKUP_FULL_FILE varchar(255),
@BACKUP_NAME varchar(255),
@WEEKDAY varchar(255)
SET DATEFIRST 1
IF @BACKUP_DATE is null
BEGIN
SET @BACKUP_DATE = getdate()
END
SET @WEEKDAY = dbo.get_weekday(@BACKUP_DATE)
IF @BACKUP_TYPE = 'FULL'
BEGIN
SET @BACKUP_FILE =
@DATABASE + '_BACKUP_' +
cast(datepart(year,@BACKUP_DATE) as varchar(20)) + '_WK' +
cast(datepart (wk,@BACKUP_DATE) as varchar(20)) + '_DAY' +
'0' + '.BAK'
SET @BACKUP_FULL_FILE = @BACKUP_DIRECTORY + '\' + @BACKUP_FILE
BACKUP DATABASE
@DATABASE
TO DISK = @BACKUP_FULL_FILE
WITH
INIT ,
NOUNLOAD ,
NAME = @BACKUP_FILE ,
SKIP ,
STATS = 10,
NOFORMAT
END
ELSE
BEGIN
SET @BACKUP_FILE =
@DATABASE + '_BACKUP_' +
cast(datepart(year,@BACKUP_DATE) as varchar(20)) + '_WK' +
cast(datepart (wk,@BACKUP_DATE) as varchar(20)) + '_DAY' +
@WEEKDAY + '.BAK'
SET @BACKUP_FULL_FILE = @BACKUP_DIRECTORY + '\' + @BACKUP_FILE
BACKUP DATABASE
@DATABASE
TO DISK = @BACKUP_FULL_FILE
WITH
DIFFERENTIAL,
INIT ,
NOUNLOAD ,
NAME = @BACKUP_FILE ,
SKIP ,
STATS = 10,
NOFORMAT
END
GO
Restore
/*
Description
------------------
This stored procedure will restore a database from the requested backup
Amendment History
------------------------------
Date By Description
------- ---- ------------------
19/01/05 Peter Gadsby Initial version
31/01/05 Peter Gadsby Added SET DATEFIRST command to correct the week number issue (for Sunday)
*/
CREATE PROCEDURE RESTORE_DB
(
@DATABASE varchar(255) = 'uscgdm_staging',
@RESTORE_DATE datetime,
@BACKUP_DIRECTORY varchar(255) = \\172.16.1.40\backups\USC\DATABASE BACKUPS\INCREMENTAL',
@RESTORE_TYPE_INC_FULL varchar(4)= 'INC'
 
AS
DECLARE
@BACKUP_FILE varchar(255),
@FULL_BACKUP_FILE varchar(255),
@FULL_BACKUP_DIR_FILE varchar(255),
@BACKUP_DIR_FILE varchar(255),
@BACKUP_NAME varchar(255),
@SQLCOMMAND varchar(8000),
@wEEKDAY varchar(255)
SET DATEFIRST 1
SET @WEEKDAY = dbo.get_weekday(@RESTORE_DATE)
IF @RESTORE_TYPE_INC_FULL != 'INC' AND @RESTORE_TYPE_INC_FULL != 'FULL'
BEGIN
RAISERROR ('Error: Restore type must be either INC (Incremental) or FULL',16,1)
END
IF @RESTORE_TYPE_INC_FULL = 'FULL'
BEGIN
SET @BACKUP_FILE =
@DATABASE + '_BACKUP_' +
cast(datepart(year,@RESTORE_DATE) as varchar(20)) + '_WK' +
cast(datepart (wk,@RESTORE_DATE) as varchar(20)) + '_DAY' +
'0' + '.BAK'
SET @BACKUP_DIR_FILE = @BACKUP_DIRECTORY + '\' + @BACKUP_FILE
SET @SQLCOMMAND = '
RESTORE DATABASE ' + @DATABASE + '
FROM DISK = ' + '''' + @BACKUP_DIR_FILE + ''''
EXEC (@SQLCOMMAND)
END
ELSE
BEGIN
SET @FULL_BACKUP_FILE =
@DATABASE + '_BACKUP_' +
cast(datepart(year,@RESTORE_DATE) as varchar(20)) + '_WK' +
cast(datepart (wk,@RESTORE_DATE) as varchar(20)) + '_DAY' +
'0' + '.BAK'
SET @BACKUP_FILE =
@DATABASE + '_BACKUP_' +
cast(datepart(year,@RESTORE_DATE) as varchar(20)) + '_WK' +
cast(datepart (wk,@RESTORE_DATE) as varchar(20)) + '_DAY' +
@WEEKDAY + '.BAK'
SET @BACKUP_DIR_FILE = @BACKUP_DIRECTORY + '\' + @BACKUP_FILE
SET @FULL_BACKUP_DIR_FILE = @BACKUP_DIRECTORY + '\' + @FULL_BACKUP_FILE
SET @SQLCOMMAND = '
RESTORE DATABASE ' + @DATABASE + '
FROM DISK = ' + '''' + @FULL_BACKUP_DIR_FILE + '''' + '
WITH NORECOVERY
RESTORE DATABASE ' + @DATABASE + '
FROM DISK = ' + '''' + @BACKUP_DIR_FILE + ''''
EXEC (@SQLCOMMAND)
--print @SQLCOMMAND
END
GO
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply