Hi all,
I have an SQL server in Colorado and one in Atlanta. Is it possible in
SQL to mirror the two SQL servers? Meaning, once a day have the server
in Colorado send all changes made to the server in Atlanta? Or am I
looking at purchasing some type of formal backup software?
TIA
Hi there,I use shared space MSSQL server in my hosting server.And I can't backup my DB to my remote server.Please help how can I do it.Thank you
View 3 Replies View Relatedhi all,
I have a remote(online) database of my web portal.
I want to take backup of that remote(online) database to my local machne.
This is SQL Server 2005 database.
Please tell me how I take backup of my database?
I have been unable to use DMO to back up a DB anywhere but locally on the PC running the DMO code. Even if a network drive is mapped (eg. F:), the backup fails.
I'm trying to automate a backup process for server(s) that will run on a PC and store the backup file on the server that contains the DB. Has anyone done this? Any hints are appreciated.
Hello Everyone,
I got a problem when I tried to backup SQL 7.0 database to a remote location.
The situation isthat I have two SQL Servers (both are SQL Server 7), They are in the same domain, but in two different NT servers mechines. When I create a new backup device, I can only see the local partitions.
I tried to use T-SQL to create the backup device. It can create it, but when I tried to use it, I got a error message, saying the disk not exist.
I thought that might have something to do with disk sharing, but not sure. Can any of you give me some advice on how I can access the remote partitions from SQL's Enterprise Manager? Thank you very much!
Lunjun
Hi,everyone!
I`d encountered a problem about backup DB to remote server.
At first,I add a dump device to remote server in local server,
for example:
sp_addumpdevice `disk`,`netback`,`RemoteSvrsharedfolderetback.dat`
Return success!
But when I begin to backup,return the message"...device error or device off line..."
the detail in error log said:"RemoteSvrsharedfolderetbackup.dat failed to open ,operating system error=5(access is denied)"
Why?
Could you help me?
Thanks you very much!
steven.z/98.8.29
Hi, I am new on sql and very happy finding your forum!
Ive just installed mssql 2005 on my pc and I need your help on backing up a database that is on a remote server.
I ve connected to the remote server using the Management Studio. Im accessing the database and Im getting the backup, but unfortunately I see that the back up is being saved on the remote server.
How can I backup the database saving it on my machine?
Thanks in advance,
Martha
Hi all,
Am extremely new to SQL and have come stuck writing a DOS script that works well if the DB's are on the same server. My problem is that one of the DB's the script is to manage is on another server and I've been losing sleep trying to work out how to resolve this.
I'm trying to keep (if possible) the script as simple as I explain below.
For the successful DEMO backups, I have the following ....
The SQL batch file (%SQLFile%) reads:
backup database DB_DEMO to DISK = 'C:DA_InstallsBOSDEMO estingackupsdbaseDB_DEMOFri.bak' with init
go
In the DOS script, I have:
osql -S %DBSrvr% -E -d %SQLDatabase% -n -i %SQLFile% -o %SQLBackupLog% >> %OutputLog%
And this is successful, as the script and the DB reside on the same server. However I'm troubled by how I connect to the DB_LIVE db on another server and write the backup to the same server the script is run from.
Any help is really appreciated.
Cheers,
Cameron
I want to restore a huge database into my workstation.The size of the backup file is more than 6 GB and I don't have enoughspace on my HD for both the database and the backup file.So I put the file in a shared folder on a pc connected through a switchto my pc.My wkst uses w2k pro sp4, the other PC win xp home SP1. MSDE 2000.The share is visible and RW for the administrator of my wkst.The 2 pc are in the same workgroup and are not part of a domain.I tried to restore using this code:RESTORE DATABASE MydbFROM DISK='\uncpath ofile.bak'WITH ...and get the error 3201 and in the log:"BackupDiskFile::OpenMedia: the backup peripheral'\uncpath ofile.bak' could not open. Error in O.S. = 5(AccessDenied.). " (I'm translating form italian)I read that the problem can arise from the fact that the restoration isexecuted by a sql server "user" that has not the same permissions as theadministrator. I tried to assign to the SQLSERVERAGENT service the useradministrator with no avail.And executing:xp_cmdshell 'dir \uncpath ofile.bak'gives the error "access denied".Is there a solution to this problem?Otherwise how can I restore a database whose backup has almost the samesize as the free space on the disk?thank youmaxx--NOSPAM: Rimuovere i trattini dallo username!Cut hyphens from my username!
View 2 Replies View Relatedhi all,
View 1 Replies View Related
Hi
How can i make backup on the other computer.
1. SQL computer is express edition, and computers are in workgroup?
2. SQL computer is express edition and is not on domain, and backup computer is on domain.
thanks
alex
hi gurus,
can any bdy tell me the procedure to create the Backup Device which is a DISK OF REMOTE PC..
i want to take database backup on remote pc's disk not on local disk or tape
i am using sql server 2005
thanks
in advance
I use SQL 2005 Database in my ASP.Net 2.0 site, I have to require admin backup db for me every time, I hope to I can backup by myself , how can I do?
You know Microsoft SQL Server Database Publishing Wizard can restore DB easily!
Many thanks!
Hi all,
I have scheduled a backup on a remote machine. Everyday I FTP these backups from the remote machine to the local drive.
A local drive is maped to the remote machine in Windows NT Explorer but SQLSERVER7 Enterprise Manager can't see this drive so that I could do the backup to it.
Can any one please tell me how to make this work?
Thanks in advance.
Attaullah
Hello Friends:
My problem relates to backing up my MS SQL 2005 database which is sitting on a shared server at a hosting company.
OVERVIEW:
- Hosting company is using MS SQL 2005
- I am using the SQL Server Management Studio that comes with SQL Server 2005 Standard (NOT Express), which is installed on MY PC.
- So, I am connecting to the SQL server over the internet
WHAT I WANT TO ACHIEVE:
- I would like to backup the data sitting on the Hosting company's MS SQL Server. I only have one database on this SQL Server. There are of course 100s of other databases on the same server which belong to other customers of the hosting company.
- I want to bring the backup to MY PC, from the SQL Server.
- As far as I can tell the following options within SQL Server Management Studio may be of help to me. I do not know which one I should use or which one is best or what is the proper method. 1) Select Backup option from the Tasks menu (but it only shows me drives/devices on the Hosting SQL Server, not my PC, so I can’t backup to my PC) 2) Export Data (it does not work, showing errors ‘…not a trusted connection…’ I have no clue what a trusted connection is. 3) Copy Database (which is supposed to copy the remote database on the Hosting company SQL server, to my local SQL Server running on my PC). I go through the wizard, on the last screen it just hangs i.e. shows- not responding)
MY QUESTION TO THE COMMUNITY:
How do I backup the database sitting on the hosting company SQL server? I of course need to bring the backup to my PC.
Hi
I want to take backup of rapport database.
and copy those .bak files from this local server to remote server automatically.
can any one help me plzzzzzzz.........
Thanks in advance
Madhu.......
Hi,
I have a database on a remote server, and want to take a back up of that to my local system. My objective is to take the back up of the db, and then restore it to another SQL Server. Can anyone please help me with this.
Thanks
Vivek
Hello-I have a Sql Server 2000 database offsite that I would like to back upto a local machine. I am using Enterprise Manager on a local machine toadminister the remote db.Whats the best way to schedule backups so the remote db is backed up tothe local machine?Can this be done in Enterprise Manager?Would you recommend any 3rd party software?Thanks!MB
View 1 Replies View RelatedHi,
We have remote offices running SQL 2000, which are on a small bandwidth pipeline, so we implemented an incremental backup schema where they back up their LOGs overnight and send it to our main office. After an initial full backup, they just keep sending **incremental** LOG backups.
In the main office we restore the full backup of each remote office and from then on do a restore of each night's LOG file (with standby option), so that we keep in sync with them. The issue we have now is that we cannot get regular full backups from the remote (and sometimes independent) offices on a regular basis - maybe only once a *quarter*.
If we use differential backups, the size of each one grows each day, until we get a full backup. Incremental backups thru LOGs seemed to be the only way to go. *HOWEVER*, we now have multiple LOG files sitting on our server in the main office and since the databases that we restored to is in warm-standby and read-only mode, we cannot make a backup of them in-house, without breaking the LOG restore chain.
That also means that we would not be able to easily re-create the environment locally, should something go wrong - we would have to apply all of the LOG restores for (potentially) the whole quarter!
I even tried to create two restored databases - one where I keep it in standby state and ready to receive more incremental LOG backups and another where I would take it out of standby mode (and into recovery mode). At that point I am then able to make a full backup locally of this second database. *BUT*, when I then try to restore the next day's LOG backup to this second database, it fails with an error
- Msg 4330 - Level 16, State 4, Line 1
- The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
How do I do incremental backups and restores, while maintaining the ability to make a new full backup of the restored database, before the restore sequence is done?
Help !
I created a maintenance plan to do the backup on my machine and created a job using CmdExec command: copy e:\*.bak \remoteackup but the copy job fail each time.
I know that command works fine when i run it from a batch file but does not seem to work from the job in sql server. I try to map the remote machine to a drive and used the drive in the copy statementcopy e:ackup*.bak q:ackup but it still does not work. Is there something I need to do inside sql server?
I would like to backup a database on a remote SQL 2005 server using T-SQL. The local server I want to issue the command from is also a SQL 2005 Server.
Do I need to use the openquery function?
I am doing this as a job step so I will be executing the query from a local server.
I do not want to use a SSIS package.
Thanks
Sorry if this is elementary, but I have searched and cannot find an answer.
I have a backup file of a sql server 2000 database. I want to restore it to a remote hosted sql server 2005.
Using sql server management studio, it seems to me that I cannot access the files stored local on my computer.
Does anybody have a solution?
What is the best way to deploy my backup file to the remote server?
Thanks for any suggestions!
i buyed hosting for my site , i am using sqlserver 2000 as backend. hosting compony allow to connect to my database through queryanalyzer not from enterprise manager.hosting compony charge me for taking database backup on there server. so i want to know how can i take databse backup from remote sql server 2000 to my local sql server 2000,any tool process by which it is possible to take databse backup at my own computers sql server 2000.
View 10 Replies View RelatedIs there an easy way to backup a database on a remote SQL Server to a backup file on my local computer?
View 3 Replies View RelatedI have upload my SQL 2005 express database to my hosting, and require DBA attach my express database to remote sql 2005 server database.
My question is
1. I have only Windows 98 PC which can connect to Internet, it mean I can use SSManagement, how can I backup remote sql 2005 database?
2. In the further, If I choose another hosting provider, how can I transfer my remote SQL 2005 database?
3. Can I download remote SQL 2005 database to local disk and change it to express database so I can use it in my VWD 2005 Express?
Does anyone know if it is possible to have a tape device another server as a tape dump device on SQL. Instead on removing the tape drive and installing it locally ?!?
Regards
Jasper
I want to set up remote backup device on "serverCentral" for 50 MSDE SQL2k servers so that they could use one backup to create(restore) any new customer databases. This way, I could keep one resource with many coming updating on procs and triggers...
I could create remote backup device with serverCentralackupackupdeviceName...
The problem is the remote device is "access denied" when I issued a "restore headeronly from backupDeviceName" sql query.
Neither I could issue xp_cmdshell 'dir serverCentralackup' --"Access Denied" from server49
Though I could dir serverCentralackup*.* from server49 command prompt.
It looks security permission is OK otherwise I could not dir from command line.
thanks for the help
David
Hello Friends:
My problem relates to backing up my MS SQL 2005 database which is sitting on a shared server at a hosting company.
OVERVIEW:
- Hosting company is using MS SQL 2005
- I am using the SQL Server Management Studio that comes with SQL Server 2005 Standard (NOT Express), which is installed on MY PC.
- So, I am connecting to the SQL server over the internet
WHAT I WANT TO ACHIEVE:
- I would like to backup the data sitting on the Hosting company's MS SQL Server. I only have one database on this SQL Server. There are of course 100s of other databases on the same server which belong to other customers of the hosting company.
- I want to bring the backup to MY PC, from the SQL Server.
- As far as I can tell the following options within SQL Server Management Studio may be of help to me. I do not know which one I should use or which one is best or what is the proper method. 1) Select Backup option from the Tasks menu (but it only shows me drives/devices on the Hosting SQL Server, not my PC, so I can’t backup to my PC) 2) Export Data (it does not work, showing errors ‘…not a trusted connection…’ I have no clue what a trusted connection is. 3) Copy Database (which is supposed to copy the remote database on the Hosting company SQL server, to my local SQL Server running on my PC). I go through the wizard, on the last screen it just hangs i.e. shows- not responding)
MY QUESTION TO THE COMMUNITY:
How do I backup the database sitting on the hosting company SQL server? I of course need to bring the backup to my PC.
Dear all,
What I want to do is to backup my database through the SQL script, when the backup is successful, the .bak file is saved in my local harddisk. After that, I used the XYRunProc to execute a "copy" command to copy the .bak file to a remote server.
However, when I want to restore the database using the .bak on the remote server, I found that the .bak is corrupted. This problem does not exist if the file is copied to the remote server manually (click copy on local server and click paste on remote server).
Do anyone know what's the problem of this? This really made me very frustrated. Hope someone can help me, thank you very much!
Regards,
Strike
I am working on a project for an SQL job. I am:
1.) Taking a database out of an availability group,
2.) Setting the recovery to simple,
3.) Shrinking the log file,
4.) Setting the recovery mode back to full,
5.) Then backing it up.
I need to restore the file to my secondary server with replace and non recovery mode. I am having trouble performing that call? I have the code to reestablish the database to the availability group if I can get the restore feature working.Â
I am trying to make my maintenance plan to backup on a remote server but it fails with:
"failed with the following error: "xp_delete_file() returned error 2".
I am pretty sure its because it doesnt have full access to the share folder... My question is, how can I grant access to it, if I am using a local account as sql agent service: NT ServiceSQLSERVERAGENT.I need to change the service account?
I €˜d like to discus with you the following REAL enough disaster scenario:
1. The TEST database is in the FULL backup mode.
2. WE have a full TEST DB backup and all tr. log backups.
3. DBA moved TEST database OFFLINE for maintenance operations.
4. MDF file for TEST DB was removed by mistake.
5. TRANSACTION LOG (LDF) file is OK.
6. DBA want to recover database to the point of failure.
According with MS SQL Server 2000 documentation it is possible.
We need to backup the transaction log , BUT I CANNOT DO THIS.
PLEASE, HELP.
In the same time, I can make LDF backup and recover database to the point of failure, if database is online and I stop/start SQL Server to remove MDF file.
FYI: I've posted this on a couple of forums and haven't gotten any response. I hope someone here can help since this is way past due.
First I'll give a little background on our situation.
Log Shipping and Replication are out, so I am scripting a backup locally, an xcopy to a remote box, and then a restore.
In the early stages of this, I'm trying to do 3 databases. 2 of them work fine alone. It's when I add the 3rd one that I have a problem. I noticed that in the 2nd stored procedure that I probably need to take out the WITH REPLACE if I'm dropping it beforehand as well. I don't have time to test it on this box until later tonight. I don't think that's the issue because it was doing the same thing before I added the drop. I'm overwriting the .txt file so I don't have the exact error that it's giving. I believe it's something similar to "Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation." I believe it also said [SQLSTATE 42000].
Now for the code. Props to Tara and the code she's put online.
Any help would be appreciated and I'll be glad to help answer questions related to what I've got.
1st Step:
Agent Job scheduled to call stored procedure
EXEC sp_backup_user_dbs3
2nd Step (The code for that stored procedure is):
CREATE PROC sp_backup_user_dbs3
AS
SET nocount ON
DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @DBName SYSNAME
DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file
DECLARE @loglogical VARCHAR(1000)
DECLARE @datalogical VARCHAR(1000)
DECLARE @restoreData VARCHAR(255)
DECLARE @restoreLog VARCHAR(255)
DECLARE @backupFile VARCHAR(255)
DECLARE @physicalNameData VARCHAR(255)
DECLARE @physicalNameLog VARCHAR(255)
DECLARE @physicalNameDataStripped VARCHAR(255)
DECLARE @physicalNameLogStripped VARCHAR(255)
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL VARCHAR(1000)
DECLARE @restoreToDataDir VARCHAR(255)
DECLARE @restoreToLogDir VARCHAR(255)
DECLARE @path VARCHAR(100)
SET @path = 'I:ackupMoveTo14'
--we need to delete all the old backup files from the I:ackupMoveTo14 folder
-- Build the del command
SELECT @cmd = 'del ' + @path + '*.BAK' + ' /Q /F'
--PRINT @cmd
EXEC master..xp_cmdshell @cmd,
NO_OUTPUT
CREATE TABLE #whichdatabase
(
dbname SYSNAME NOT NULL
)
INSERT
INTO #whichdatabase
(
dbname
)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ( 'db1', 'db2')
ORDER BY [name]
-- Get the database to be backed up
SELECT TOP 1 @DBName = dbname
FROM #whichdatabase
SET @RowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0 BEGIN SELECT @filename = @Path + '' + @DBName + '.BAK' BEGIN backup log @dbname
WITH truncate_only
END
-- Backup the database
BACKUP database @DBName TO disk = @filename
DELETE
FROM #whichdatabase
WHERE dbname = @DBName
-- Get the database to be backed up
SELECT TOP 1 @DBName = dbname
FROM #whichdatabase
SET @RowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR delay '00:00:05'
END
DROP TABLE #whichdatabase
SET nocount OFF BEGIN
SET @cmd = ''
SET @cmd = 'xcopy I:ackupMoveTo14*.BAK \RemoteServer /C /Y' EXEC master.dbo.xp_cmdshell @cmd
END BEGIN
EXEC [RemoteServer].master..usp_restoreDbsFromDir2
END
RETURN 0 GO
3rd Step(the code for the usp_restoreDbsFromDir2 on the remote server):
CREATE PROCEDURE usp_restoreDbsFromDir2
AS
SET NOCOUNT ON
DECLARE @dbname varchar(255)
DECLARE @loglogical varchar(1000)
DECLARE @datalogical varchar(1000)
DECLARE @physicalName varchar(255)
DECLARE @physicalFileName varchar(255)
DECLARE @restoreData varchar(255)
DECLARE @restoreLog varchar(255)
DECLARE @backupDisk nvarchar (255)
DECLARE @physicalNameData varchar(255)
DECLARE @physicalNameLog varchar(255)
DECLARE @physicalNameDataStripped varchar(255)
DECLARE @physicalNameLogStripped nvarchar (255)
DECLARE @rowCnt int -- @@ROWCOUNT
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL varchar(1000)
DECLARE @spidstr varchar(8000)
DECLARE @cmd sysname
DECLARE @bkpFile nvarchar(1000)
DECLARE @sql nvarchar(4000)
DECLARE @restoreDir varchar(255)
DECLARE @PhysicalDataPath varchar(255)
DECLARE @PhysicalLogPath varchar(255)
SET @restoreDir = 'F:MSSQLBACKUP'
-- Get files sorted by date
SET @cmd = 'dir ' + @restoreDir + '*.BAK /OD'
CREATE TABLE #Dir
(DirInfo VARCHAR(7000)
) -- Stores the dir results
CREATE TABLE #BackupFiles
(BackupDate varchar(10),
BackupFileName nvarchar(1000)
) -- Stores only the data we want from the dir
CREATE TABLE #RestoreFileListOnly
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
[Size] numeric(20,0),
[MaxSize] numeric(20,0)
)
INSERT INTO #Dir
EXEC master.dbo.xp_cmdshell @cmd
INSERT INTO #BackupFiles
SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #Dir
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'
-- Get the newest file
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC
SET @rowCnt = @@ROWCOUNT
-- Iterate throught the table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN
SET @cmd = @restoreDir + @bkpFile
INSERT INTO #RestoreFileListOnly
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')
--get the dbname from the bkpFile name
--SET @strSQL = CHARINDEX('_db_', @bkpFile)
--SET @dbname = LEFT(@bkpFile, @strSQL - 1)
SET @strSQL = CHARINDEX('.bak', @bkpFile)
SET @dbname = LEFT(@bkpFile, @strSQL - 1)
--PRINT @dbname
--IF @@ROWCOUNT <> 2
-- RETURN 3
SET @backupDisk = @restoreDir + @bkpFile
SELECT @datalogical = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @loglogical = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'
SELECT @PhysicalDataPath = PhysicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @PhysicalLogPath = PhysicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'
SELECT @strSQL = 'alter database ' + @dbname + ' set offline with rollback immediate'
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
SELECT @strSQL = 'DROP database ' + @dbname
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
--restore the database
SELECT @strSQL = ''
SELECT @strSQL = @strSQL + 'RESTORE DATABASE ' + @dbname + CHAR(10)
SELECT @strSQL = @strSQL + 'FROM DISK = ''' + @backupDisk + '''' + CHAR(10)
SELECT @strSQL = @strSQL + 'WITH' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'REPLACE'
SELECT @strSQL = @strSQL + ',' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE '''+ @datalogical + ''''+ ' TO '''+ @PhysicalDataPath + ''''
SELECT @strSQL = @strSQL + ',' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE ''' + @loglogical + '''' + ' TO '''+ @PhysicalLogPath + ''''
--PRINT @strSQL
EXEC (@strSQL)
SELECT @strSQL = 'alter database ' + @dbname + ' set online with rollback immediate'
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)
BEGIN
-- Build the del command
SELECT @cmd = 'del ' + @restoreDir + '' + @bkpFile + ' /Q /F'
--PRINT @cmd
-- Delete the file
EXEC master..xp_cmdshell @cmd, NO_OUTPUT
END
--This is supposed to remove the row once done
DELETE FROM #BackupFiles
WHERE @bkpFile = BackupFileName
-- Get the database to be backed up
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC
SET @rowCnt = @@ROWCOUNT
--Wait a couple of seconds before starting the next one
WAITFOR delay '00:00:30'
END
Drop TABLE #Dir
Drop TABLE #BackupFiles
Drop TABLE #RestoreFileListOnly
SET NOCOUNT OFF
RETURN 0
GO