November 13, 2002 at 8:24 am
Hi there,
we have two very large Databases. I would like to create (automatically) a backup (just on a harddisk-drive). The only problem is the local drives are not large enough to hold the databases and the backup.
So is there any way to automatically backup the databases to a mapped drives on a second Server? If yes, how can this be done?
Please, help.
Thanks.
November 13, 2002 at 8:29 am
Yes, backups can be created on a mapped/network drive. To do this you need to first create a database backup device that points to the network device. I would specify a UNC name when defining that device. Next you backup the database to that device. Be aware the backup will take longer because you are now writing the backup across the network, instead of directly to the server. Also the account that your backup process is running under will need to have access to the network share you are writing to.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 13, 2002 at 9:01 am
Thanks Gregory for you help.
It really worked.
Regards,
Thorsten :-))
November 13, 2002 at 9:27 am
You can just use the UNC path in the maintenance plan or backup wizards. The backup device is not required.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 27, 2002 at 10:23 am
I have a related question..
How restore a backup FROM a mapped drive (unc or otherwise!)
I know that it doesnt work out of the box, but maybe someone has figured out a way to do it..This would eliminate the need to have available disk space of twice the size of the database (once for the bak file, one for the actual data once restored) thanks!
December 12, 2002 at 8:16 pm
Just restore from device added during restore. Again just use the unc path name before file name.
December 16, 2002 at 9:55 am
I mapped a share to the drive on the network that I want to back up the database, and ran it. I still get a device error. I tried both ways, thru the mapped drive and UNC, no luck.
Any pointers would be appreciated.
December 16, 2002 at 12:49 pm
Here's what I use for my dumps.
Full dump scheduled 1/day around midnight.
declare @v-2 varchar(250)
select @v-2= '\\BackupServer\MSSQL\BackUps\' + 'MKDB_DB' +
convert(varchar(6), getdate(), 12) +
convert(varchar(2), getdate(), 108) +
substring((convert(varchar(19), getdate(), 100)), 18, 2)
dump database MKDB to disk=@v
And transaction logs every 5 minutes
declare @v-2 varchar(80)
select @v-2= '\\BackupServer\MSSQL\BackUps\'+'MKDB_LG'+
convert(varchar(6), getdate(), 12) +
convert(varchar(2), getdate(), 108) +
substring((convert(varchar(19), getdate(), 100)), 18, 2)
dump transaction MKDB to disk=@v
And to restore, you can do it through the gui as the easiest way, especially if you are using the transaction logs, but if not, then just restore using the full path as UNC. I had a script that generated me a point in time batch from the backup history table at one time, but haven't used it in so long that I can't find it right off. This method of backup is great for any programatic use of backup files as you always know what the name is since its structured programatically. I've used it for years for log shipping, with another set of scheduled jobs on the recieving end to restore by the same structured names, only five minutes less. Never have understood why log shipping is such a big deal. Anyway, hope this helps.
December 16, 2002 at 1:01 pm
here are the errors that I encounter...
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\gary\d$\report.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
please help
December 16, 2002 at 1:21 pm
When I clicked, All Tasks |Backup Database |Add |Backup device |<New Backup Device> and set Name: = Test and FileName = \\ky2095\c$\temp\backups\, I was able to backup successfully from the server to my personal workstation.
hth
December 16, 2002 at 1:37 pm
I still get the same errors. Device offline.
I must not have the right administrative rights on that share.
December 16, 2002 at 5:19 pm
garutyunyan, you need to get the correct permissions to perform the backup. If run through the EM or SQL agent as a scheduled job, then for the login for SQL or SQL AGENT.
I used to use the Backup Device like that, but you are unable to change the name of the file you dump to, and that caused me problems, so T-SQL to the rescue. Keeping all my backup files in a single device is too dangerous for me, as I've seen backups corrupt, and when they are all in a single device like that, it corrupts your whole file, not just the latest backup. Also, portability was a concern. We had the backup device in a DMZ and transferred our backup files down to local backup servers at night. Made a world of difference keeping them single and following a structured naming convention.
Edited by - scorpion_66 on 12/16/2002 5:21:16 PM
December 19, 2002 at 3:00 am
You need to ensure that the account trying to run the backup has full permissions on the target share otherwise you will encounter problems. The easiest way is to give the SQL Server account access to the share, this way if you schedule it, it will still work.
December 19, 2002 at 4:47 am
To verify the reason is permissions based or to get a more specific answer look at the window Event Viewer logs. There is usually more descriptive answer to the problem there in regards to this message.
December 23, 2002 at 7:48 am
Also remember that to use a mapped drive, you must be logged in. If you automate this task and have it run on the server and no one has logged in, the drive will not be mapped and your backup will fail.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply