| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-02-12 : 12:03:20
|
| This article covers the basics of full backup backups and restores in SQL Server. The examples are from SQL Server 2005 however it applies to SQL Server 2000 and SQL Server 2005. This is a very basic article covering full database backups, database restores and the simple and full recovery models. Article Link. |
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2007-12-31 : 16:56:28
|
| In the article it mentions a growing database is because of a large transaction log, and suggest switching recovery modes to shring the log. In SQL 2000 this breaks the transaction log, is this different in 2005? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-31 : 17:15:34
|
| No. The transaction log chain is broken when you make the switch. Make sure to perform a full backup to start it up again.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Jason Lynn
Starting Member
5 Posts |
Posted - 2008-02-06 : 07:23:07
|
| Thank you for this fantastic article! I'm just on my starting point of SQL management and my scope of what I can do with SQL is pretty limited. Backup is right that operation that I recently got stuck with. Well, the problem is quite simple but I have no clue how I can solve it. I need to backup several databases but I am running out of space. Could anybody help me with this. Oddly I can't find out the resolution although I feel like I am pretty close to finding it. But nothing is coming at the moment… |
|
|
|
SQLBud
Starting Member
5 Posts |
Posted - 2008-02-06 : 10:08:16
|
| Why can't you just save the backup to another drive? |
|
|
|
Jason Lynn
Starting Member
5 Posts |
Posted - 2008-02-06 : 10:37:32
|
| Ha, if I could! That's what I do: I open SQL Management Studio, connect to the SQL server and choose the database I want to backup and select Tasks|Backup from the context menu, right? Now I click Add button and then the only destination that I can choose from is my local C:\ drive! What gives? Am I getting it wrong? In dialog where it asks me to select backup destination I just click for browse and then the next dialog that opens I can only choose a folder from the C:\ drive. What can I do? I don’t have space on my drive. What's more, what's the need putting something to the same basket? I it fails your backup will go tits up as well. Huh? |
|
|
|
SQLBud
Starting Member
5 Posts |
Posted - 2008-02-06 : 10:56:56
|
| Try backing up database to a network drive like Bill writes there but make sure to care about load that you put on your network channel. Saving large databases as-is may seriously cap network throughput degrading the overall performance. |
|
|
|
Jason Lynn
Starting Member
5 Posts |
Posted - 2008-02-06 : 11:15:26
|
| I'd do if I knew. I don't seem to see my network shares in that dialog. |
|
|
|
SQLBud
Starting Member
5 Posts |
Posted - 2008-02-06 : 14:01:35
|
| No need to worry. Here's how you can do that and successfully backup your data to a remote shared folder on your network. First off, you can try a brute force method showing how you can do that programmatically.1. In Microsoft SQL Server Management Studio click the New Query button on the Standard toolbar.2. The new query tab opens. Click within this tab and type the following:USE <DatabaseName>GOBACKUP DATABASE <DATABASENAME> TO DISK='\\ServerName\SharedFolder\DatabaseBackupFileName.bck'Here <DatabaseName> is a placeholder for the database you want to backup. Make sure you are declaring the database name without angle brackets! Now the script is done and you can proceed with backing the database up. To do that just click Execute on the SQL Editor toolbar and wait until SQL Studio finishes it. Interestingly it works for me without needing to put N before declaring the UNC path.However, when this approach works quite well if you need to implement it within a custom stored procedure it's hardly a viable for such administrative tasks as this. As an admin I prefer using GUI tools when everything is intuitive. SQL Management studio is a smart thingy. It automatically reads your mind. Well, at least it automatically puts the save path and a file name for you once you select its backup function after you performed backup via script. That’s even more simple than this script although yet not that simple as it can be done with professional tools that sometimes provide many tricky options like compression and solutions to speedup configuration and backup. But it works. To backup file to a remote share via UNC path using GUI perform the following procedure.3. Rightclick the database and proceed with that way that you did yourself by opening the Backup Database dialog window. Yeah, you were not far from finding the solution by yourself. But the tool is quite hard to get familiar with immediately so that's no wonder that you didn't found it out yourself. As you may see SQL Management Studio will automatically add the network path you've defined in the script above. So that you'll see the \\ServerName\SharedFolder\DatabaseBackupFileName.bck path added to the Backup to list one you open the backup dialog. Note! You have to backup the same database you've defined instead of the <DatabaseName> placeholder to make SQL Studio get it right. Otherwise you have to define the save path from scratch. Still if you wish to save this database to other location nothing prevents us to do that.4. In the same Backup Database dialog click Add button next to the Destination section. In the Select Backup Destination dialog box click the Browse (dotted) button to open the window when you can set the save location. I mean that Locate Database Files dialog window where you see the only single hard drive C:\ presented in your system. Here's the trick. In the File name edit box put \\ServerNameNew\OtherSharedFolder\DatabaseBackupFileName.bck and click OK. Bingo! As you may see, the Studio has just added the UNC path you've defined into the Destination on disk field in the Select Backup Destination dialog box. Click OK and the path will be added to the Destination box in the Backup Database dialog box. Now that you just need to click OK. Sit back and wait when backup finishes. |
|
|
|
Jason Lynn
Starting Member
5 Posts |
Posted - 2008-02-07 : 05:10:43
|
| Thank you for your detailed explanation! That's awesome! Now I see how I can do that. I successfully added the path on my network drive. But it seems like it's less free space on my network share than it's required. When I back the database I get errors saying that the file I specify cannot be open. I believe compression may help me here. What you were talking there about professional tools? |
|
|
|
SQLBud
Starting Member
5 Posts |
Posted - 2008-02-08 : 09:12:08
|
| Professional tools may help you to solve your problem and save you a lot of space. I am using Scriptlogic's Litespeed and it usually gets me 30 to 60% saves. But wait! I guess, I know what's preventing you from backing up with SQL Studio. Aren't you running your SQL server on under Local System account? If yes, change it to Network Service and see if it helps. 1. Open the SQL Server Configuration ManagerClick the SQL Server 2005 Services and locate the SQL Server service for the SQL instance that hosts the database you are trying to backup. Doubleclick it and set the Network Service as the login account for the SQL server service in the built-in account drop-down list on the Log on tab. Click OK to finish the operation and try the backup again. I recommend you also add the Everyone well-known SID to have appropriate rights on the network share for the SQL to be able to write data to share. |
|
|
|
Jason Lynn
Starting Member
5 Posts |
Posted - 2008-02-08 : 11:09:41
|
| Thank you. You were right! My SQL server service runs under the Local System account. I've tried changing it to Network Service account as you said but when I click Apply I get the "A new member could not be added to a local group because the member has the wrong account type. [0x8007056c]" error from the WMI Provider. |
|
|
|
SQLBud
Starting Member
5 Posts |
Posted - 2008-02-12 : 04:51:01
|
| Are you running it on Domain Controller? If so, you can't specify the Network Service because it's not supported on domain controllers. In fact it's not recommended to run SQL server on domain controllers but there's the trick that will allow you to get access to the network share running the SQL Server service under the Local System account. The answer is you have to add the machine account the SQL Server is running on to the ACL for the network share. Open the Security tab for the shared folder and add the DomainName\ComputerName$ account to the ACL where the DomainName is the domain name the database server belongs to and the ComputerName is the NETBIOS name for the database server machine. Repeat the procedure once you are finished with editing the security. By the way the best way would be to edit these settings with something that would allow you to edit security remotely. Personally I use another management tool from Scriptlogic called Security Explorer for SQL. For me the combination of these two tools from Scriptlogic - the Litespeed and the Security Explorer provides the best value/performance ratio. Look the Litespeed tools supports like 10 or 12 levels of compression that you can apply to a database you backup. That provides me with the broad number of combinations that I may use to select the best combination between the performance and space savings. I may then apply 256-bit long key encryption to the DB to be sure that no one will be able to read the database even if I provide the Everyone SID with a Full Control access to the network share where I store the database. |
|
|
|
alexeyk
Starting Member
1 Post |
Posted - 2010-04-30 : 10:19:24
|
| I'd like to use the following tool - SQL Backup and FTP (http://). It's a very nice, simple, powerful and freeware application. It allows you to do SQL backups as 1-2-3 and provide you with functionality to compress backups and send them via FTP. |
|
|
|
AlexGreen
Starting Member
8 Posts |
Posted - 2011-07-03 : 10:18:36
|
quote:
Originally posted by alexeyk
I'd like to use the following tool - SQL Backup and FTP (http://). It's a very nice, simple, powerful and freeware application. It allows you to do SQL backups as 1-2-3 and provide you with functionality to compress backups and send them via FTP.
I use this tool also, we have SQL Server 2005 Express running for our stock application. SQL Backup and FTP scheduler help us with our daily backup. |
|
|
|
elliswhite
Starting Member
36 Posts |
Posted - 2014-05-03 : 08:07:22
|
| By following steps described in the article, I could have able to restore my backup database very safely. Thanks for posting. |
|
|
|
|
Article: Using BACKUP and RESTORE in SQL Server -- Full Backups