Recently i encountered a sitution where we need to take a back up of existing sqlserver and restore it elsewhere. So i immeditely logged on to the server for checking the sie of the existing db files and my jaw dropped looking at the size! Its over 300 gigs. Yeah you guessed it right, the server is not having that much space for taking the backup.
Immediately i tried for getting a new drive to the box and as usually it is going to take time. But i came to know that there is some 500 gigs of space available on a linux box which i can use temporarily. Great i immediately created a samba share on the linux box and tested the same on the production box for accessibility. Successfully accessed the share with the provided credentials, mapped the drive as z: drive on my server box.
Started the sql server enterprise manger and open the backup dialog only to find that my Z: was not listed in there!! What's happening? The sad news is sql server does not support network folders as database storage folders. Great! now what to do...? ... That's exactly what i did, again went back to google with more queries. At last, i found that this can be achieved using some under documented features of sql-server. This is how we do it.
Disclaimer ;) : Using a network folder for storing backups is not supported and there is a good chance of corrupted db files[From MSDN].
First of all, there are a set of trace flags which were present in sql server that we need to configure. I used only the flag 1807 for this purpose.
DBCC TRACESTATUS
Execute the above sql statement and you should get a results with three coulmns, with the status of configured trace flags on global, and session level's.
Now, how to turn on that flag?
DBCC TRACEON( 1807 ) to turn this on for session
DBCC TRACEON( 1807 , -1) to turn this on globally.
Once checked the status of the same using tracestatus command, i proceeded to take the backup. Still not working! My mapped network drive is not coming in the list, so i tried by typing in the path directly, that also didn't work. Then tried by giving the UNC folder path, even that also failed.
Back to MSDN and i found out why, at the very end of the doc there is a suggesstion saying that the trace flags will work better if set as one of the startup parameter using -T option. Great, now i need to restart my db server twice. (but it seems that we need to configure this particular flag as startup param)
After setting the startup param in the sql server configuratiom utility i restarted the server and issued the tracestatus command, and surprise the flag was set globally now!
Okay i proceeded to do backup now and still my mapped drive is not showing up in the file browser!. Okay, i tried by directly typing in the UNC path. Still no luck! Arrrgg. What should i do? As per MSDN everything is setup and fine.
After re-reviewing the whole setup the only thing that was looking odd is the shared folder itself. The share is on a linux box as a samba share (not a windows share), and it requires credentials! Wait a minute, though we already mapped the drive in explorer using credentials the sql server might not be using them. So went back to linux, made the share accessible to everyone (what? To everyone? That's ridiculous. I was going to copy a production db backup on to a drive accessible to everyone? Yeah, but what to do? No other quick option available now, so just made sure that the subnet is not being used by any one at that time by fiddling with the router firewall settings.) Now that's done, i have deleted the already mapped drive and re-mapped the same with out suppling the credentials. It worked. Immediately i tried the backup process and it worked!!!! Finally i was able to backup on to a linux shared folder using sql server.
So in brief the steps are
1. Set up a share with public access (Need to check if a windows share with credentials is having any issues)
2. Open the sql server config utility and add the startup parameter ;-T1807
3. Restart the SQL Server
4. Check the status of the flag using DBCC TRACESTATUS, it should show 1 for global.
5. Do the backup by supplying the full UNC path in the destination folder.
6. Open the sql server config utility and remove the trace flag parameter.
Now that we have backedup the db on to share it is recommended to restore the backup from the shared folder and check for any corruption issues.
Sql server does support a shared folder. But SQL Server supports provided that we install a SQL server certified storage driver which in-turn accesses the shared folder. Now obviously that will not come for free.
Comments, opinions, suggesstions are welcome.
No comments:
Post a Comment