question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked MikhailFirsov-1277 answered

Question on backing up clustered instance

Hello,

It's rather strange but I failed to find information about best practices on backing up clustered SQL Server instances in my MS textbooks (official curriculums) as well as on the technet - would anyone please share a link to such documents should they exist?

I'm asking that question because I can't seem to make my clustered SQL Server instance to backup to another shared drive (another LUN) which I've already added to the SQL Server's resource group - it can only "see" the LUN (disk) which had been selected during setup...

Thank you in advance,
Michael

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

It doesn't matter if it's a cluster or stand-alone server, SQL Server backup works always the same way

backup to another shared drive

If you mean a NAS share, the you can not "see" it in backup wizard.
You have to use plain T-SQL backup command and address the share by UNC path.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered OlafHelper-2800 commented

Yes, T-SQL Backup statement worked out perfect - but why does the SQL Server's backup wizard "see" just the drive (LUN) where its databases are located? Is it a bug or it's by design? I would never have guessed that the wizard can't see what the TSQL can see...

By the way:
"It doesn't matter if it's a cluster or stand-alone server, SQL Server backup works always the same way
backup to another shared drive
"

1) the "stand-alone version of the wizard" always does see the same set of disks as TSQL sees
2) what does mean "shared drive" in the context of the single server???

Regards,
Michael

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

"shared drive" is more something in the cloud.

You can have a SAN as attached drive, defined as cluster resource. Then it's available as a drive for the active node, but not for the passive one.
And you can have a NAS share, which you may map as a drive, but then it's mapped in your profile and only for your login; not for SQL Server (service account)

But that's all more Windows then SQL Server related.

0 Votes 0 ·
MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered MikhailFirsov-1277 edited

Ok, I understand - thank you for the explanation!

I also tried to create a maintenance plan (backup db, log...) now and it was created using the default drive (LUN), but it allowed me to change the destination folder by typing in the path to the other LUN and the plan did produced the backups on that (E:) drive (there was strange errorduring the execution but all database and log backups have been created):
133124-q1.png

133048-q2.png

...can't understand 1) the behaviour of the backup wizard 2) why this is not documented (at least I've never seen it in documents/guides/textbooks)... :(



q1.png (17.8 KiB)
q2.png (17.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered

P.S. Here're the two jobs: the first is part of the maintenance plan (backup to e: as mentioned above - just the selected checkbox next to "Backup DB"), the second is the manually-created job -


 BACKUP DATABASE Resources
  TO DISK = 'E:\JOBbackups\DB.bak'
     WITH FORMAT;
 GO

The results are as follows:
133069-q3.png


The most interesting fact: backups are getting created in both cases! In spite of the error in the first case the backup that's being created by the maintenance plan is totally correct (I've tested it by restoring the db from it) - no comments...


q3.png (37.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.